# Database Discussions > MDX and Analysis Services >  MDX query combining

## Mohammed Imtiaz

I am trying to write a query in MDXwhere i have to combine results of 2 MDX query into one 
query goes like this


With Measure Measures.A as '.....'
select Measure.A on columns,
dimension.DIM_1 on rows
from MYCUBE

With Measure Measures.A as '.....'
select Measure.A on columns,
dimension.DIM_2 on rows
from MYCUBE

is there any way to combine the result of these 2 queries in to one

i tried using UNION but in tht it requires same dimesnion in both the queries (if m not wrong)

plz help me out in this

Mohammed Imtiaz

----------


## Island1

The two calculated members can be put into a single query as two successive WITH MEMBER statements, as I'm sure you know ...  Are you intending to Crossjoin the two dimensions?

If you restate what you want with an example using the Adventure Works cube, we can get you back a working example, if what you want is possible.

HTH,

Bill

----------


## coombsj

For those of us very new to MDX can you show an example of the multiple with and member statments you allude to in your answer.

Thank you,

----------


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


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

Or is it a new post altogether?

Thanks.

Bill

----------


## coombsj

I should have started a new thread with my question above. Sorry for any confusion.

----------


## Island1

Other searchers for this information will find it easier to find if issues / solutions remain separate.  Thanks for understanding.

Bill

----------

