Results 1 to 4 of 4

Thread: Combining MDX queries on KPIs

  1. #1
    Join Date
    Jun 2009
    Posts
    6

    Combining MDX queries on KPIs

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

    "Hard coding" the KPI?

    Hi:

    QUOTE: "... if someone knows how to query the name of the KPI rather than hard coding it I would appreciate that very much also."

    I'm not sure I understand what you mean by "rather than hard coding" it - do you mean querying the KPI directly, rather than creating a calculated member to contain the KPI, which you next query via the CM? Wouldn't something like this work (it returns the correct data from the Adventure Works AS DB)?

    SELECT
    {
    KPIValue("Net Income"),
    KPIGoal("Net Income"),
    KPIStatus("Net Income"),
    KPITrend("Net Income")
    } ON AXIS(0),

    {[Date].[Calendar].[Calendar Year].MEMBERS} ON AXIS(1)

    FROM
    [Adventure Works]

    HTH,

    Bill

  3. #3
    Join Date
    Jun 2009
    Posts
    6
    What I mean by
    QUOTE: "... if someone knows how to query the name of the KPI rather than hard coding it I would appreciate that very much also."

    Is that in the query I posted there is a Calculated Member containing the name of the KPI:

    MEMBER [KPI Name] AS "New Units Sold"

    Can the string "New Units Sold" be queried from the cube rather than hard coded in the MEMBER statement?

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

    If I understand the question ...

    If the KPI with that name exists in the cube, can you not retrieve it using something like this?

    KPIValue("New Units Sold")

    Like the working example I show in my last response? Using this syntax, you would be asking for "the value of the New Units Sold KPI ..."

    Sorry if I'm missing something ...

    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
  •