Grand total row on report in AX 2012

• 
  

Everyone who knows AX 2009 or previous versions, is aware of very useful feature – > Print options. It was available almost on every x++ report.

User was able to add totals/ subtotals and grand totals by simple “clicking”.

With SSRS Reporting Services this option is not available anymore and the only way to achieve the same results is possible by programming under Visual Studio.

This article will show you how to add grand total on the report. At first it looks difficult for people who do not have programming skills, but I’m a Functional Engineer as well and I could do it myself.

In addition, if you see that Subtotals or Grand total is not available on very important report, I recommend to log a Product Suggestion on MS Connect Site for each report. This will help to track all this requirements by Product team.

Example:

Ledger Transaction list report

1. Open Visual Studio > Application Explorer > SSRS Reports > Reports > LedgerTransListAccount > right click > edit

2. Add Parameter > Parameter > name: GrandTotal

3. Change properties as follow:
– DataType: boolean
– Name: GrandTotal
– Prompt String: @SYS179500 (Grand total)
 
Effect > Grand total parameter is available:

 
4. Back to Visual Studio > Expand the LedgerTransListAccount > Designs > report > right click > Edit Using Designer

5. The Design View will open > right click on the left side of window and insert row > outside group – below

6. On the added row (left side of window)  > right click and select the command > row visibility

7. Select > Show or hide based on expression and add expression as follow:
= iif(Parameters!GrandTotal.value = true, false, true)

Click Ok and close the row visibility form.
8. Add the Line label > right click on first column > expression

9. Add expression as follow:
=Labels!@SYS179500

10. Under the column Amount in accounting currency Debit -> right click and select Expression

11. Add expression:
=Microsoft.Dynamics.Framework.Reports.BuiltInMethods.ToDisplayStringAmount(
 Parameters!AX_RenderingCulture.Value,
 Sum(Fields!AccountingCurrencyAmountDebit.Value),
 True)

12. Repeat step 6 on the next field (Amount in accounting currency Credit) and add expression:
=Microsoft.Dynamics.Framework.Reports.BuiltInMethods.ToDisplayStringAmount(
 Parameters!AX_RenderingCulture.Value,
 Sum(Fields!AccountingCurrencyAmountCredit.Value),
 True)

13. On the Solution Explorer select ReportModel > right click > deploy

14. Close Visual Studio after deployment, restart AOS and check the results:
 

Additional notes, if user would like to see grand total for net difference – You have to follow the same steps, but the expression is different in point 11 and 12:
Design: new row added as in previous steps
 
Expression 11:
=Microsoft.Dynamics.Framework.Reports.BuiltInMethods.ToDisplayStringAmount(
 Parameters!AX_RenderingCulture.Value,
 Iif((Sum(Fields!AccountingCurrencyAmountDebit.Value)) – Abs(Sum(Fields!AccountingCurrencyAmountCredit.Value)) > 0,
  Sum(Fields!AccountingCurrencyAmountDebit.Value) – Sum(Fields!AccountingCurrencyAmountCredit.Value),
  Cdbl(0)),
 False)

Expression 12:
=Microsoft.Dynamics.Framework.Reports.BuiltInMethods.ToDisplayStringAmount(
 Parameters!AX_RenderingCulture.Value,
 Iif((Sum(Fields!AccountingCurrencyAmountDebit.Value) – Sum(Fields!AccountingCurrencyAmountCredit.Value)) <= 0,
  Abs(Sum(Fields!AccountingCurrencyAmountDebit.Value) – Sum(Fields!AccountingCurrencyAmountCredit.Value)),
  Cdbl(0)),
 True)

Results: