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:
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