Results 1 to 7 of 7

Thread: MDX query combining

  1. #1
    Join Date
    Jan 2009
    Posts
    1

    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

  2. #2
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607

    Confirming my Understanding ...

    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

  3. #3
    Join Date
    Jun 2009
    Posts
    6

    Example please

    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,

  4. #4
    Join Date
    Jun 2009
    Posts
    6

    My problem

    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

  5. #5
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607

    Is this a continuation of the above post?

    Or is it a new post altogether?

    Thanks.

    Bill

  6. #6
    Join Date
    Jun 2009
    Posts
    6

    New question

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

  7. #7
    Join Date
    Jun 2004
    Location
    Atlanta and Manhattan
    Posts
    607

    Other Searchers will Thank You ...

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

    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •