Recently I came across a request wherein the user wanted to build a RDLC which should show duplicate records in the detail but should not add them in the sum. The data looked like the below one
and the user wanted to show the data like
This looked simple except that the Group Total should not consider duplicate InvoiceAmount.
I started by adding a Group called “Grp” on “Group Name” with group header and footer. Then displayed the Invoice and the InvoiceAmount in detail. I used the SUM(Fields!InvoiceAmount, “Grp”) in group footer but that resulted in duplicate invoiceamount adding into the Total which I did not want to happen.
So I created two custom functions (in Report Menu –> Report Properties –> Code), one to sum the invoice amount excluding duplicates and another to return the summed value. The function looks like
Public InvoiceNumberGlobal As String
Public Shared InvAmt As Decimal = 0
Public GroupNameGlobal AS String
Public Function SumInvTot(InvoiceNumber As String, GroupName As String, Total As Decimal) AS Decimal
If GroupNameGlobal <> GroupName Then
InvAmt = 0
GroupNameGlobal = GroupName
If InvoiceNumberGlobal <> InvoiceNumber Then
InvAmt = InvAmt + Total
InvoiceNumberGlobal = InvoiceNumber
Public Shared Function ReturnInvTot() AS Decimal
Called the function SumInvTot from detail as
and ReturnInvTot from group footer as
I made these changes in RDL and it worked fine. So I added the same code in RDLC but I got weird results when I executed the report.
While the detail amount (invoice amount) displayed fine, the group total for the “Group1″ was returned as “0″ and for the Group2 it was “28410.34″ which was supposed to be Group1 total. The output from the RDLC was like this
The normal order of execution for the report is top to bottom i.e. Report Header –> Page Header –> Group Header –> Detail –> Group Footer –> Page Footer –> Report Footer.
But in this case it looked to me as if the Group Footer was getting executed first. I put the expression ReturnInvTot in page footer and I got “0″. The same code worked fine RDL.
Since custom code let me down, I tried to use the ReportItem built-in variable and sum up the values. I added an expression in the group footer like
which resulted in following errors
The Value expression for the textbox ” uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers.
The Value expression for the textbox ‘’ refers to the report item ‘’. Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope.
After few “binging”, I figured out that while we can use ReportItems in tables, we cannot use any aggregate functions on them. The only place where aggregates can be used on ReportItems is page header or footer. This is applicable to RDL also.
Finally I had no other choice but to revert back to SQL to pull me out of the mess. I introduced two more columns, GrPTotal and GrandTotal and updated them with proper values.
Though finally I could able to resolve the issue, till now I could not identify the reason for the weird behaviour of custom code in RDLC footer.
Have a look at the below thread if you would like