How to use Define contents in crosstab element in Cognos 10 and render it

There is a useful feature in Cognos Report Studio that allows overriding the contents of cells in a crosstab. This is specially useful when the automatic calculation that Report Studio makes is incorrect or when you want to show different levels of information.

In order to do this, you can use the “Define Contents” property of a crosstab intersection.

 I’m going to use an example where we will want to show % of sales related to a higher grouping level.

In this example, we have sales by type of customer, category, store, city, region and country. We want to show the % of sales of each group item within the group and we want to show that % in 1 column like so:

As you can see, the columns “%Sales 2015”, and “%Sales 2016” show percentages related to different grouping levels in the same colum. This will not be automatically done by Report Studio and needs using the “Define Contents” property but it needs to be used in a specific way to have the results rendered.

We’ll assume that all dimensions, members, levels, measures, etc. are properly set up in our model and we have created our crosstab. The crosstab will look something like this:

For this example, I’ve created a set called “Cust. Type” that holds the 4 customer types, a set called “Categories” with all the product categories, a set called “MadridShops” with all of the shops in Madrid Region and a set called Madrid to group the Madrid figures.

As upi can see in the “% Sales 2015” column, there is nothing special in the column yet. If we were to run the report like this,  the values for that column for the summaries would make no sense, only the calues for the “Cust. Type” rows would make sense. So we need to override the crosstab intersections to show the values we want to see.

First, we click on the intersection “Summary” and “% Sales 2015” to replace the default value with the % of sales of each category as referred to the total sales for the shop. When we do that, we can see the “Define contents” property which default value is “no”

First we’ll set the value to “Yes”

Note that when we do that, the value of the crosstab intersection is empty

We will have previously created a data item with the calculation:

In this case, the calculation is the percentage of Net Sales within Categories. We’ll have to create a data item for the percentage of Net Sales within the Shops sets and within the Region Sets.

Now we have to replace the content of the crosstab intersection with the data item we want displayed. To do that, first we unlock the crosstab and then drag the data item into place

When unlocked, drag the item into place

And then lock again.

That should be enough, I think, but unfortunately it is not. At least in Cognos 10.2 the cell will render empty unless you add the data item as a column in the crosstab. Of course, that is not what we want to see, so we need to add the data item as a column and then hide it.

Column Added

Hide column header by setting property “Box Type” to “None”:

Select all rows in that column and hide by setting property “Box Type” to “None”:

Repeat the process for all of the summaries that you want to change:

Now when we run the report, we’ll get the desired output

Last, one caveat. I’ve done this is several reports and sometimes the Defined cell did not render despite having the data item columns show the right values. That was due to using a set (Month, country, product, etc.) instead of a measure in the “percent” function. For whatever reason Report Studio will allow you to use for example “percent ([Jan 2015] within set [Categories])” to calculate for example the % of Sales of a Category for the month of Jan 2015 if you put that data item under the measure lie this:

But then it will not render if you use the “Defined contents” property to display that value elsewhere in the crosstab.