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

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

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

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)
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)
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)
=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:
No comments:
Post a Comment