RIanGillis February 2016

SumDistinct Within a Group in SSRS

I have an SSRS report displaying data on a per-day basis in a matrix. I am using the left side (up-to-down) to display the total of all entities grouped by day. I am using the top side (left-to-right) to display a break-down of the different types of entries that are summed up in that row.

eg: dataset:

day typ cnt amount  exp
Mon 1   3   001000  400
Tue 1   4   000200  400
Wed 1   0   000000  400
Thu 1   1   000020  400
Fri 1   5   002100  400
Mon 2   2   001000  200
Tue 2   0   000000  200
Wed 2   2   005000  200
Thu 2   0   000000  200
Fri 2   20  250000  200

Output:

||  day cnt amount  exp ||  typ cnt amount  typ cnt amount
||  Mon 5   002000  600 ||  1   3   001000  2   2   001000
up  Tue 4   000200  600 up  1   4   000200  2   0   000000
dwn Wed 2   005000  600 dwn 1   0   000000  2   2   005000
||  Thu 1   000020  600 ||  1   1   000020  2   0   000000
||  Fri 25  252100  600 ||  1   5   002100  2   20  250000

The caveat is that I want to essentially sum-by-distinct-type the exp column (expected amount).

Normally I would sum/ group everything in my query, but one requirement of this report is to display each individual entry on a Detail page (in addition to the output I described above) and the query is already prohibitively heavy.

Hopefully my formatted Output is not too hard to decipher; The left side (surrounded by ||||up dwn||||) is grouping on day, sum(cnt), sum(amount), sumDistinct(exp). And the right side is the "matrix", grouped on typ. The sumDistinct(exp) (DISTINCT by the typ column) is the part I am having trouble with.

Answers


Quinncy February 2016

Normally I would sum/ group everything in my query, but one requirement of this report is to display each individual entry on a Detail page (in addition to the output I described above) and the query is already prohibitively heavy.

If I have something like this, I will define something called a ""Subreport" at the of the first, that is separated by a page-break. Not sure what program you are using for building your reports, however it should allow you to create a report with all the details save it and add it to your main report as a sub-report.

Post Status

Asked in February 2016
Viewed 1,053 times
Voted 6
Answered 1 times

Search




Leave an answer