Rich Taylor · Dec 14, 2015

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?

0 277
Discussion (3)2
Log in or sign up to continue

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,
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.