Post reply

Refresh Pivot Table w/ selected members

  • January 10, 2006 at 6:48 am

    #166680

    Hi,

    We are facing a problem with the refresh of Pivot Tables in a report that selects some members of a dimension.

    I have a Pivot Table connecting to FoodMart 2000\Sales cube:

    • Select all measures.

    • The Non-empty behavior is turned off (i.e, it display members in the dimension, even if there is no value in the facts).

    • I have selected only some values in the Store Country dimension.

    • Select only a few Stores.

    Afterwards I have:

    • Inserted a new Store in the access file. Lets sais Store 100 (as a clone from Store 24).

    • Incrementally process the Store dimension.

    • Fully process the Sales cube.

    If you do a refresj in the PT the Store 100 apears in the PT. However, since I did not selected in the first place I do not want to see it, otherwise I cannot create a static report.

    Would it be possible for the new lines to be inserted at the end?

    Thanks in advance,

    Tiago.

  • January 10, 2006 at 5:16 pm

    #613956

    Hi Tiago,

    It's not really a direct answer to your question, but you could try using the Excel Add In (Microsoft Excel Acellerator for OLAP or something similar).  A new version was just released (maybe a month ago) to support 2k and 2k5.  When using the addin, you can create both tructured and unstructured reports - in the old Cognos Powerplay speak, these are Explorer and Reporter reports.  In the first, it's like the query is written as 'select <BOB>.CHILDREN from ...'  so if any new members are added, they will automatically be included in the result.  The second one (unstructured) is effectively written as 'select <BOB>.<member1, <BOB>.member2, ...' so you've explicitely requested the members you want to see,so no new members will be added to the result set automatically.

    If your aim is to be able to create staic reports (like a Profit and loss statement) and you want to control if and when new members are added, I would really recommend you try out the add in - it was written for this purpose.

    Cheers,

    Steve.

  • January 11, 2006 at 8:22 am

    #614078

    In the pivot table, you can create a grouped store field and only include the stores you want in a particular group. If you make the grouped field a page field with this group selected, it will never show newly added stores.

  • January 11, 2006 at 10:36 am

    #614123

    Hi all,

    Thanks for your replies. However, it still does not solve the problem:

    • The end users do not like Microsoft's add-in, they prefer the Pivot Tables.
    • The "Group" does not allow to select members that do not have values, nor it does allow to group members with different parents.

    Any other ideias?

    Thanks,

    Tiago.

  • January 12, 2006 at 4:55 am

    #614291

    If it's meant to be a static type report, could you try setting the mdx through VBA? 

    Steve.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply