KPI List handling of SSAS KPIs with null values

When displaying KPIs from Analysis Services 2005 in Sharepoint using the KPI List, we noticed something unexpected. It seems that if the KPI Value evaluates to Null (ie there's no data for the KPI), the goal value doesn't display either, even if it has a value. So, for example, if the goal is "10", but the value is Null, neither value displays. That doesn't seem right to us - just because there's no data for the KPI doesn't mean that there isn't a valid goal.

Is there anything we can do to change this behavior Note that this is inconsistent with how Excel 2007 displays KPIs - Excel acts as we would expect.

Answer this question

KPI List handling of SSAS KPIs with null values

Aaron Oneal
Phil,
In this specific case (which is looking at Goal, not Status or Trend), SSAS evaluates and returns the goal just fine when queried with MDX (the goal here is set to a fixed value, like 10). It's only Sharepoint (and BI Studio, as it happens) that doesn't show the goal value.

Excel 2007, incidentally, works fine (using the CUBE functions).
robhendershot
Hi Kevin,

This might be "by design", but it makes life a little difficult. And it is not only SharePoint that has the issue - we have a custom coded client with the same problem since SSAS will not/cannot evaluate the Trend or Status if the KPI Value is null.

One way round it might be to wrap the KPI MDX expression in CoalesceEmpty, as in CoalesceEmpty(KPI MDX Expression, 0).
See http://msdn2.microsoft.com/en-us/library/ms146080.aspx for more details.

This has worked for us - hope it helps.

--Phil
KPI List handling of SSAS KPIs with null values

Answer this question