Hide Cross Tab

[jasonmacpei]

Hi,

I have a cross tab that shows the months across the top, and I'm looking to only show the last 4 months, while still showing the totals at the end.

How can I hide these previous months, without filtering them out (as I want the results for the totals).

Thank you in advance!

[Lynn]

Hi,

I have a cross tab that shows the months across the top, and I'm looking to only show the last 4 months, while still showing the totals at the end.

How can I hide these previous months, without filtering them out (as I want the results for the totals).

Thank you in advance!

Relational or dimensional?

[jasonmacpei]

Sorry Relational Data.

The months across the top is calculated field, where the underlying value is a date.  I use two calcs to get the month text, and "extract" for the month number, and a "case" (ie: monthNumber = 1 then 'January') to get the month text.

Thanks for your help

[BigChris]

I think you're going to need two fields, one for the 4 months across the top, and one for the 12 month total at the end of your report.

[jasonmacpei]

Hey Big Chris...thanks for your reply.  When you say two calcs, not sure how to do this in terms of a cross-tab.  I could set it up as a table, and develop some singleton calcs to do this, but will be a lot more work.  Maybe I'm just not sure how to do this?

I've attached a screen capture of the crosstab in development and the resulting table.  In the example, I want to just show the last 4 months, November, December, January, February.

[Lynn]

It would be a fairly simple task with a dimensional source. Sometimes you can use dimensional functions with a crosstab layout even against a relational source because Cognos spins up a little cube in the background to satisfy the layout.

Using a relational GO Sales source, I was able to use a "tail" function to get only the last four months of a year and then a separate "aggregate" function to show the total across all months, but I can't get the tail to work against a calculated month which is how you're creating the expressions for your column.

As an alternative, you might try creating a separate measure that only populates for records within the 4 month time frame you want and is zero for other months. Use this as the measure on your columns and suppress columns to only show the four months. Use the real measure as the last column to show your total.

I'm not sure what your logic is to determine the last four months, but the idea of the other measure is along the lines of this:

Code Select Expand


case
  when [Your Record Time Frame] = [Desired Time Frame]
    then [Your Measure]
  else 0
end