Add carry forward sum to OpenSalesOrders SSRS report

A common requirement is to add a running totals sum and carry foward which displays the actual sum on the next page. Following the tutorials like this by André it’s easy to implement also for Dynamics AX.

  1. Start Visual Studio as Admin and open CustSalesOpenOrders_NA report from the application explorer
  2. Open the report design
  3. Add 2 new columns on the right side and set Hidden property to TRUE
  4. Open the property dialog for the first new column and change the name to RunningTotal
  5. Set the following expression as value
    =RunningValue(Fields!amountRemainingMST.Value,SUM,"CustSalesOpenOrders_NADS")
  6. Open the property dialog for the second new column and change the name to RunningTotalPrev
  7. Set the following expression as value
    =RunningValue(Fields!amountRemainingMST.Value,SUM,"CustSalesOpenOrders_NADS") – Fields!amountRemainingMST.Value
  8. In the report design create a new text field in the report header above the amountRemainingMST
  9. Set the following expression as value
    ="Running Total: " +cstr(First(ReportItems!RunningTotalPrev.Value))
  10. In the report design add a new footer and add a text field in the footer under the amountRemainingMST
  11. Set the following expression as value
    ="Running Total: " +cstr(Last(ReportItems!RunningTotal.Value))
  12. Save and deploy the report
  13. Open Dynamics AX go to Sales and Marketing > Reports > Sales Orders and start the Open Sales Orders report

Your report should look like this, including a page sum and a carry forward on the next page

image

About erpcoder
Azure Cloud Architect and Dynamics 365 enthusiast working in Research & Development for InsideAx

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: