Cognos 8 - reports studio - Display on GROUPED rows

[CrystalLion]

Can someone provide specific guidance on how to display only the Group Rows and Total Row

Report Column 1 is Grouped.
It is Variance indicator (Y or N) if Total Balance 1<>Total Balance 2.

Column 2 is Loan ID with Count of Loan ID grouped by Var
Column 3-5 are dollar amounts
I want to see the following

Var | Count| Total Balance 1 | Total Balance 2 | Variance
N | 10 | 10,000 | 10,000 | 0
Y | 5 | 1,000 | 500 | 500
Tot | 15 | 11,000 | 10,500 | 500

 

[blom0344]

I would try to solve this through a union based on 3 sets:

In SQL (as example):

select
'N' as Var,
count(distinct [someid],
sum(bal1),
sum(bal2),
sum(bal1)-sum(bal2)
from...........  where bal1 <> bal2
union all
select
'Y' as Var,
count(distinct [someid],
sum(bal1),
sum(bal2),
sum(bal1)-sum(bal2)
from........... where bal1 = bal2
union all
select
'Tot' as Var,
count(distinct [someid],
sum(bal1),
sum(bal2),
sum(bal1)-sum(bal2)
from...........

and display the resultset in a crosstab



Ties Blom