Reporting Services – Reports – Designer – Adding Group Summary

Background

Lately, I have been working on putting together a set of Utilization Reports.  Having not worked in Microsoft Reporting Services for a while, I am getting frustrated at my rust.  Things that would have been easy to do in years gone by are not nearly as intuitive and I am stumbling badly.

So for the sake of mine, let us go over how to add Group Summary Totals.

 

Report Designer

Original Detail Reports

Here is the Original Detail report:

Report-Detail-TransactionsForTheMonth

 

 

 

Detail Reports (with group summary)

Let us add Group summary based on Transaction Type:

  • Access Report Designer
    • Access the Detail Row
      • Right click on selected Detail Row
      • From drop-down, click on “Add Group”
      • In the Row-Group drop-down, select “Parent Group …”
      • In the Tablix group window
        • Indicate the “Group By” indicate the column we are grouping by; in our case “transactionType”
        • Place a check-mark besides the “Add Group footer”
    • Once the steps detailed above are completed, the “Group Footer” will have been added
    • Access the Group Footer band, specifically the amount cell
      • Right click on the cell
      • From the drop-down menu, select the “Expression” entry
      • In the expression enter “=Sum(Fields!amount.Value)
      • Click OK to exit the dialog
    • Clean-up
      • Let us remove extraneous columns
        • Let us remove the columns such as our group-by columns that are no longer valid in their original location
          • We can remove them or simply hide them; in our case will simply hide them
        • We will also columns that are not quite meaningful or relevant for our purpose
          • i.e. ID
    • Repeating Values
      • As we ended up with some repeating values, let us take care of some of those, as well
        • On each such entry in the detail row, access the text-box
        • Access the properties and select the HideDuplicates property
        • In the HideDuplicates value select the dataset; in our case Dataset1

 

 

 

Images

Adding Parent Group

ReportDesigner-AddGroup-Focused

 

Tablix Group:

ReportDesigner-TablixGroup-TransactionType

 

Tablix Group – Group Detail Added:

ReportDesigner-TablixGroup-GroupDetailAdded

 

Tablix Group – Group Detail – Enter Amount

ReportDesigner-TablixGroup-GroupDetailAdded-Amount

 

Expression – Sum Columns

Expression-Amount-Sum

 

 

Detail – Transaction Type – Sumary Amount Added

 

Report-Detail-TransactionsForTheMonth-GroupByTransactionType

 

Report Designer – HideDuplicates

Report-Detail-HideDuplicates

 

Transactions with Group Totals – Cleaned-up

Report-Detail-TransactionsForTheMonth-Cleanup

 

Dedicated

Dedicated to a young Tevin Campbell; as we all were young once and believed ….

 

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s