SQL Server Reporting Services is an amazing reporting tool. However, I recently noticed that most of the reports print in detail.  Users want the same functionality from SSRS that is available to them in Dynamics GP, such as Detail and Summary versions of reports.  In this step-by-step guide, I will walk you through creating a Summary version of the Payables Historical Aged Trial Balance.

  1. Go to your SQL Server Reporting Services reports page
  2. Click on the Company Folder
  3. Then Click on the Purchasing Folder
  4. Click on the Historical Aged Trial Balance and select Edit in Report Historical Aged Trial Balance and select Edit in Report Builder
  5. Report Builder will launch. Click the Pie Icon in the left hand corner and click Save AsReport Builder will launch in order to save it
  6. Rename the Report to Historical Aged Trial Balance-Summary and click SaveRename report to historical aged trial balance-summary
  7. Remove the Row Groupings for Tbl_Main_Details_Group, to do so: Right click on the Group and click Delete Group. Choose Delete group and related rows and columns.

    Remove the Row Groupings for Tbl_Main_Details_GroupTbl_Main_Details_Group

  8. Click Ok
  9. Remove the Row Groupings for Grp_Control_Number in the same fashion as above
  10. Remove the unwanted column headings from the report such as the following:Remove the unwanted column headings
  11. To do so, click on the cell until the box is highlighted in grey and hit your delete button on your keyboard

    Remove vendor id and name

  12. Complete this for the remaining column headings
  13. Move the VENDORID ROW up slightly but do not move the fields into the above row. This undoes the grouping on the VendorID.Move the VENDORID ROW
  14. Where the report displays the title add “-Summary” to the report

    Export Historical Trial Balance

  15. Click Run on the Ribbon and run the report to review how it looks

    Run report to review how it works

  16. Enter information like you normally would and then click View Report

    16. Enter information like you normally would and view report

  17. The report will load and you can view how the information will be displayed to the users

    Load the report and view how the information will be displayed

  18. You may notice at the very end of the report viewer that the grand totals look scrunched together. You might need to move the columns by using the sliders at the top to adjust the fields.

    move the columns by using the sliders at the top to adjust the fields

  19. When finished with your report styling, save the report, and Exit Report Builder.
  20. Click back to the Company Reports folders and click Purchasing folder again to refresh your reports. You will now see Historical Aged Trial Balance-Summary available.go to Company Reports folders and click Purchasing folder again to refresh your reports
  21. Grant security access to the report as needed.

As you can see, after a few quick steps you now have a Summary version of the Historical Aged Trial balance ready for review.  You can utilize the same steps if you would like to create one for the Receivables Historical Aged Trial balance.  The fields and groupings will be different but the same concept applies.  If you need further assistance with this creation, you can reach out to us by filling the form below.