# Database Discussions > MDX and Analysis Services >  Union MDX query results

## coombsj

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:


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

I may be missing something, but what is the need for UNIONing here?  If you have the KPIs in place, why can't they be straightforward pulls into your matrix?

Sorry if I misunderstand the need ...

Bill

----------


## Island1

What do you mean by "... how to query the name of the KPI rather than hard coding it ..." ?  Could you perhaps give an example?

Thanks!

Bill

----------

