I have the following MDX:

SELECT
{
Measures.ValuedCustomer1, Measures.ValuedCustomer2,
Measures.ValuedCustomer3, Measures.ValuedCustomer4,
Measures.ValuedCustomer5,
Measures.WelcomedCustomer1, Measures.WelcomedCustomer2,
Measures.WelcomedCustomer3, Measures.WelcomedCustomer4,
Measures.WelcomedCustomer5

} ON COLUMNS
FROM [Customer Satisfaction]

Running this MDX against my cube returns a single row with 12 columns.
I really need two separate rows - the first row displaying measures
ValuedCustomer1 - 5; the second row displaying measures
WelcomedCustomer1 - 5. How can I put the measures on separate rows?

TIA

For all practical purposes it's not doable.

public @ the domain below
www.tomchester.net

"Greg" <greg.block (AT) comcast (DOT) net> wrote

On the other hand... You could create 5 conditional calcs such as:

IIF(MyDim.CurrentMember IS [<TheRowOneMember>],
Measures.ValuedCustomer1, Measures.WelcomedCustomer1)

public @ the domain below
www.tomchester.net

The situation you are describing is covered in George Spofford's book "MDX Solutions" from Wiley, p 233 - 237. The explanation is rather involved, and includes creating a separate dummy Scenario cube and Scenarios dimension (with no All level) to identify the two scenarios, in your case "Valued" and "Welcomed". The dimension would have two members, "Valued" and "Welcomed" and the dummy cube would have a single cell. You would then create a virtual cube (in my example I name it Customer Satisfaction Scenarios) to join these two cubes, and measures for Customer1-5. You would then hide some of these dummy objects to present an uncluttered view to users. These member definitions perform string manipulation of the current member of the scenario dimension in order to select the appropriate actual measure

CREATE MEMBER [Customer Satisfaction Scenarios].[Measures].[Customer1] AS
StrToTuple
"Measures.[" + Scenario.CurrentMember.Name + "Customer1]

', SOLVE_ORDER =

Create five of these MEMBER defs. Then when you write your select statement, you would put the scenario values for the row specification

SELECT

Measures.Customer1, Measures.Customer2
Measures.Customer3, Measures.Customer4,
Measures.Customer
} ON COLUMNS
{ Scenarios.[Welcomed], Scenarios.[Valued] } ON ROW
FROM [Customer Satisfaction

The author offers advice on how to generalize this to more complicated situations. Of course, you could just redesign your relational database and cube to include an extra dimension in the first place

- Pau



---
Posted using Wimdows.net NntpNews Component - Posted from SQL Servers Largest Community Website: http://www.sqlJunkies.com/newsgroups/