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

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

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?

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