Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

If statement with dimensional data source

Status
Not open for further replies.

Revelator80

Technical User
Jul 14, 2010
14
GB
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
 
Code:
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

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

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top