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