CODEWITHSUNDEEP
sqlsql-serverreporting-services
Note Ngernkuakul
Note Ngernkuakul

Reputation: 25

SQL server report builder 3.0 dynamic grouping and sum

So I'm trying to make a report and I want to group my row using certain condition. This is what I have

    Code    July,1    July,2  SC   BF
    20       5          2      1    6
    21       4          1      4    1
    31       4          7      9    2
    35       5          1      6    0

What I want to do is sum for the twenties, thirties and etc. like

    Code    July,1    July,2  SC   BF
    20       5          2      1    6
    21       4          1      4    1
    2*       9          3      5    7
    31       4          7      9    2
    35       5          1      6    0
    3*       9          8      15   2

Or possibly

    Code    July,1    July,2  SC   BF
    20       5          2      1    6
    21       4          1      4    1
    2*                         5    7
    31       4          7      9    2
    35       5          1      6    0
    3*                         15   2

Thank you in advance

Upvotes: 0

Views: 78

Answers (3)

Kashif Qureshi
Kashif Qureshi

Reputation: 1490

In ssrs report You can add a calculated field in your dataset with field name as Group and expression as =Floor(Fields!code.Value/10) and then in tablix do your sum bassed on that grouping. Adding Expression

Creating Tablix

Results

Upvotes: 1

Ashu
Ashu

Reputation: 482

select Code    July,1    July,2  SC   BF from tablename
union 



select DISTINCT Code/10 ,  (select SUM (July,1) over (Code/10) from tablename where code=t.code)July,1   ,    (select SUM (July,2) over (Code/10) from tablename where code=t.code) July,2 ,  (select SUM (SC) over (Code/10) from tablename where code=t.code) SC,  (select SUM (BF) over (Code/10) from tablename where code=t.code) BF from tablename t

Upvotes: 0

Jesse
Jesse

Reputation: 873

One way to handle this is in your SQL setup a column you can group by. In this case if Code is an integer, you can divide by 10 and it will group all the twenties with 2 and thirties with 3:

create table #test
(
    intValue int,
    sumValue int
)

insert into #test
select 20, 9
union all select 21, 10
union all select 30, 1
union all select 31, 2

select * , round(intValue/10,0) as GroupByThis
from #test

Upvotes: 0

Related Questions