Receivables SSRS HATB: How to Add Additional Aging Periods

Receivables SSRS HATB: How to Add Additional Aging Periods

This document explains how to add aging periods to the default Historical Aged Trial Balance SSRS Report in Report Builder. Many customers have more than the 4 default Aging Periods set up in Receivables Management and would like to view all of their Aging Period Buckets.

(Microsoft Dynamics GP>>Tools>>Setup>>Sales>>Receivables)

In my example below, I have 7 aging periods set up.

Please use the following instructions to add more than 4 aging periods to the Default Historical Aged Trial Balance Report. (Report Builder and SSRS 2016 are used in the example below)

1. Open the following Page from your Reporting Services Web Portal

   HOME>>DYNAMICSReports>>Company>>Sales

2. Click the ‘…’ ellipse button and choose Manage on the Historical Aged Trial Balance.

   

3. Click on the ‘Edit in Report Builder’ button.

   

Note: if you do not have report builder installed, you will be directed to the page to download it.

1. When the report opens in Report Builder, highlight the column for Aging Bucket 4.

2. Right click the selected columns, and choose ‘Insert Columns to the Right’

   

3. Right click to Copy and Paste each of the 6 fields that were previously highlighted from Aging Bucket 4 into your new column for Aging Bucket 5. Rename your Aging Bucket labels as well.

   

4. Right Click on the <<Expr>> field (labeled as 2 above) but in the Aging Bucket 5 Column and Choose ‘Expression’.

    

5. The current Expressions should look as follows when completed. If there is more information in the expression, please delete it all and paste only information below:

   The entire expressions should look as follows:

   =Sum(iif(Fields!AGNGBUKT.Value = 4, Fields!AGING_AMOUNT.Value, 0))

6. Change the value = 4 to be value = 5 so your new expression looks as follows:

   =Sum(iif(Fields!AGNGBUKT.Value = 5, Fields!AGING_AMOUNT.Value, 0)) 

7. Click OK, then repeat on the second <<Expr>> field (labeled as 3 above) which is located just below the one in step 5 so your second expression looks as follows when complete. If there is more information in the expression, please delete it all and paste only information below:

   

   The entire expression should only contain the following when complete:

   =iif(Fields!AGNGBUKT.Value = 5, Fields!APPLY_AMOUNT.Value, 0)

8. Now on the Third bolded <<Expr>> field (labeled as 4 above) we will change the Value from 4 to 5 again so that it looks as follows. If there is more information in the expression, please delete it all and paste only information below:

   

   The entire expression should only contain the following when complete:

   =sum(iif(Fields!AGNGBUKT.Value=5 and Fields!RMDTYPAL.Value<>2,cdec(Fields!CURTRXAM.Value),cdec(0)))

9. On the fourth bolded <<Expr>> field (labeled as 6 above) for the Grand Totals Aging Bucket 5 we will change the Value from 4 to 5 again so that it looks as follows. If there is more information in the expression, please delete it all and paste only information below:

    

   The entire expression should only contain the following when complete:

   =sum(iif(Fields!AGNGBUKT.Value=5 and Fields!RMDTYPAL.Value<>2,cdec(Fields!CURTRXAM.Value),cdec(0)))

10. To add more Aging Periods/Aging Buckets repeat steps 1-19. You can have up to 7 Aging buckets as pictured below.

11. Once Completed Go to File>>Save As in Report Builder and save the report under a new name. This way you will not overwrite the default GP report. When saving choose the same path as the original document and save it into the Sales Folder with a new file name

    ReportServer_GP2016>>DYNAMICSReports>>Company>>Sales

NOTE: If you receive errors when trying to run the report, please confirm that your expressions do not contain extra data, they should look identical to the expressions in in the steps and should not have any more or less information in them.