Change expression based on column name

[cognos05]

Hi,

I have a product named 'ToolKit' which is ideally 1 single item qty  but assumed to be four pieces.
I have two measures qty and full revenue

cross tab looks like
               Qty             Full revenue
Toolkit       1                  450

I wanted all the qty for toolkit to be multiplied by 4 and the revenue still be the same.

So my toolkit will have an expression like

If measure is FullRevenue  then [ToolKit]
Else ([ToolKit]*4)

How to check if the measure is Qty or fullrevenue in the data item expression of the toolkit.

Thanks,
Nithya

[Francis aka khayman]

shouldn't it be:

if (Product = 'Toolkit') then (qty * 4) else (qty)

?

[cognos05]

So you mean the expression should be for the measure column.

How will you compare the product data item , can you directly compare with toolkit..toolkit is a member in product hierarchy.

Thanks,
Nithya

[Francis aka khayman]

honestly i can't understand :)

please check the sticky thread, how you can present your issue properly.

[Robl]

You said
"toolkit is a member in product hierarchy."
Does that mean this is a cube based report?

[cognos05]

Yes, its a cube based report.

[cognos05]

I came up with this expression but it works for few columns and not for other columns

if (currentMember([Sales].[Measures])=[Full Revenue])
Then
([212 - SPEEDBRIDGE - Implants - Shoulder])
Else
([212 - SPEEDBRIDGE - Implants - Shoulder]*4).

DO you guys think of any other approach.

Thanks,
Nithya

[Robl]

I'd try and fix it back in the cube.
Perhaps add a measure for 'Pack Size' which would default to 1.
Then your calculation would be Qty * Pack Size

[cognos05]

Hi,

I have a product hierarchy , in which toolkit is an item at level 3.


I drag the toolkit element to the rows and in columns I have two measures.
                 
                                     Qty     Sales
Toolkit                    6        150

Other Products        12        1450

So for all other products the qty should be the same as in cube value but for toolkit I need a calculated qty to be shown.

                                      Qty     Sales
Toolkit                     24        150

Other Products             12        1450
 

So the toolkit element should be a calculated data item like,

if(currentmember(Measures)=Qty)
then
(4 * [toolkit])
else if(currentmember(Measure)=Sales)
then
([toolkit])

so is the expression currentmember(Measure)=Sales for comparing the measure and assigning calculations correct.

Thanks,
Nithya

[Francis aka khayman]

if (currentMember([Product]='Toolkit')) then ([Qty] * 4) else ([Qty])

[cognos05]

I have used if caption (dataitem) = product name
Then
Qty *4
Else
Qty

It works fine.

thanks...