If statement with dimensional data source

[Revelator80]

Hi,

I am building a report off a powercube and need to group data in a crosstab to identify whether something has been mapped or not. Unmapped items are assigned an 'Unregistered' Id so I have tried to create a data item that uses an IF statement for:

if([CPD_Cube].[All Suppliers].[All Suppliers].[Supplier Name]='Unregistered') then ('Unmapped') else ('Mapped')

I have tried various iterations and either get no data, no distinction (i.e. it all just gets lumped under 'Data Item 3' or this error message:

Unable to support query items from the same dimension on different report dimensions: dimension="[MEASURES]"; edge="axis(0)"; edge="axis(2)".

I'm new to all this so any help anybody can give would be greatly appreciated.

Cheers

 

[blom0344]

CASE
[CPD_Cube].[All Suppliers].[All Suppliers].[Supplier Name].[CurrentMember]
WHEN
[CPD_Cube].[All Suppliers].[All Suppliers].[Supplier Name].[Unregistered]
THEN
'Unmapped'
ELSE
'Mapped'
END

Ties Blom

 

[Revelator80]

Hi,

Thanks for that, it works to a certain extent insofar as it splits out the Unmapped spend. However, the totals for Mapped spend are the overall total e.g. total spend = 100, unmapped = 20, mapped = 100!! What is happening there!? Logically it looks like it should be 100,20,80...

Cheers
David

 

[blom0344]

Yes, but building these type of constructs on a dimensional source is not very trivial. Unlike SQL , I think you need to explicitly write the MDX expression for the totals as well.

MDX is not my expertise, but The OLAP forum has some experts running around..

Ties Blom