Search code examples
mdxssrs-2012query-stringssas-tabular

Mdx parameters- pull in all data


I have a situation where I have a parameter in ssrs that contains only a single value. When that value gets passed over in the mdx query it allows the results to be filters. The values are directly linked to a dimension in the tabular model. However the tabular model only contains ( null, black, blue, red). I can easily get results when the parameter is black, blue or red but I need the results for "Colors" which is a top of the house values that requires us to display the results for all three colors. Any ideas?

Code

Select
Non Empty
{ ( Measures.TotalColors]} on columns
,Non Empty
{
[DifferentColors].[Color2].[Color2].allmembers
}
Dimension Properties
Member_Caption
,Member_Unique_name
on Rows
From [Colors]

Result

Color2   TotalColors
null    100
black   10
blue    23
red     11

When the parameter is black then it needs to show 10 and when its blue then it needs to show 23. This is easy using parameters.

However I need the following result when I select "Colors" which should bring in all the values in the dimension.

Intended Result

Color2    TotalColors
Colors   144

Solution

  • You could simply aggregate data on higher level for all member, however this will not give you Colors

    Select 
      Non Empty{ ( Measures.TotalColors]} on columns,
      Non Empty{[DifferentColors].[Color2].[All]} on Rows
    From [Colors]
    

    Or if you want to show Total additionally to list of colors you have to introduce calculated dimension member:

    WITH MEMBER [DifferentColors].[Color2].[Color2].[Colors] AS 
      SUM([DifferentColors].[Color2].[Color2].MEMBERS, Measures.TotalColors)
    Select 
      Non Empty{ ( Measures.TotalColors]} on columns,
      Non Empty{[DifferentColors].[Color2].[Color2].allmembers} on Rows
    From [Colors]