Cognos Report Studio - Grouping/Filtering

Question

Truth be told I'm not entirely sure what it is I'm trying to do here, well, that is I know what I want to achieve, but not how to go about it... so here's hoping you can help point me in the right direction!

I need to create a Crosstab report which has customers down the side, dates for columns, sales for figures. Simple enough.

Where it gets tricky is that they then want another row beneath the customers which singles out two customers, and their sales for one particular product.

They then want another row which will remove that figure from the total of the overall sales total for the first section (see example image).

I'm not really sure where to even start with this. I think I may need to use a query union, but every time I start I get kinda stuck... help!

Answer 1

That's not a crosstab. The summary calculations don't work. It's probably going to end up being three crosstabs formatted to look like a single crosstab. The first crosstab is everything down to the Total line. The last two lines are each crosstabs. Set the Size & Overflow, Padding, Margin, and other formatting properties so everything is layed out, bolded, and shaded the way you want.

Answer 2

You can create this as a crosstab If you are okay with setting each customer as a fact You can add them to a crosstab in any order you want

Then you can create a new data item and use a conditional statement that sets the metric to be the specific customers the consumer wants

For example,

IF([Customer] IN(?PrmCust) Then([Sales])Else(0)

For the revised total, unlock the report and replace them with layout calculations that take the total and reduce it by the amount of the selected customers