Combining MDX queries on KPIs

[coombsj]

Here is what I am trying to accomplish. My code could be modified to use the two KPIs found in the Adventure Works Analysis Cube.

I am working with SQL Server 2008

I have created a set of KPIs in SSAS that I would like to display in a table/matrix in SSRS.
The 4 KPIs are:
- Units Sold
- Total PVR
- Front PVR
- Back PVR

I would like to show all 4 in a table/matrix for a given store something like:

Store Name KPI Name KPI Value KPI Goal KPI Status
------------------------------------------------------------
Store 1 Units Sold 100 120 -1
Total PVR 2000 1900 1
Front PVR 400 300 1
Back PVR 1500 1700 -1

Store 2 Units Sold 100 120 -1
Total PVR 2000 1900 1
Front PVR 400 300 1
Back PVR 1500 1700 -1

I have an MDX query to pull the values I want for one KPI and in TSQL I would union the results of the first query with a similar query for each of the other KPIs, but I can't seem to find a way to do this in MDX.

Here is my query for one KPI:

Code:

  WITH 
		MEMBER [KPI Name] AS "New Units Sold"
		MEMBER [KPI Value] As KPIValue("Units_Sold")
		MEMBER [KPI Goal] AS KPIGoal("Units_Sold")
		MEMBER [KPI Status] AS KPIStatus("Units_Sold")
		MEMBER [KPI Trend] As KPITrend("Units_Sold")
  SELECT 
	NON EMPTY 
		{ 
		  [KPI Name],
		  [KPI Value], 
		  [KPI Goal], 
		  [KPI Status], 
		  [KPI Trend]		  
		} ON COLUMNS,
	FILTER 
		(
			[Dms Ws Group Accounts].[Ws Group Id].[Ws Group Id].MEMBERS,
			NOT(ISEMPTY(KPIValue("Units_Sold")))
		) DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
	FROM [DSP Prototype Cube] 
	WHERE
		(
			[SaleType].[Saletype].[Saletype].&[new],
			[Time].[Month].&[2009-03-01T00:00:00]
		)
	CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Please Help. And if someone knows how to query the name of the KPI rather than hard coding it I would appreciate that very much also.

Thank you,
Jonathan

[Island1]

Hi:

QUOTE: "... if someone knows how to query the name of the KPI rather than hard coding it I would appreciate that very much also."

I'm not sure I understand what you mean by "rather than hard coding" it - do you mean querying the KPI directly, rather than creating a calculated member to contain the KPI, which you next query via the CM? Wouldn't something like this work (it returns the correct data from the Adventure Works AS DB)?

SELECT
{
KPIValue("Net Income"),
KPIGoal("Net Income"),
KPIStatus("Net Income"),
KPITrend("Net Income")
} ON AXIS(0),

{[Date].[Calendar].[Calendar Year].MEMBERS} ON AXIS(1)

FROM
[Adventure Works]

HTH,

Bill

[coombsj]

What I mean by
QUOTE: "... if someone knows how to query the name of the KPI rather than hard coding it I would appreciate that very much also."

Is that in the query I posted there is a Calculated Member containing the name of the KPI:

MEMBER [KPI Name] AS "New Units Sold"

Can the string "New Units Sold" be queried from the cube rather than hard coded in the MEMBER statement?

[Island1]

If the KPI with that name exists in the cube, can you not retrieve it using something like this?

KPIValue("New Units Sold")

Like the working example I show in my last response? Using this syntax, you would be asking for "the value of the New Units Sold KPI ..."

Sorry if I'm missing something ...

Bill