Results 1 to 3 of 3

Thread: Union MDX query results

  1. #1
    Join Date
    Jun 2009
    Posts
    6

    Union MDX query results

    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

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

    I Don't See the Need to UNION Here ...

    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

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

    A Little More Explanation?

    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

Tags for this Thread

Posting Permissions

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