Case Statement expression in a cube?

[jv70]

Hi All,
I'm not new to Report Studio but I am new to dynamic cubes.  I know that I'm still in the 'relational database' mindset and that's probably why I just can't seem to wrap my mind around something that to me seems like it should be easy.

I have one dimension that is called Calc Type.  For simplicity sake, lets say the members are 'A', 'B', 'C' and 'D'.  I then have measures that are called 'Calc A Amt', 'Calc B Amt' , 'Calc C Amt' & 'Calc D Amt'. 
All I want to do is create a new measure called 'Amount' that I can display on a crosstab report.  This new measure would hold the value of either Calc A Amt, Calc B Amt, Calc C Amt or Calc D amt.  Basically I'm looking for the cube version of the case statement here:

case
when (Calc Type = 'A') then (Calc A Amt)
when (Calc Type = 'B') then (Calc B Amt)
when (Calc Type = 'C') then (Calc C Amt)
else
(Calc D Amt)
end

Also for this new measure, would it be better to build it in Dynamic Cube designer (Cognos Cube Designer) or would we be better off having it in the report?

Any help would be greatly appreciated!

[cognostechie]

The powercube does that automatically if you put A, B, C and D as members of a level. For dynamic cubes, the case statement should work, not sure though, but I don't see why it wont work that way considering it is ROLAP

[jv70]

Thanks for the feedback.  I was able to get the case statement to work but unfortunately it brings things to a screeching halt.  my report with all the measures was returning within 30 seconds but when I add the column to bring back my new calculated amount, it runs for 5 minutes and then I get a memory error (JVM Heap issues).  I can't make any further adjustments to the JVM heap size.  It's already at 5000MB.

Is there an alternate way of writing this that may be better for cube reporting.  I tried If/Else if as well and it had no effect on the return time.

case when (caption([Calc Type] )= 'A') then ([Calc Amt A])
when (caption([Calc Type] )= 'B') then ([Calc Amt B] )
when (caption([Calc Type] )= 'C') then ([Calc Amt C] )
when (caption([Calc Type] )= 'D') then ( [Calc Amt D])
else ([Calc Amt E]) end

[jv70]

Here is a better example of what I'm working with and what I'm trying to do.  In the final report, I'd only like to display 'Calculated Amt'.  Hope this makes sense.

Type             Calculated Amt |   Calc Amt A   |   Cacl Amt B   |   Calc Amt C  |   Calc Amt D   |
A           |        12.00            |      12.00       |       25.00       |       32.00      |         50.00
B           |        18.00             |      27.00       |       18.00       |       29.00      |         75.00
C           |        22.00             |      14.00       |       21.00       |       22.00      |         72.00
D           |        71.00             |      27.00       |        1.00       |       29.00      |        71.00

[bdbits]

You seem to be ignoring cognostechie, but he is pointing you in the right direction. Your crosstab should be more-or-less automatic, if you have modeled your cube correctly.

Your crosstab in the last post makes it a bit confusing though. Normally in a crosstab, the rows and columns represent dimensions and the intersections are your measures (facts). Yet your earlier posts mention only one dimension and some measures (multiple fact tables?). Is this an actual data source or hypothetical? If actual, I think there is some missing information.

[jv70]

Thanks for your response and no, I didn't mean to ignore cognostechie.  Perhaps I'm just not quite understanding it, hence my rather confusing crosstab example. 

You are correct, i did forget to add a dimension to the crosstab.  I have added it below and it's a member of time.  Directly below the time member, i am displaying all the measures in my measure dimension that are related to my 'Calculated Amt' measure (see my original case statement).   Ultimately though, i would just like to display just the one 'Calculated Amt' measure in my crosstab.

I hope this makes better sense.  Like you said, this very well could be an issue with the cube but I wanted to see if there is way to resolve it in report studio.

                                                                                JAN - 2014
Type             Calculated Amt |   Calc Amt A   |   Cacl Amt B   |   Calc Amt C  |   Calc Amt D   |
A           |        12.00            |      12.00       |       25.00       |       32.00      |         50.00
B           |        18.00             |      27.00       |       18.00       |       29.00      |         75.00
C           |        22.00             |      14.00       |       21.00       |       22.00      |         72.00
D           |        71.00             |      27.00       |        1.00       |       29.00      |        71.00

[Lynn]

I don't see why your cube would have separate measures for each type. This defeats the point of dimensional analysis. I would expect you to simply have one "amount" measure and that it will pertain to one of the members in your type dimension.

You could then put your type dimension members on rows, your desired date member on the column and your amount as the intersection.

Maybe there is more to it than that because I don't really see why your cube currently gives you numbers where the calculation type and the dimension type don't match.

The only other thing I can suggest is that you bring in your type dimension members individually ---not as a set. Then you can nest Calc Amt A on the row next to the A member. Do the same for the remaining items leaving the date member as the only column.

Code Select Expand


                  JAN - 2014
A  | Calc Amt A | 12.00
B  | Calc Amt B | 18.00
C  | Calc Amt C | 22.00
D  | Calc Amt D | 71.00