SSRS RDLC and Custom Code

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
      InvoiceNumberGlobal =””
 End If
 
  If InvoiceNumberGlobal <> InvoiceNumber Then
      InvAmt = InvAmt + Total
      InvoiceNumberGlobal = InvoiceNumber
  End If
 
  Return Total
End Function
 

Public Shared Function ReturnInvTot() AS Decimal
  Return InvAmt
End Function
 

Called the function SumInvTot from detail as

=Code.SumInvTot(Fields!Invoice.Value,Fields!GroupName.Value,Fields!InvoiceAmount.Value)

and ReturnInvTot from group footer as

=Code.ReturnInvTot()
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

=SUM(ReportItems!textbox14.Value )

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

http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/0c24d678-5808-4c7a-93f3-8b80ac8a5511

About these ads

About Suresh Kumar D

Hardcore SQL Developer and aspiring Database Architect.
This entry was posted in SSRS. Bookmark the permalink.

One Response to SSRS RDLC and Custom Code

  1. Pingback: 2010 in review | Kirukkal a.k.a Scribbling

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s