{Resolved} Display Parent but Filter on Children - MDX

[LFC]

Hello All,

We have a OLAP CUBE Datasource with "Location" dimension with Country > State > City as the various levels.

                                                                                     All Locations
                                                    USA                                                               CANADA
                     CALIFORNIA                            FLORIDA                        QUEBEC                 ALBERTA
         SACREMENTO  SAN FRANSICO       TAMPA   ORLANDO            MONTREAL      EDMONTON  CALGARY

We are trying to render simple bar chart with the Country Level as the x-axis and Revenue as Measures.

The business requirement is to let the users have a prompt via which they can filter the data by any of the levels in "Location" dimension as well....We want to filter by the any level but only use the Country level in the chart x-axis. for example the user can filter the chart for certain cities or for certain states,  by default nothing will be selected in the prompt. So only the countries which the user selected or had one or more of the child members( states or cities ) selected will be plotted in the x-axis.

I am able to achieve the above but the additional requirement is which i have not been able to solve.

If the user make a selection then the Total for the Country should be sum of the respective children members selected by the user.

I have tried a few functions in MDX but the ones that would make sense (eg ancestors, parent) seem to be member specific so I cannot apply a 'filter' to to bring back the children leaf node level 0 and then display the ancestors of these results...or at least it does not seem you can.

So from the above dimensional hierarchy..If the user selects "Sacramento" , "SAN FRANSICO" and "EDMONTON" in the prompt then

both USA and CANADA should be plotted in the chart x-axis but the totals for the USA will be sum of Sacramento+San Fransisco and CANADA will be total for EDMONTON.

currently the two ways to achive the solution is to

1) add a detailed filter ( location hierarachy in ( selected prompt values) ) which will do the trick or
2) create a new dimension with just the Countries as the only level.

I am trying to avoid the above two quick fixes...any help would be greatly appreciated.

[MFGF]

Hello All,

We have a OLAP CUBE Datasource with "Location" dimension with Country > State > City as the various levels.

                                                                                     All Locations
                                                    USA                                                               CANADA
                     CALIFORNIA                            FLORIDA                        QUEBEC                 ALBERTA
         SACREMENTO  SAN FRANSICO       TAMPA   ORLANDO            MONTREAL      EDMONTON  CALGARY

We are trying to render simple bar chart with the Country Level as the x-axis and Revenue as Measures.

The business requirement is to let the users have a prompt via which they can filter the data by any of the levels in "Location" dimension as well....We want to filter by the any level but only use the Country level in the chart x-axis. for example the user can filter the chart for certain cities or for certain states,  by default nothing will be selected in the prompt. So only the countries which the user selected or had one or more of the child members( states or cities ) selected will be plotted in the x-axis.

I am able to achieve the above but the additional requirement is which i have not been able to solve.

If the user make a selection then the Total for the Country should be sum of the respective children members selected by the user.

I have tried a few functions in MDX but the ones that would make sense (eg ancestors, parent) seem to be member specific so I cannot apply a 'filter' to to bring back the children leaf node level 0 and then display the ancestors of these results...or at least it does not seem you can.

So from the above dimensional hierarchy..If the user selects "Sacramento" , "SAN FRANSICO" and "EDMONTON" in the prompt then

both USA and CANADA should be plotted in the chart x-axis but the totals for the USA will be sum of Sacramento+San Fransisco and CANADA will be total for EDMONTON.

currently the two ways to achive the solution is to

1) add a detailed filter ( location hierarachy in ( selected prompt values) ) which will do the trick or
2) create a new dimension with just the Countries as the only level.

I am trying to avoid the above two quick fixes...any help would be greatly appreciated.

Hi,

Seems to me all you need here is a slicer filter in the query of your chart. The syntax for the filter would be:

set([Your Location Hierarchy] -> ?Your parameter?)

If you then use the Country level in your chart, you should only see measure values plotted for the members selected from the resultant tree prompt.

If the tree prompt is also on the report page, you'd need to set a Default Selection of the top level All Locations member.

Cheers!

MF.

[LFC]

Thanks MFGF. Using a slicer does not work in this case


                                                                                     All Locations(80)
                                                       USA (40)                                                                            CANADA(40)
                     CALIFORNIA(20)                                        FLORIDA(20)                          QUEBEC(20)                   ALBERTA(20) 
         SACREMENTO(10)    SAN FRANSICO(10)       TAMPA(10)   ORLANDO(10)            MONTREAL(10)      EDMONTON(10) CALGARY(10)

When i use a slicer with the Location Prompt and If i select just California and Montreal in the LocationPrompt...The x-axis will plot USA and CANADA categories but instead of having values 20 for USA and 10 for CANADA it will still have values 40 for USA and 40 for CANADA.

i was able to write this following mdx which works instead of using a slicer.

the query will have two data items.

data item 1: ( Countries)
code: <country level> from location dimension.

data item 2: ( Measure)
code:

total( <Revenue>< witin set
intersect ( #promptmany('pLocationHier', 'memberuniquename', 'members( <Region Level> from location dimension )', 'set(', '', ')')# , union( descendants (  currentMember ( < Location Hierarchy > ) , 2 , beforewithmember  )  ,parent (  currentMember ( < Location Hierarchy > )  )  )  ) )


that seemed to do the trick without using detail filter or a new dimension. Thanks