question

georgeg809C avatar image
georgeg809C asked

SSRS Grouping and Subtotals and Totals

ssrs.jpg

I have a table that is grouped by Invoice Number. each invoice shows the product and the components that makeup the product.

I also have invoice totals, and grand total of all invoices

I wan to add total of component 1 and total of component 2

Not sure how to do this. any suggestions would be appreciated, thank you.

George809C

ssrs
ssrs.jpg (21.9 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Brad_Smith avatar image
Brad_Smith answered

You can sum a field with an expression. This is where you add sum(compID) to a formula. The result depends how the data is grouped.

You can group (and sum) by rows and/or columns.

  1. Under View tick the grouping checkbox
  2. Add fields to group by row groups
  3. Add fields to group by column groups
  4. Sometimes additional fields are hidden under this arrow

The grouping properties may need to be reviewed to get the desired result (view > properties).

Grouping can get messy especially when reports contain multiple records + page breaks. A workaround is to sum within your select statement. Then you add this summed field without grouping (or less intensive group nesting). Try sum over partition by adding this line to your select statement:

SUM (COMPID) OVER (INVOICEID) AS INVOICESUM


capture.png





capture.png (43.2 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.