KPI Goal Value doesn't change after filtering the date

Hello everyone,

i'm new to Analysis Services and trying to build a Data Warehouse especially for using KPIs in it. Watching the famous AdventureWorksDW example i try to use the MDX Statements likewise. I want to use a KPI just like the first in the list "Growth in Customer Base". But when using a MDX Statement for the Goal Expression like this:

Case
When [Date].[Fiscal].CurrentMember.Level Is [Date].[Fiscal].[Fiscal Year]
Then .30
When [Date].[Fiscal].CurrentMember.Level Is [Date].[Fiscal].[Fiscal Semester]
Then .15
When [Date].[Fiscal].CurrentMember.Level Is [Date].[Fiscal].[Fiscal Quarter]
Then .075
When [Date].[Fiscal].CurrentMember.Level Is [Date].[Fiscal].[Month]
Then .025
Else "NA"
End

after filtering the resultset by the "Date" Dimension -> "Fiscal" Hierarchy -> equals "FY 2004" in the KPI-Browser it just shows "NA" like it does in the AdventureWorksDW, too. Is there a way to use a similar example in AdventureWorksDW that changes the goal dependend on the Filter like my description? All other KPI examples in AdventureWorksDW the goals depend on other values already contained in the DB and not dependend on the Filter Expression used.

Unfortunately, I think that the KPI browser may be misleading because, "filtering the resultset by the "Date" Dimension -> "Fiscal" Hierarchy -> equals "FY 2004" in the KPI-Browser" is probably generating a subselect, rather than applying the condition in the where clause. Try an MDX query directly, like:

>>

select {KPIGoal("Growth in Customer Base")} on 0

from [Adventure Works]

where [Date].[Fiscal].[Fiscal Year].&[2004]

-

Growth in Customer Base Goal
0.3

>>

|||Hi Deepak,

thank's a lot for your help! That works perfectly. But my goal is to visualize the KPIs with the Business Scorecard Manager and hoped I just have to give him the cube and he visualizes it. Does the Business Scorecard Manager filter with subselects or in a where clause?

I hope you or someone else can help me out with this second point and I will be happy ...

Claudio|||

Hi Claudio,

My guess is that BSM 2005 filters with where clause, since I think that it works with AS 2000 cube as well. But you could find out for sure by tracing the MDX query from BSM to AS 2005, using SQL Profiler.