How to create a Payables Historical Aged Trial Balance Summary SSRS report

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 
5. Report Builder will launch. Click the Pie Icon in the left hand corner and click Save As
6. Rename the Report to Historical Aged Trial Balance-Summary and click Save
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.
   

   

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:
11. To do so, click on the cell until the box is highlighted in grey and hit your delete button on your keyboard
    

    

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.
14. Where the report displays the title add “-Summary” to the report
    

    

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

    

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

    

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

    

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.
    

    

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.
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.