CODEWITHSUNDEEP
sql-serverreporting-services
skathan
skathan

Reputation: 689

Dynamic Columns in SSRS with Dynamic Row Summing?

So I've got a pretty simple view that looks like this:

Location    Month       DelRec      Warehouse_Number    Code    Value
Canada      November    Deliveries  Warehouse1          C       11041.2
Canada      November    Deliveries  Warehouse 2         C       0
Canada      November    Receipts    Warehouse 3         C       0
USA         November    Receipts    Warehouse_10        H       2543.2

Ultimately, what I would like to create is a matrix in SSRS that uses 'Code' as a dynamic column name and grouping by DelRec and Location (expanding the number of columns for each code available for that grouping). Easy enough, right? Looks something like this:

simple matrix

Now the next step I've got is to do some conditional summing on these values, which has got me stymied. In particular, for example, in the below data, for Canada, Deliveries need to be summed up together (Warehouse 1 and 2), and then the total of those two subtracted from Receipts (in this case, just Warehouse 3). Ostensibly, this would look something like this:

with_total

The problem is, I can't figure out how to do conditional summing (i.e. sum deliveries and subtract them from the sum of receipts), especially when dynamic columns are involved (because we don't know what warehouse numbers might come in future data).

In addition, I actually need a column that has the Total Deliveries and Total Receipts, which is causing me even further difficulty.

Anybody have any idea of how to do this? The result should look something like this:

enter image description here

I've created a a pastebin with some representative SQL data for insert if anyone would actually like to try this out.

Upvotes: 0

Views: 1463

Answers (1)

cabbagetreecustard
cabbagetreecustard

Reputation: 687

As I understand essentially "Receipts" are credits and can be treated as a positive amount and "Deliveries" are debits and can be treated as a negative amount. To get the correct total you could replace the Sum expression in the bottom right of the matrix with

=Sum(IIF(Fields!DelRec.Value="Receipts",Fields!Value.Value,-1 * Fields!Value.Value))

To get a total line for deliveries and receipts you need to either right click on Warehouse_Number in the matrix, then click on Add Total then click on After.

Add total to Warehouse Number

Alteratively you can right click on Warehouse_Number in the Row Groups and add total from there.

The matrix should now look like this

enter image description here

The report should now look like this

Report with both totals

Right clicking on Code in the matrix then "Add Total" -> "After" will add Row Totals as a new column. Note that this will only total "Receipt" or "Deliveries" by Location as the report is grouped Location then DelRec then Warehouse. You could achieve a Receipt Grand Total through either adding extra rows at the bottom of the matrix, or better in my opinion is another table only grouped by DelRec to calculate the DelRec Grand Totals.

Upvotes: 2

Related Questions