how to write case statement in universe

[Former Member]

Hi All,

I have to write Case statement in select clause as

CASE

WHEN @Select(calc\quarter )=1

THEN calc.Quarter = 4 AND rep.year=2009 (here my requirement is to give the values to columns of different tables directly)

ELSE calc.Quarter AND rep.year

END

Here I'm validating a single condition and if it is true, THEN part should be executed. In THEN part again I'm assigning some values to the columns of different tables. One column from table calc and another from table rep. While parsing this I'm getting error at '='.

How to achieve my requirement.

Any help is highly appreciated.

Cheers,

Suresh Aluri

Edited by: Suresh Babu Aluri on Apr 30, 2010 1:11 PM

[former_member142702 · Accepted solution]

Hi

the THEN part in your statement containts a condition and this does not really make sense.

Can it be that the following syntax works for you? (I underlined the modified parts)

CASE

WHEN @Select(calc\quarter )=1

AND calc.Quarter = 4 AND rep.year=2009

THEN calc.Quarter ELSE rep.year

END

You have to make sure to add all the required tables in the definition of your object that contains the CASE caluse

Regards,

Stratos

[Former Member · Accepted solution]

Hi Stratos,

Thanks for your reply.

My requirement is like this

CASE

WHEN @Select(PA Report Elements\Quarter No)=1

THEN Reporting.ReportingMonth.Quarter = 4 AND reporting.ReportingYear=2009

ELSE Reporting.ReportingMonth.Quarter AND reporting.ReportingYear

END

i.e. In THEN part for the Quarter column the value should be assigned as 4, similar to year it should be 2009 and those are not conditions. I have to assign values for the above two columns. Is that possible ...

Cheers,

Suresh Aluri.

[former_member142702 · Accepted solution]

No this is not possible.

What you can do is to include two additional objects (if not already available) eg ReportingQuarter and ReportingYear in your universe:

You can then provide the following definition for this objects:

for ReportingQuarter:

CASE

WHEN @Select(PA Report Elements\Quarter No)=1

THEN 4

ELSE Reporting.ReportingMonth.Quarter

END

for ReportingYear:

CASE

WHEN @Select(PA Report Elements\Quarter No)=1

THEN 2009

ELSE reporting.ReportingYear

END

USe then those objects places in your universe and/or your reports instead of the original ones (Reporting.ReportingMonth.Quarter and reporting.ReportingYear).

Regards,

Stratos