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:
=RunningValue(Fields!AccountingCurrencyAmountDebit.Value,SUM,"LedgerTransListAccountDS")
or if we will group by account num:
=RunningValue(Fields!AccountingCurrencyAmountDebit.Value,SUM,"AccountNum_0")
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:
=First(ReportItems!dCarryH.Value)
Visibility have to bound a condition (So there won't be carry previous at first page):
=Globals!PageNumber=1
Add a textbox for carry forward to
Page Footer and put
value like this:
=last(ReportItems!dCarry.Value)
It's
Visibility bound a condition too (So there won't be carry forward at last page):
=Globals!PageNumber=Globals!TotalPages
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;
else
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:
=PreviousTotal(First(ReportItems!dCarryH.Value))
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:
=PageTotal(last(ReportItems!dCarry.Value))
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:
=Reset()
