SQLServerCentral / SQL Server 2005 / Business Intelligence / KPIs and how to hide them / Latest Posts

RE: KPIs and how to hide them

RollTide — Mon, 14 Jan 2013 14:08:46 GMT

Solution is to add the following to the beginning of your status expression:CASE WHEN IsEmpty(KpiValue("MyKPI")) THEN NULL

RE: KPIs and how to hide them

mtassin — Mon, 21 Sep 2009 10:19:13 GMT

If I can't force SSAS to not calculate KPIs when there is no data, should I instead cut my customer's dimension down to just those that I have records for?

RE: KPIs and how to hide them

mtassin — Thu, 17 Sep 2009 12:00:10 GMT

Ok the client is Excel 2007.The trouble is that when I bring a KPI status or KPI trend onto the cube view it gives them for dimension members that have no data (and likely will never have data). Which means instead of the couple hundred or so customers who use this product we have 10,000 plus customers triggering a KPI calculation on non-existant data. I just want to suppress the call to get those calculations.

RE: KPIs and how to hide them

mtassin — Thu, 17 Sep 2009 09:13:56 GMT

I guess rather I should aski how to not perform KPI calculations when there is no data to perform them on. I can do that to a degree with a calculated measure.i.e. I can give it non empty behavoir to follow. But there isn't an option for that on the KPI page.My formulas look like thisValue Expression:[code]IIF(NOT ISEMPTY([Measures].[Average Loadtime]),[Measures].[Average Loadtime],Null)[/code]Goal Expression:[code]IIF(NOT ISEMPTY([Measures].[Average Loadtime]),2000,NULL)[/code]Status Expression:[code]CASEWHEN (ISEMPTY([Measures].[Count of Instances]))THEN NULLWHEN ([Measures].[Average Loadtime]) <= 2000 then 1WHEN ([Measures].[Average Loadtime]) > 2000 and ([Measures].[Average Loadtime]) <= 5000 then 0WHEN ([Measures].[Average Loadtime]) > 5000 then -1END[/code]Trend Expression:[code]CASEWHEN isempty(([Measures].[Average Loadtime])) then NULL when ([Measures].[Average Loadtime]) > (([Measures].[Average Loadtime]),[Time].[Calendar].Prevmember) then 1 WHEN ([Measures].[Average Loadtime]) = (([Measures].[Average Loadtime]),[Time].[Calendar].Prevmember) then 0WHEN([Measures].[Average Loadtime]) < (([Measures].[Average Loadtime]),[Time].[Calendar].Prevmember) then -1END[/code]But it's still leaving all the customers in the list who don't have values to measure the KPI against

KPIs and how to hide them

mtassin — Thu, 17 Sep 2009 08:32:29 GMT

So I'm coming along developing my SSAS cube. And I have a small problem.I have a shared dimension that has every customer the company has in it.My measures are for the performance of a single application that some of our customer use.When I just pull the measures into the cube on the client I get just the customers who have reported values for the measures and customers not using said product don't show up.But...If I add my KPI to the display, then suddenly every customer of the company shows up.How can I hide KPIs for customers who don't have any data at all?