I am having a problem with the Rank function when the user selects multiple dates from a tree prompt. I am pretty sure the problem is related to getting multipe members back in the prompt. I have a data item [SelectedPeriods] that contains a prompt macro:
#promptmany('pSelectedPeriods','memberuniquename', '', 'set(', ' [Dimensions].[Date].[Date].[Year] ', ')' )#
I am using the data item [SelectedPeriods] as a slicer. If the user selects a single data item, '2014', the rank works correctly, but if the user selects multiple data items, '2013 Q4' and '2014 Q1' for example, the rank is not calculated, I get '--' instead.
Thanks in advance for your help.
rank is a relational function. Tree prompts belong in dimensional. try using topCount instead.
I have a TopCustomers set expression that is using the topCount
topCount( filter( [Dimensions].[Customer].[Customer].[Customer],[Quantity] <> null ) , ?pTopCustomerCount?, [Revenue] )
This returns the set of top customers but does not enumerate them.
The "AUTHOR REPORTS WITH MULTIDIMENSIONAL DATA (V10.2)" describes rank as a dimensional function
rank (numeric_expression [ASC|DESC] [tuple member_expression { , member_expression }] within set set_expression)
It does seem somewhat redundant to get a set of top customers and then rank them - but how else can I enumerate the list and sort the list asc or desc?
Well, you could sort them by the actual measure... 8)
I was able to resolve this issue using the dimensional format for rank. My final expression was:
rank( [Revenue] within set [TopCustomers] )
I also removed the filter from my topCount expression winding up with:
topCount([Dimensions].[Customer].[Customer].[Customer],?PTopCustomerCount?,[Revenue])
The filter function was redundant and slowed down the results. I probably put it in there while building the report basics and never took it out.