How to summarize values of all first rows of sub-groups in a group while using MS SSRS?

Question

In our DB, we have: (x means don't care)

GID   UID    COST
================================
A   1    100
A   1    x
A   2    200
A   2    x
B   3    330
B   3    x

And the customer report required to look like:

   UID    COST
================================
[Group - A]
   1    100
   1    
   2    200
   2    
   ---Subtotal: 300
[Group - B]
   3    330
   3    x
   ---Subtotal: 330
                        ======Total:    630

I've 2 groups in the SSRS report, one is group on GID, and one is group on UID, and I've tried many ways to summarize all the first COST of an UID in a group of GID. But no success.

If doing this in Crystal report, we can use "on group change formula" to achieve it. But in SSRS, I found no way to do it right.

Please kindly help!

@Mark, I'm new to Stackoverflow, indeed, i've tried to rate up the answer, but the system doesn't allow me to do so... > — William X, Nov 22 '10 at 03:35
@Mark, I've told the Stackoverflow.com about my "Mark / Vote" situation, and now I'm able to "accept the answer". Many thanks. — William X, Nov 22 '10 at 03:39

Nat · Accepted Answer · 2010-12-01T01:04:33.323

You maybe have to go back to the SQL and create the column you want to sum.

Using your example:

select 
    GID,
    UID,
    Cost,
    case when row_number()  over(partition by GID,UID ORDER BY GID,UID,Cost) = 1 then Cost else 0 end as firstCostGroup
from
(
    select 'a' as GID, 1 as UID, 100 as Cost
    union
    select 'a', 1, 101
    union
    select 'a', 2, 200
    union
    select 'a', 2, 201
    union
    select 'b', 3, 300
    union
    select 'b', 3, 301 
) as rawdata

The row_number function requires SQL 2005 or greater.

A workaround for SQL 2000 would be something like

drop table #RawData
go
drop table #RawDataFirstRows
GO
create table #RawData
(
id int identity(1,1),
GID varchar(10),
UID int,
Cost int
)

insert into #RawData
    select 'a' as GID, 1 as UID, 100 as Cost 
    union 
    select 'a', 1, 101 
    union 
    select 'a', 2, 200 
    union 
    select 'a', 2, 201 
    union 
    select 'b', 3, 300 
    union 
    select 'b', 3, 301  

create table #RawDataFirstRows
(
id int
)

insert into #RawDataFirstRows
select
rd.id
from #RawData rd
where
rd.id = (select top 1 id from #RawData rw where rd.uid = rw.uid and rd.gid = rw.gid order by rw.gid,rw.uid)


select
rd.GID, rd.UID, rd.Cost, case when rw.id is null then 0 else 1 end as firstCostGroup
from
#RawData rd
left join
#RawDataFirstRows rw on rd.id = rw.id

Note that the nested query in the where clause is incredibly inffecient as it has to call that query for each row in the #Rawdata table. Gets the job done, but at what cost?

If it does not cause performance issues at production levels of data, you may be okay.

Thanks, that's the way I found, but I can't apply this into company's environment since we're using SQL Server 2000. — William X, Nov 30 '10 at 05:28

How to summarize values of all first rows of sub-groups in a group while using MS SSRS?

1 Answers1