Hello everyone,
As a part of my cascading parameter for SSRS report, I want to show the list of Benefit names associated to a Group .
So here is my MDX query:
WITH MEMBER [Measures].[ParameterCaption] AS [BenefitGroup].[Standard Benefit Name].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [BenefitGroup].[Standard Benefit Name].CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[ParameterLevel] AS [BenefitGroup].[Standard Benefit Name].CURRENTMEMBER.LEVEL.ORDINAL
SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,
NONEMPTY([BenefitGroup].[Standard Benefit Name].CHILDREN
)
ON ROWS
FROM [Cube]
where [BenefitGroup].[Group ID].&[5001]&[N/A]
Results of MDX query are:

MDXResult.png

But the Dimension table has 4 benefits for the Group:
select * from DimBenefitGroup where GroupID = '5001'

SQLResult.png

I believe the other 2 benefitd are no puled becuase they have N/A in the Link column. How do I modify my query to pull all the 4 Benefitnames for the input Group?
Thank you for your response in advance!

MDXResult.pngSQLResult.png