If.. then..else conditional expression on cube, in crosstab column

[Cognos_World]

Hi Gurus,

I was trying to get a conditional expression for the cube in Report Studio for a crosstab column. Is there any way to do it?

e.g. If members([Country]) = [USA] then [Sales Order $] else (null)
where [Country] is a member and [USA] is one of the children of that member. I can't use a filter as I need other countries also in my report.

Crosstab should looks something like this:

                   Sales Order $ for USA       
Dept 1          10000.00                       
Dept 2          12000.00                           
Dept 3          12000.00                           

and total(Sales Order $) is equal to say 100,000 based on that, the Sales Order % for USA will be

                   Sales Order $ for USA       Sales Order % of Total
Dept 1          10000.00                              10.0%
Dept 2          12000.00                              12.0%
Dept 3          12000.00                              12.0%

Thanks in advance.

[MFGF]

You are trying to use a relational reporting technique on a dimensional source, and probably finding things really difficult.

Instead of the if/then/else, simply drag Sales Order in as the default measure, drag the USA member in as a column in your crosstab, drag the Country member in to the right of it, then drag a Query Calculation to the right of Country with an expression which divides USA by Country. You can then format this as %

MF.

[Cognos_World]

Thanks.
I have multiple measures and I am using it in the columns.
Alternatively, I used nested columns

                            Sales Order                                                                          |           Booked Order
                      USA          |  Rest of the World | USA/(USA+Rest of the World)  |    Inbound          Final
Dept 1          10000.00   |            20000        |          50%                                |
Dept 2          12000.00   |            23000        |          52%                                |
Dept 3          12000.00   |            23000        |          52%                                |

Now my problem is how do I hide the Rest of the World column?

The render variable option is not available for the crosstab column and making box type none is messing up the format at the Sales Order column level, the nested columns from the Booked Order (which is at the Sales Order level) is seen in the Sales Order nesting...

Hope I am able to explain the scenario.

Thanks,

[Cognos_World]

Also, for calculating % total, I created separate data item, unlocked the total line, made the define contents = Yes and inserted the calculated data item with a higher solve order. This gives the correct % total. However, this calculated item has to be on the report layout as a column. If I add to the layout and cut it, the % total disappears. This is definitely an extra column on the report and is there any way to get rid of this column and  the column in which it is nested getting size-wise adjusted accordingly. I tried setting the column width for each column, that didn't helped.

Thank you for your time.

[MFGF]

You are still thinking in terms of relational reporting by the looks of things. If you have multiple measures, then you could code your USA column as a query calculation using a tuple() function for the USA member and the desired measure, and use the same approach for Rest of the World. Does this help?

MF.

Sent from my iPad using Tapatalk HD

[Cognos_World]

To be more precise I have 2 nested dimensions in rows. 2 dimensions as columns and many measures as nested columns.

Example:
                             Dim3              |      Dim4
                     M1    M2   M%        |  M1   M2  M%
Dim1   Dim2
           Dim2

I have a single member in Dim1 and many members in Dim2.

I tried tuple, but, because I have Dim2 as children(D2), the tuple didn't worked and gave error.

Also, using memberset in Dim2 and using it in the tuple gives error.


Thanks,