locked
Filter using SETs RRS feed

  • Question

  • Hi,

     

    I have a set in the cube called currentDate, this set has the actual yyyy-mm date every month.

     

    I will like to create a PCA report that filters the top 10 item using the currentDate set

    Is this possible???

    Thanks for your response!!!

    CM

     

    Thursday, April 17, 2008 8:27 PM

Answers

  • Benn, Amanda 

    Thanks you very much for your help .

    It's working, I used the shared item and the current month in the background -as suggested!!! 

    Looks very good!!

     

    Smile

    Claudia

    Thursday, April 24, 2008 9:45 PM

All replies

  • Hi Claudia,

     

    You should be able to do this by using the TopCount function.  If you have further questions as to how to do that let me know and I'll see what I can come up with.

     

    Thanks!

    Amanda

     

    Thursday, April 17, 2008 9:02 PM
  • Yes I can do this with the top count but when I select the field "based on" in the Filter I cannot select  the member set Currentmonth (this member set changes automatically every month).

     

    The filter hardcodes the month (for example march) value..... this means that next month (for example april) the filter point to March and not to April

     

    I hope this is clear....

    Sad

    Thursday, April 17, 2008 10:32 PM
  • It sounds like you want to place your data hierarchy in the background and select your named set for that hierarchy.  That will filter the view based on the value of the set (current month in this case).

    Monday, April 21, 2008 8:35 PM
  • Thanks Ben

     

    If I understands your answer, you are suggesting to put the time hierarchy in the background.. right?

     

    I cannot do this because I need the time hierarchy (in this case the member set current_month) as a column.

    I need to display the top 10 products (as rows) for the current_month  (as column). The goal is to display this report in the PCA dashboard...so users can see the top 10 products of the current_month automatically (every month without human intervention)

     

    Is this type of automation possible.. I'm trying a lot of thinks and it doesn’t look possible?

    Claudia

    Monday, April 21, 2008 9:11 PM
  • Hello again Claudia,

     

    If you need to display the current month on COLUMNS and Top 10 Products on ROWS can't you create a query within ProClarity Professional specifying:

     

    ON COLUMNS- Current month (using a dynamic time member so that the set automatically pulls in the current month without user intervention - MTD)

     

    ON ROWS - Top 10 Products

     

    . . . and then publish that view to your PAS Server, in turn allowing users to be able to implement it within their Dashboard view. 

     

    Please forgive me if I have misunderstood the question.  Hopefully I have evaluated your inquiry using the correct assumptions.  If not feel free to elaborate.

     

    Regards,

    Amanda

     

    Tuesday, April 22, 2008 5:13 PM
  • Hi Amanda,

     

    This is what I try after your response.   I create two reports:

    The first report:  ROW:I create a new set using the selector and in the selector I choose to keep the top 10  items based on measure x

                                    COLUMNS:  current month

    The second report: ROWS:  I list all the items

                                    COLUMNS:  current month

                                    And then I use the filter dialog to filter only the top 10 count based on measure x

     

    Both reports have the same criteria’s and the results are not 100% identical

    Result of report 1:           prod1 = 100

                                                    Prod2 = 70

                                                    Prod3 = 50

                                                    Prod4 = 40

    Result of report 1:           prod1 = 100

                                                    Prod2 = 70

                                                    Prod7 = 4

                                                    Prod9 = 8

    So, there’s a difference by using the filter dialog (top count) vs filtering with a member set (defined in the selector as top 10 items)?

    Could you please explain what is the difference??   

    claudia

    Tuesday, April 22, 2008 10:13 PM
  • Will you please send the MDX for the two views, and the MDX for the named set?

     

    To get the MDX for the views, go to View|MDX Editor after the view has loaded.

     

    To get the MDX for the named set, right click on it and choose Edit.

     

    Tuesday, April 22, 2008 11:12 PM
  • Yes, the following is the mdx for the view using the selector member

     

    SELECT { [Previous Current Month] } ON COLUMNS ,

    { ORDER( { TOPCOUNT( { [<##<SET!cmercado!New Set (2)>##>] }, 10, ( [Time].[Time Hierarchy].[Time Month].&[200803], [Measures].[Return Quantity by Current Date] ) ) }, ( [Time].[Time Hierarchy].[Time Month].&[200803] ), BDESC ) } ON ROWS 

    FROM [Product Summary]

    WHERE ( [Product].[Product Hierarchy].[Product Platform Business].&[Enterprise], [Time In Field].[TL9000 Hierarchy].[TL9000].[LTR], [Product].[Product Reported].&[Product Reported YES], [Measures].[Return Quantity by Current Date], [Customer].[Customer Reported].&[Customer reported Yes], [Return Reason].[Event Return Group].&[RETURN DEFECTIVE] )
    CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL

     

    mdx of the selector member

    TopCount ([Product].[Product PEC Group].[Product PEC Group].Members, 10, [Measures].[Return Quantity by Current Date])

     

     

     

    The following is the mdx view using the filter

    SELECT { [Previous Current Month] } ON COLUMNS ,

    { ORDER( { TOPCOUNT( { [Product].[Product PEC Group].[All].CHILDREN }, 10, ( [Time].[Time Hierarchy].[Time Month].&[200803], [Measures].[Return Quantity by Current Date] ) ) }, ( [Time].[Time Hierarchy].[Time Month].&[200803] ), BDESC ) } ON ROWS

    FROM [Product Summary]

    WHERE ( [Product].[Product Hierarchy].[Product Platform Business].&[Enterprise], [Time In Field].[TL9000 Hierarchy].[TL9000].[LTR], [Product].[Product Reported].&[Product Reported YES], [Measures].[Return Quantity by Current Date], [Customer].[Customer Reported].&[Customer reported Yes], [Return Reason].[Event Return Group].&[RETURN DEFECTIVE] )

    CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL

     

    thanks

    Wednesday, April 23, 2008 3:59 PM
  • Yes, the following is the mdx for the view using the selector member

     

    SELECT { [Previous Current Month] } ON COLUMNS ,

    { ORDER( { TOPCOUNT( { [<##<SET!cmercado!New Set (2)>##>] }, 10, ( [Time].[Time Hierarchy].[Time Month].&[200803], [Measures].[Return Quantity by Current Date] ) ) }, ( [Time].[Time Hierarchy].[Time Month].&[200803] ), BDESC ) } ON ROWS 

    FROM [Product Summary]

    WHERE ( [Product].[Product Hierarchy].[Product Platform Business].&[Enterprise], [Time In Field].[TL9000 Hierarchy].[TL9000].[LTR], [Product].[Product Reported].&[Product Reported YES], [Measures].[Return Quantity by Current Date], [Customer].[Customer Reported].&[Customer reported Yes], [Return Reason].[Event Return Group].&[RETURN DEFECTIVE] )
    CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL

     

    mdx of the selector member

    TopCount ([Product].[Product PEC Group].[Product PEC Group].Members, 10, [Measures].[Return Quantity by Current Date])

     

     

     

    The following is the mdx for the view mdx view using the filter

    SELECT { [Previous Current Month] } ON COLUMNS ,

    { ORDER( { TOPCOUNT( { [Product].[Product PEC Group].[All].CHILDREN }, 10, ( [Time].[Time Hierarchy].[Time Month].&[200803], [Measures].[Return Quantity by Current Date] ) ) }, ( [Time].[Time Hierarchy].[Time Month].&[200803] ), BDESC ) } ON ROWS

    FROM [Product Summary]

    WHERE ( [Product].[Product Hierarchy].[Product Platform Business].&[Enterprise], [Time In Field].[TL9000 Hierarchy].[TL9000].[LTR], [Product].[Product Reported].&[Product Reported YES], [Measures].[Return Quantity by Current Date], [Customer].[Customer Reported].&[Customer reported Yes], [Return Reason].[Event Return Group].&[RETURN DEFECTIVE] )

    CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL

     

    thanks

    Wednesday, April 23, 2008 3:59 PM
  • It would appear the view using the set is actually doing two top counts, while the view using the filter is only doing one top count.  In the view using the set, the set does a top count, and then the view does a top count as well.  So effectively you are seeing a top count of the top count.  If you take the top count filter out of the set view, do you get expected results?

     

    Wednesday, April 23, 2008 4:31 PM
  •  

    No... this is the new mdx

    SELECT { [Previous Current Month] } ON COLUMNS ,

    { ORDER( { [<##<SET!cmercado!New Set (2)>##>] }, ( [Time].[Time Hierarchy].[Time Month].&[200803] ), BDESC ) } ON ROWS

    FROM [Product Summary]

    WHERE ( [Product].[Product Hierarchy].[Product Platform Business].&[Enterprise], [Time In Field].[TL9000 Hierarchy].[TL9000].[LTR], [Product].[Product Reported].&[Product Reported YES], [Measures].[Return Quantity by Current Date], [Customer].[Customer Reported].&[Customer reported Yes], [Return Reason].[Event Return Group].&[RETURN DEFECTIVE] )

    CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL

     

     

     

    Wednesday, April 23, 2008 5:06 PM
  •  

    No... this is the new mdx

    SELECT { [Previous Current Month] } ON COLUMNS ,

    { ORDER( { [<##<SET!cmercado!New Set (2)>##>] }, ( [Time].[Time Hierarchy].[Time Month].&[200803] ), BDESC ) } ON ROWS

    FROM [Product Summary]

    WHERE ( [Product].[Product Hierarchy].[Product Platform Business].&[Enterprise], [Time In Field].[TL9000 Hierarchy].[TL9000].[LTR], [Product].[Product Reported].&[Product Reported YES], [Measures].[Return Quantity by Current Date], [Customer].[Customer Reported].&[Customer reported Yes], [Return Reason].[Event Return Group].&[RETURN DEFECTIVE] )

    CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL

     

     

     

    Wednesday, April 23, 2008 5:06 PM
  • I also notice the filter view has a tuple of "( [Time].[Time Hierarchy].[Time Month].&[200803], [Measures].[Return Quantity by Current Date] )" defined for the numberic expression in the top count, while the set has only "[Measures].[Return Quantity by Current Date]" for the numeric expression.  If you'd like to include a time member in the set, choose to edit the set with Selector, edit the statement that adds the top count items, and choose the time member you want from the "For hierarchy" scroll box.  This will add the time member to the numeric expression in the top count.  Do the numbers look correct after doing that?

    Wednesday, April 23, 2008 6:13 PM
  •  

    Yes, I have the same results

    So, In order to add -as you said- the [Time].[Time Hierarchy].[Time Month].&[200803]  I used the scroll box in and I choose the member called current_month from the [Time hierarchy] this member points to our actual month. (and every month this member changes automatically) .....then the 200803 value appeared under items... this is good for this month

    But for next month, what will happened? The 200803 will change automatically for 200804 or it will stay hardcoded as it is now 200803?

     

    Wednesday, April 23, 2008 8:08 PM
  • Now you're starting to bump into some limitations in SSAS 2005.  This can get a little convoluted, but I'll do my best to layout the different scenarios and options.

     

    Of course the ideal solution would be able to define a top count named set on the cube that was just "Top Ten Products" and then have the query context determine what was meant by "top ten".  That is, if you had the named set on rows and chose current month, the named set would evaluate the top ten for the current month.  Unfortunately, with SSAS 2005 all named sets are resolved on cube connection.  So in our example, if the top ten products don't define a time member, the set will always display the top ten products for all time regardless of what the current query context is because that would be the member from the time hierarchy when the cube connection takes place.  The good news here is that SSAS 2008 introduces a new concept called "dynamic named sets" that will do exactly this.  However, for what you're doing, a cube named set probably won't work.

     

    So, the next option is to use a ProClarity Shared Item.  In this scenario the "top ten" named set is created in ProClarity and saved as a Shared Item.  Now this set becomes dynamic.  That is, the set is reevaluated based on the query context.

     

    The limit here is with regard to the selections on the foreground axes, that is rows and columns.  For instance, if you have the Shared Item named set on rows and you have your "Current Month" named set on cols, you will notice that the current month is not taken into consideration for the members of the named set.   However, if you move the current month to the background and place something else on rows, you will then see the top products for the current month.  This is due to the way queries are resolved.  The named set is not aware of the value on the columns when it is evaluated, so the set doesn't reflect the member on columns.  Only selections in the background will be taken into account when building the named set.

     

    The last option of course is one you've already explored, and it's to define the time value when creating the named set.  However, as you point out, the named set of "current month" is resolved when the set is created, so the time member is no longer dynamic when the set is built.

     

    I realize none of these options are ideal for your situation.  However, I would recommend using a Shared Item in ProClarity for the top count named set and working with the users to see if current month can somehow be placed in the background, with another value going on columns.  Perhaps it can be included as a slicer so it still appears in the view, or maybe the entire solution can go into a dashboard and the current month can be part of a time parameter selection.  However it's done, with time in the background the Shared Item named set will be dynamic, and of course the current month set will be dynamic in the background.

    Thursday, April 24, 2008 6:35 PM
  • Benn, Amanda 

    Thanks you very much for your help .

    It's working, I used the shared item and the current month in the background -as suggested!!! 

    Looks very good!!

     

    Smile

    Claudia

    Thursday, April 24, 2008 9:45 PM