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.