COGNOiSe.com - The IBM Cognos Community

Hi ,

I have a crosstab with set of products on rows and growth calulations on the columns .

Now I need to nest a filed next to rows to show the product team based on the product name .

so looks like
        Ytd pytd growth
prd1
prd2

Now I want something like

                               Ytd Pytd Growth
prd1 shoulder
prd2 knee



so what i am doing is something like this on the data item ad nesting in rows after the product set


if(caption(currentmember([ReportingOnlyAgencySales].[Items].[Items]))='prd1')
Then('Shoulder')
Else
('Knee')


But I am not getting result for my measures as well as values on the dataitem .

Any help on how this can be achieved is appreciated.

Thanks,

I also tried this with a list and no luck .

I have a column which is a set of products and
say product1
prod2 - Aggregate(currentmeasure within set set(prd1,prd2))

now the set has (product1 and prod2)

Now this set is placed in list 1 column and in second column i wanted to have something like
if(caption(setname) = 'prod2')
Then ('Knee')
Else if(caption(setname)='product1')
Then
('Shoulder')
etc...

but the caption is not working for the prod2 member where we aggregated 2 measures .

How to make this working .

Because you're just trying to change the label that appears in the output, I would recommend using a layout expression.

Drag a crosstab space (with fact cells) to the right of the Product node. Set the source value of the text item to report expression. Then you can use something like the following
case [Query1].[Product line]
when 'Camping Equipment' then 'Team 1'
when 'Golf Equipment' then 'Team 2'
when 'Mountaineering Equipment' then 'Team 3'
when 'Outdoor Protection' then 'Team 4'
when 'Personal Accessories' then 'Team 5'
else 'Unknown Team'
end

Attached is an example report.

Hi Paul ,

I am getting an error on adding the report expression with case when statement

case [T24 Query].[Items]
when 'abc' then 'abc'
when 'bc' then 'bc'
Else 'Unknown Team'
End

Error is CRX-UDA-2001 Internal Error .

Can we use case when with dimensional data.

Guys,

Any help on this . Not sure what I am doing wrong.

Does my example report work for you?

Make sure you're putting the report expression on the page level and not the query. A case statement shouldn't be used on dimensional data, but that is limited to query level expressions. A report expression is processed as the output is being drawn onto the page.

How do i put the report expression at page level.

I have my report with 5 cross tabs and each cross tab has seperate queries,

Now I wanted to add this caption check only to 3 rd crosstab which has a query linked to name T24Query.

In your case there was only 1 query. Putting report expression at page level means, Is it setting that query to Page query .

Right now page query is empty in your report and also in my report.

The calculation should not be done in the query at all. That was what I meant when I said page level. Report expressions are processed as the query output is drawn onto the page.

In my crosstab, click on the cell with the calculation and look at the "Source Type". Notice that it is set to "Report expression". When you double click on the row below the report expression editor will open.

In your report, you need to do the same thing. Change the source type to report expression, and add the case statement there. 

QuoteI have a column which is a set of products and
say product1
prod2 - Aggregate(currentmeasure within set set(prd1,prd2))

now the set has (product1 and prod2)

Now this set is placed in list 1 column and in second column i wanted to have something like
if(caption(setname) = 'prod2')
Then ('Knee')
Else if(caption(setname)='product1')
Then
('Shoulder')
etc...

In Crosstab you could bring two Dataitems:  prod2 and product1 into rows.
Then you can just add a Layout Calculation with relevant static text.
And you would have something like:

                               Ytd Pytd Growth
prd1 shoulder
prd2 knee

Attached a report example, PP cube and screenshot

Hi Paul ,

Your example works for me as well ,but it works only when we have a set with members directly from the dimension or a level .

In my case I have something like set ( a,b,c)  and a has a calc like ( Total(currentMeasure within set set (1a,1b,1c) . etc..

I tried with a simple level and it worked but not with a set having a list of consolidated members .

Any suggestions is appreciated.

Andrei ,

In my example I am using a set which has a list of 10 to 12 memebers . If there is only one member it would be easy tp put a crosstab space and add a static text . I will sue that if adding case does not work.

Thanks,