10 Kasım 2017 Cuma

AX 2012 - SSRS Report Carry Forward and Page Total

Unfortunately adding carry forward and page total are a bit tricky with SSRS. I used up a blog (Peter ?) for carry forward and another blog (Annette Theißen) for page total.

Name our field for use total AccountingCurrencyAmountDebit.

Add a textbox for carry forward named dCarry, and value like below:


or if we will group by account num:


Carry previous named dCarryH and like this:

=RunningValue(Fields!AccountingCurrencyAmountDebit.Value,SUM,"AccountNum_0") - Fields!AccountingCurrencyAmountDebit.Value

Put visibility to no both of them. Even they hidden values will be calculated.

For show carry previous value add a textbox to Page Header,  it's value should be like:


Visibility have to bound a condition (So there won't be carry previous at first page):


Add a textbox for carry forward to Page Footer and put value like this:


It's Visibility bound a condition too (So there won't be carry forward at last page):


We need two data methods and some variables for page total. For add Data method do right mouse - Add Data Method at treeview's Data Methods node under Designs. Than double click for open code window. Our code is red lined:

using System;
using System.Collections.Generic;
using System.Security.Permissions;
using System.Data;
using Microsoft.Dynamics.Framework.Reports;
using Microsoft.Dynamics.AX.Application.Reports;
public partial class LedgerTransListAccount
    static double previousTotal;
    static double pageTotal;
    static bool NewAccount;
    static string lastAccount;

 [DataMethod(), PermissionSet(SecurityAction.Assert, Name = "FullTrust")]
    public static double PageTotal(double total)
         if (NewAccount)
            return total;
            return total - previousTotal;

    [DataMethod(), PermissionSet(SecurityAction.Assert, Name = "FullTrust")]
    public static double PreviousTotal(double total)
        previousTotal = total;

        return 0;
[DataMethod(), PermissionSet(SecurityAction.Assert, Name = "FullTrust")]
    public static string Reset()
        NewAccount = false;
        lastAccount = "";

        return "";

    [DataMethod(), PermissionSet(SecurityAction.Assert, Name = "FullTrust")]
    public static string FollowAccount(string account)
        if (lastAccount != "" && lastAccount != account)
            NewAccount = true;
        lastAccount = account;

        return account;


Add a textbox at Page Footer:


Unfortunately if we make visible false it doesn't calculate. Instead of make visible false we make display format as number and change Show zero as combobox value with blank (Not Nonel!!).

Add a textbox for page total:


If you ask why we used an extra textbox; unfortunately SSRS doesn't accept two field for expression parameters.

Add a textbox to page header:


Hiç yorum yok:

Yorum Gönder