COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Report Studio => Topic started by: Arsenal on 05 Apr 2007 10:19:11 PM

Title: OK, I give up. What am I doing wrong?Crosstab
Post by: Arsenal on 05 Apr 2007 10:19:11 PM
basically, crosstab needs to be created against a row and 4 measures. The row is coming from a status_cd query item for which I'm applying a filter in the model that keeps the status_cd down to 4 values - let's call them a, b, c and d. Let's call the measures total orders, effective orders, total amount, effective amount

effective order = total order - return orders
effective amount = total amount-returned amount (say)

the row will be populated with 4 values from status_cd i.e. a, b, c, d

for all a's i want the row to display say, apple, for all b's, i want th row to display say ball, for all c's it should be cats and for all d's it should be dogs

i'm dropping a query calculation into the row, then inn the expression i'm typing:

if ([model.dev.status_cd] = 'a') then ('apple')
else if ([model.dev.status_cd] = 'b') then ('ball')

and so on

keeps giving me a parsing error
i tried having a else(0) as the ending, tried putting else (if([model.dev.status_cd] = 'b') then ('ball')) and tried other combinations

even tried putting the actual status_cd query item into the row, and double clicking and putting the expression there, but to no avail.

the ghost of freakin parsing error keeps haunting me.

anyone spot anything

then i've to figure out how to break the row such that after a and b, there is  summary row, then another summary row after c and d, and then a grand total summary row


any hints anybody? ???
Title: Re: OK, I give up. What am I doing wrong?Crosstab
Post by: MDXpressor on 06 Apr 2007 09:11:14 PM
not that if...then...else won't work, but it is certainly harder to keep clean than a case statement:

Case
when ([model.dev.product_cd] = 'a') then ('apples')
when ([model.dev.product_cd] = 'b') then ('ball')
when ([model.dev.product_cd] = 'c') then ('cats')
when ([model.dev.product_cd] = 'd') then ('dogs')
else 'Error!'
end

Having said that is [model.dev.product_cd] supposed to be the model path?  If so, should it not have square brackets to close each level?  i.e. [model].[dev].[product_cd]