MDX query combining

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

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

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,

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

Or is it a new post altogether?

Thanks.

Bill

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

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

Bill