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.
And I encourage You to vote for the existing one:
or
More information about MS Connect site available in previous blog post.
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),="Microsoft.Dynamics.Framework.Reports.BuiltInMethods.ToDisplayStringAmount(
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),="Microsoft.Dynamics.Framework.Reports.BuiltInMethods.ToDisplayStringAmount(
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)), ="Microsoft.Dynamics.Framework.Reports.BuiltInMethods.ToDisplayStringAmount(
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:
Disclaimer:
This programming example is for illustration purposes only. Microsoft disclaims all warranties and conditions with regard to use of the programming example for other purposes.
Microsoft shall not, at any time, be liable for any special, direct, indirect or consequential damages, whether in an action of contract, negligence or other action arising out of or in connection with the use or performance of the programming example. Nothing herein should be construed as constituting any kind of warranty.