Wednesday, June 23, 2010

Disabling Drill Through on Totals in Crosstab

If you have Crosstab Reports with drill through on the measure and you also have Totals. You would notice that the drill through is applied on the Totals column as well. If you try removing the drill through on the Totals the drill through on the measures also gets removed.


Step 1: In the Total Column, set the Define Contents property to Yes. The measure value disappears in this column.

Step 2: Now re-drag the total measure into the column from Insertable Objects after unlocking the column.

Step 3: Navigate to Crosstab intersection for the column and set the Source Type to Cell Value.

The Drill Through Link is no longer applied on the Total Column.



Anonymous said...

What if you have more than one measure (group) on the left and then sum each measure? Basically, the totals will be at bottom and not off to the right.

Zephyr said...

With more number of measures too you could still display totals to the right.

Say for example you wish to display Products across Rows and Years across Columns and include 2 measures - Sales , Revenue. When you display Totals to the bottom you would be displaying totals for a year across products and when you display totals to the right you would be displaying Totals for a product across years.

Scott said...

This helped me a lot. I was trying to figure out how to do it and this was exactly the answer. I do need to know, however, to do something else. If I have two columns of measures - the first is the number of participants and the second is the percent of participants of the total of participants. At the bottom, I don't mind totaling the participants column, but the second column needs to be the percent of that total of the overall total.

Zephyr said...

Hi Scott, I have published a post based on your requirement "Creating Overall Percentage Summary Values in Reports". Let me know if this is your requirement.

Scott said...

Thanks for the quick reply, Zephyr. However, the issue I have is that I need to display the percentages at the bottom. Column 1 is the number of Pregnant Women. Column 2 needs to show the percentage of the total participation that are Pregnant Women. Column 3 is Partially Breastfeeding Women and Column 4 needs to show the percentage of the total participation that are Partially Breastfeeding Women.

The old mainframe report that this report is replacing has the percentages in the row beneath the numbers, but I cannot figure out how to do that, so I have to put the percentages in the columns beside the numbers. Perhaps you have an idea?

Wasim said...

hi Zephyr

I'm trying to create a drill through on a crosstab, but the crosstab intersection does not show any options for a drill through definition...
Can you tell me how you managed to create a drill through on the crosstab "measures".


Zephyr said...

Hi Wasim,

You need to unlock your crosstab and set the drill through on the text items in your crosstab.

Z said...

Hi Zephyr,

We are in the process of testing our new upgrade from 8.3 to 8.4. The new features in Query Studio, Filter and Sort are missing from the context menu. As IBM suggested,we set the datasource interface property and published the package, but it is still missing. Any ideas?Thoughts?

Zephyr said...

Hi Z, If features are missing from Query Studio, I think its an environment set-up issue rather than a package issue. Did you find anything on Cognos Support site regarding this issue?

Z said...

Yes I did and tried what the support site said and it still didn't work!

There is a new functionality in Query Studio 8.4 to filter or sort for the report selecting the option from the context menu of query item in the left hand side metadata tree. When working with relational package context menu (right-click menu) should contain options 'Filter for report' and 'Sort for report'. These menu options does not appear and are missing completely.

Menu options missing from context menu

Data source Interface property in Framework Manager is not set (=blank)

Resolving the problem
Set the Data source interface property to correct data source type and re-publish the package. (Some most common interface types are OR for Oracle and OL for MS SQL.)

Zephyr said...

Oh, you mean the "Filter for Report" and "Sort for Report" options available when you right-click on a data item in your package on the left hand menu. I am sorry, I have no idea on why that isn't appearing up. Let me know what you hear from IBM.

Amphora said...


Are you aware of the fact that this function only applies to relational packages. Not to dimensional or hybride packages.

Zephyr said...

Hmmm...Works for me against a cube based report..

Anonymous said...

Hi Zephyr,

I have an issue with Drill through defined in Crosstab report.

For selected date prompt, I need to calculate 'previous month end date' and pass this as drill through to detail report. So, this calculated data item is defined in the drill through definition but the detail report is not picking up this value and popping the prompt page.

I tried adding the data item 'previous month end date' to the layout and to the properties too and still the detail report is not picking up this data item value. I tried displaying the parameter in the detail report and it is just blank.

Any help will be highly appreciated.

Zephyr said...

I am assuming that the data item is included as a column in your cross tab report.

Properties will not help unfortunately due to a defect/limitation with crosstabs.

What version of Cognos are you on?

Take a look at this and let me know if that helps.

Anonymous said...

Thanks broww, you are the man!
Saved my life!

José Cano said...

Hello there,

Thanks for posting this information. However, I have a different situation.

I have a crosstab with a hierarchy in the rows, each group has its own total (plus the Grand Total). I've defined a drill through in the fact cells, however, it is not working when the click is done in the subtotal/total cells. I mean, the child report is not receiving the correct parameters (for instance, the categories selected for the corresponding cell).

Is there anyway to pass the selected category to the child report? I tried with several variants of data items, but it is not working.

Thanks in advance for your help.

José Cano