Creating Trend line (scorecard) from data rather than counts
I need to create a KPI similar to the DeepSee.Model.KPIs.DemoTrendLines kpi in Samples. This kpi supports a scorecard widget with a trend line. It shows patient counts by city with a trend line of the count of patients over a decade. What I want is to show the trend line based on the average allergy score ([Measures].[Avg Test Score]). A pivot table query that shows the data I would want to base the trend line on is:
SELECT NON EMPTY [BirthD].[H1].[Decade].Members ON 0, NON EMPTY [HomeD].[H1].[City].Members ON 1 FROM [Patients] %FILTER [Measures].[Avg Test Score]
Has anyone done this?
Comments
Found the solution. The following MDX gives the values that I want.
SELECT {MEASURES.[Avg Test Score],
%LIST(NONEMPTYCROSSJOIN([BirthD].[H1].[Decade].Members,{[Measures].[Avg Test Score]}))} ON 0,
NON EMPTY homed.city.MEMBERS ON 1
FROM patients
I think the key was to enclose the measure in the NONEMPTYCROSSJOIN function in curly braces. I had not done this in a previous attempt at getting this to work.
It does not make sense that you would have to use curly braces here. I get the same results for your query with or without them.
But anyway, am glad you got the MDX you wanted.
This documentation page describes the use of { } within MDX. As Lexi points out, a single member inside of { } should behave the same way without the { }. This is the syntax for a set. If you come across something that is not working as expected without the { }, please consider submitting a WRC so we can get the behavior resolved.