OLAP Universe and Case Statement

[tom_krusinski]

Hi-

I have read in several documents that we do and do not support case statements in an OLAP universe. My universe is connected to MS2000 and need to query information for my Xcelsius DB and formatted in such a way that our measures are columns. In a traditional relational DB and universe we do something like this

sum(case when Order_Detail.Quarter = 1 then Order_Detail.NetSales else 0 end)

which would return results like this

q1 q2

100 200

etc.

Now I am not concerned about the date part because I have an object for quarter (foodmart) retuning data this way makes it easy for charts etc in xcelsius.

Thanks

Tom

[tom_krusinski · Accepted solution]

Didier,

I have entered the IIF sent in previous thread (and below) directly into Analysis Services and it works fine..just cannot get it to parse in a universe.

IIF ([Time].CurrentMember = [Time].[1997].[Q1], [Measures].[Store Sales], 0)

I cannt get this simple IIF to work in a universe-I am running BO 3.0 and analysis services 2000, but again it works fine in a Calculated Measure

tom

[Former Member · Accepted solution]

Tom,

You are right, the syntax is a bit incorrect.

Here is the correct definition:

IIF ([Time].CurrentMember.UniqueName = "[Time].[1997].[Q1]", [Measures].[Store Sales], 0)

Do not forget also to enclose your MDX definition in XML tags as following:

<EXPRESSION> IIF ([Time].CurrentMember.UniqueName = "[Time].[1997].[Q1]", [Measures].[Store Sales], 0) </EXPRESSION>

Didier

[tom_krusinski]

Didier,

one minor change to your syntax got it working..notice the &

<EXPRESSION> IIF ([Time].CurrentMember.UniqueName = "[Time].&[1997].&[Q3]", [Measures].[Store Sales], 0)</EXPRESSION>

Thanks for all your help

Tom

[Former Member]

Hi Didier,

Is this IIF Syntax correct in OLAP Universe?

IIF(@Select(Accountancy Entries\Company Code)="AU","1","0")

It gives an error "The supplied XML is not valid"

Please let me know what will be the correct syntax for the above.

(Note: (@Select(Accountancy Entries\Company Code) = [dim_Company].[Company Code].[Company Code]

Thanks in Advance!

[Former Member]

Hi Didier,

Syntax worked by adding <EXPRESSION> tag

<EXPRESSION>IIF(@Select(Accountancy Entries\Company Code)="AU","1","0")</EXPRESSION>.

But no luck in o/p i am getting blank column for the above statement.

Please let me know where i am going wrong.

Thanks in advance!

Regards,

Pallavi

[tom_krusinski]

Didier

I still cannot get it to parse...is there anything you can see in my very simple example

IIF ([Time].CurrentMember = [Time].[1997].[Q1], [Measures].[Store Sales], 0)

it looks very simple but will not parse

thanks

tom

[tom_krusinski]

Didier,

First of all thank you for the quick reply but I cannot get the IIF to parse

IIF ([Date].[Calendar].Currentmember = [Date].[Calendar].[Calendar Quarter].&[2003]&[1], [Measures][Store Sales], 0)

I am using the foodmart cube with a Class name TIME and dimension name QUARTER and a Measures class and Store Sales is my measure.

My question is, do I replace the [Date].[Calendar].Currentmember with [Time].[Quarter].Currentmember? and the same for [Date].[Calendar].[Calendar Quarter].&[2003]&[1] eliminating the 2003?

Thanks and I apologize but trying to understand MDX for demand on the demo team

Tom

[Former Member]

Hi,

Of course, you have to use the appropriate MDX definition for thedimension and the member.

So in your case, it might be:

CASE
WHEN [Time].CurrentMember = [Time].[1997].[Q1] THEN [Measures].[Store Sales]
ELSE 0
END

Didier

[Former Member]

Hi,

Here is the MDX you have to write for the following databases:

MSAS2005:

CASE
WHEN [Date].[Calendar].Currentmember = [Date].[Calendar].[Calendar Quarter].&[2003]&[1] THEN [Measures][Net Sales]
ELSE 0
END

MSAS2000:

IIF ([Date].[Calendar].Currentmember = [Date].[Calendar].[Calendar Quarter].&[2003]&[1], [Measures][Net Sales], 0)

Didier