Text only | Text with Images

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Cognos 10 BI => Report Studio => Topic started by: cognos05 on 27 Jan 2015 02:51:19 PM

Title: Change expression based on column name
Post by: cognos05 on 27 Jan 2015 02:51:19 PM
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
Title: Re: Change expression based on column name
Post by: Francis aka khayman on 27 Jan 2015 08:29:43 PM
shouldn't it be:

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

?
Title: Re: Change expression based on column name
Post by: cognos05 on 27 Jan 2015 09:50:42 PM
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
Title: Re: Change expression based on column name
Post by: Francis aka khayman on 27 Jan 2015 10:03:24 PM
honestly i can't understand :)

please check the sticky thread, how you can present your issue properly.
Title: Re: Change expression based on column name
Post by: Robl on 28 Jan 2015 05:24:53 AM
You said
"toolkit is a member in product hierarchy."
Does that mean this is a cube based report?

Title: Re: Change expression based on column name
Post by: cognos05 on 28 Jan 2015 07:57:43 AM
Yes, its a cube based report.
Title: Re: Change expression based on column name
Post by: cognos05 on 28 Jan 2015 08:48:33 AM
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
Title: Re: Change expression based on column name
Post by: Robl on 28 Jan 2015 09:19:33 AM
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

Title: Re: Change expression based on column name
Post by: cognos05 on 28 Jan 2015 10:24:12 AM
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
Title: Re: Change expression based on column name
Post by: Francis aka khayman on 28 Jan 2015 08:18:06 PM
if (currentMember([Product]='Toolkit')) then ([Qty] * 4) else ([Qty])
Title: Re: Change expression based on column name
Post by: cognos05 on 28 Jan 2015 08:46:01 PM
I have used if caption (dataitem) = product name
Then
Qty *4
Else
Qty

It works fine.

thanks...
Text only | Text with Images