Creating Trend line (scorecard) from data rather than counts

Question

Question

Rich Taylor · Dec 14, 2015

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?

Discussion (3)2

Log in or sign up to continue

Peter Steiwer · Oct 14, 2016

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.

1 0

score 0 · Answer 1 · 2015-12-14T12:00:00-05:00

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.

score 0 · Answer 2 · 2015-12-16T10:40:17-05:00

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.