locked
Creating Hierarchical Dashboard filters using MDX RRS feed

  • Question

  • Hi,

    I want to create a set of filters which are hierarchical in nature. The data source for these is a OLAP Cube (Hiererchical dimensions).

    I do not know how to write a MDX query to do this. It would be great if somebody helps me in writing these queries.

    Here is the kind of filter i'm expecting:

    Suppose there is a hiererchical dimension Geography. This dimension will contain parent and child levels. For eg. parent level member will be Asia and child members will be India,China,Manila,Japan etc. The parent level is named as Continent and Child level is named as Country.

    I want the resultant filter to display Asia at the parent level and when the expand button (+) is clicked all the countries under it to be displayed.

    Any help is highly appreciated

    Thanks,

    Krishna

     

    Wednesday, March 31, 2010 12:18 PM

Answers

  • You would simply use the MDX Query as your Filter option and type the following - [DimensionName].[Geography].Members

    You would need to fill in the name for the dimension that is being referenced and I am assuming the name of the user-defined hierarchy here is Geography.  Then you would select the multi-select tree view display for the filter.

    If you don't want to include the [All] member then simply use - [DimensionName].[Geography].Children

    If you want to remove members that do not have any data for an associated measure then wrap this in a NONEMPTY like - NONEMPTY([DimensionName].[Geography].Members, Measures.[MeasureName])

     


    Dan English's BI Blog
    • Proposed as answer by JanakaV Tuesday, April 13, 2010 2:53 PM
    • Marked as answer by Heidi Tr - MSFT Wednesday, May 5, 2010 7:49 PM
    Tuesday, April 13, 2010 1:08 PM

All replies

  • I know its a late reply but I just encountered something similar and here's what I did.

    You need to use "hierarchize" function. Create a named set and then create filter based on this named set. You query will look like something like this

    CREATE DYNAMIC SET CURRENTCUBE.[Sales]

     AS hierarchize(

    {

    descendants([Geography].[ Hierarchy].[Continent].&[Asia],

    [Geography].[ Hierarchy].Levels (4),

     

    SELF_and_after

    )}) ;

    level will dependent on the depth of parent-child relationship.

     

    Hope that was useful.

     

     

     

    Friday, April 9, 2010 2:05 PM
  • If the user defined hierarchy is already defined in the dimension then you simply referene the hierarchy and select the tree or multi-select tree option depending on how you want this displayed.

    If you are going to go over 500 members in the filter then you will need to reference this - Changing the limit on the number of items returned in a filter

     


    Dan English's BI Blog
    Tuesday, April 13, 2010 12:35 AM
  • Thanks Dan for the reply... we have tried using the hiererchy directly. But the problem here is that whenever there is any updates in the cube (tables)..even after processing the cube, the updates doesn't get reflected at the filter. We have to re-deploy the dashboard everytime there is an update on tables of cube. This is like hard-coding the filter parameters.

    Hence we are researching for MDX which is more dynamic in nature.

    We would appreciate if you could throw some light in this scenario.

    Tuesday, April 13, 2010 6:42 AM
  • You would simply use the MDX Query as your Filter option and type the following - [DimensionName].[Geography].Members

    You would need to fill in the name for the dimension that is being referenced and I am assuming the name of the user-defined hierarchy here is Geography.  Then you would select the multi-select tree view display for the filter.

    If you don't want to include the [All] member then simply use - [DimensionName].[Geography].Children

    If you want to remove members that do not have any data for an associated measure then wrap this in a NONEMPTY like - NONEMPTY([DimensionName].[Geography].Members, Measures.[MeasureName])

     


    Dan English's BI Blog
    • Proposed as answer by JanakaV Tuesday, April 13, 2010 2:53 PM
    • Marked as answer by Heidi Tr - MSFT Wednesday, May 5, 2010 7:49 PM
    Tuesday, April 13, 2010 1:08 PM
  • Hello Dan,

    I have a limited knowledge of MDX but if you put expression [DimensionName].[Geography].Members as filter expression then the very top member will be 'all' member and then rest of the memberrs will be displayed as a tree structure. 

    I thought krishna wanted to start in the middle of the parent-child hierarchy and then display its children. 

    Is there a way of doing this?

    Thanks

    Sean

    Tuesday, April 13, 2010 2:58 PM
  • Hi,

    Thanks for your suggestions. I did follow the advices given here and it seems i have almost achieved what i wanted. But i am stuck at one critical point.

    My requirement in the PPS dashboard is that, the filter should take value from one column for display and should pass the value from another column to the SSRS report as an input parameter.

    For eg. Suppose i have created a SSRS report that displays population of continents (same example that i have used in the beginning of the thread) from a table which has 3 columns viz Continent_ID,Continent_Name,population(the report will use a simple query like "select population from continent_dim where Continent_ID=@continentkey" to populate).

    For this report, the dashboard filters should display(from the hierarchical dimension) the continent_name values, but while passing the parameter to the SSRS it should pass the value from Continent_ID.

    In other words, the filter should display value from column 'x' but pass the value from column 'y'.(both columns x and y are related in one-to-one).

    I did try tweaking some properties of attributes in ssas 2005 like namecolumn,keycolumns,valuecolumn, but none of them are helping. The result that i am getting is that, whichever value is visible in the dashboard filter, the same is being passed to the report as the input parameter.

    Do let me know if i have articulated the problem properly or if some more info is required.

    Any help in this regard is highly appreciated.

     

    Thanks,

    Krishna

     

     

     

    Monday, April 19, 2010 7:53 AM
  • Hi Krishna,

    Can you try this.

    1. Change the name column property of the continentid key column in Continent dimension to continentname.

    2. Change the query for ssrs parameter to "select population from continent_dim where ContinentName=@continentname"

    3. When you attach dashboard filter to ssrs report use Display Value instead of MemberUniqueName..

    Hope this will work.

    Ta

    Sean

    Monday, April 19, 2010 12:00 PM
  • Another suggestion would be to create the SSRS report with the SSAS data source and dataset instead and simply use the Member UniqueName from the filter which will be the fully qualified dimension member value to the report.  Assuming you might have the Population value setup as a Member Property in the dimension to retrieve, but maybe you have setup the dimension to also be a measure group as well.

    If you pass the Member UniqueName to the report you would need to basically create a second parameter or include some code to parse out the key value from the fully qualified name that is being passed to the report currently.  The alternate approach would be to use the logic that Sean has posted.


    Dan English's BI Blog
    Monday, April 19, 2010 12:42 PM
  • Hi Sean,

    Thanks for repeatedly answering all my queries.

    Once we set the name column property of the continentid key column to continent name, in pps filter both the display as well as the parameter passed will be the continentname.

    What i have found out that, the value displayed in PPS filter and the value passed as parameter to SSRS both will be the same column that is set as name column property.

    But my requirement is that in PPS filter, ContinentName should be visible in the filter and the value passed to the SSRS report should be the corresponding Continent_ID. For eg. If I have a record in Continent table with values as continent_Id=1, continentName='Asia' and population=100000. Then the filter should display Asia and when i select Asia, the parameter that goes to the report should be its Id i.e 1 and then the population should be displayed in the report as 100000.

    Regards,

    Krishna

    Monday, April 19, 2010 2:11 PM
  • Hi Dan,

    If i have understood correctly, your suggestion is to connect SSRS directly to SSAS. The problem with this approach is that SSRS does not have a facility for tree-styled (hierarchical) input parameters for a report. It has only cascaded parameters which does not serve my requirement.

    And one more thing to be noticed is that i am not using any measures in SSAS. SSAS is purely used as a means of populating the filters in PPS. I know this is a strange architecture that i am using but we use the SSAS cube(infact only the hierarchical dimension) only for filters and then the entire processing is done using Stored procs. In the cube, we have an empty fact table i.e truncated. So the cube is for a dummy purpose. No measures and facts!

    And the way i am using the dimension is: I Have a dimension named geography. It has a hierarchy of four levels. Continent-->Country-->State-->City. This hierarchy is named as Geo_hierarchy.

    Then in pps filter i have used a named set which uses the mdx query: descendants([Geography].[Geo_Hierarchy], , self_and_after). This mdx will retrieve me a hierarchical filter.

    I have explained the problem i am facing in the reply to Sean above this giving an example.

    Thanks,

    Krishna

    Monday, April 19, 2010 2:24 PM
  • Hi Sean,

    Just continuing my reply. You can refer to my reply to Dan below this reply. There i have explained the architecture i am using. This might help you in better understanding the problem and helping me.

    Thanks,

    Krishna

    Monday, April 19, 2010 2:25 PM
  • Unfortunately you are misinformed with SSRS not being able to support hierarchy filters with SSAS data sources - Using Reporting Services (SSRS) with SSAS data.  In setting up the filters in the designer you can simply check the Parameters box and if this is a hierarchy it will display the data accordingly (http://public.blu.livefilestore.com/y1pxXWvjx_p82FcOaEgLMgqp1UfIXhGiy4Lrncyc6nKQNTXyunPu2z3RNn5IwmpH7Mpn4NejU8lVp15BHJgUPN0ZQ?PARTNER=WRITER).

    Sean explained how you can get at the value you want and I also stated that you can simply parse the Member UniqueName which is the key value associated with the display value to get at the ID value you are seeking.  Your value would like something like [Geography].[Geography Hierarchy].[Continent].&[1].


    Dan English's BI Blog
    Monday, April 19, 2010 2:39 PM
  • Adding to Dan's reply above here's how you can check what value is being passed from filter

    http://nickbarclay.blogspot.com/2008/02/debugging-filter-links-with-web-page.html

     

    Monday, April 19, 2010 3:09 PM