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

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

Up vote 1 down vote favorite share g+ share fb share tw.

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! Crystal-reports ssrs-2008 ssrs-reports ssrs-grouping link|improve this question edited Nov 19 '10 at 9:49 asked Nov 19 '10 at 9:24William Choi429112 92% accept rate.

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...

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 Choi Nov 30 '10 at 5:28 Not a pretty hack, but it gets the job done. – Nat Dec 1 '10 at 1:04.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.