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
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]