Filter rows displayed in a crosstab & don't display null/other rows

[cognoskat]

I have a report requirement that I know should be easy but I'm stumped... I'll try to "draw" the output first. It's a crosstab report.

                                Q1 2007
                                Units | Revenue
Brand 1                       100 | $100
Brand 2                       200 | $200
Total for All Brands        500 | $500

So basically what I am trying to do is select two brands to show units sold and revenue for, out of hundreds of brands that we carry. Then, in the last row of crosstab report, I need to show total revenue for all brands, not just the two.

When I wrote this report I was able to show revenue for the two brands and successfully show units and revenue, but unfortunately there is a row added that is the revenue for all brands except the two that I've filtered in my data item for the brands. I need to NOT display that one row that contains that extra information. That's where I'm stumped.

My data item for "Brands" is:
case when([Database Layer].[DimProducts].[Brand] = 'Brand 1') then 'Brand 1'
when ([Database Layer].[DimProducts].[Brand] = 'Brand 2') then 'Brand 2'
else NULL
end

My data item for "Units" (and it is similar for "Revenue") is:
case when([Database Layer].[DimProducts].[Brand] = 'Brand 1') then ([Database Layer].[FactOrders].[OrderItemQuantity])
when ([Database Layer].[DimProducts].[Brand] = 'Brand 2') then ([Database Layer].[FactOrders].[OrderItemQuantity])
else NULL
end

I tried conditional formatting to match on a condition=NULL and set the box type to "none", but this didn't make a difference in my report.

[almeids]

I don't have a solution for you but would advise that you avoid using null values in your logic, in general Cognos products do not play well with nulls.  Try using some static text instead in your data item calculations and conditional formatting test.
I'm not familiar with the Report Studio architecture in C8 but in Reportnet you would also be able to keep your subtotal out with a filter at the query level rather than suppressing the output with conditional formatting, perhaps a similar approach would work at the equivalent "level" in C8?

[Suraj]

Put a filter on the query level instead of creating data items...
[Database Layer].[DimProducts].[Brand] in ('Brand1', 'Brand2')
it'll display only those two brands in report.
For total, just total from the menu by clicking on the brands.
You'll see totals if the query is not filtered and when you try to add a row manually for total.