My report gets data from SQL based on a date range, uses custom code to calculate totals by Employee and CategoryType and stores the values in a dictionary. To get the grand total by Employee, in a matrix cell, I call a function and pass it the EmployeeID which returns the accumulated total. A chart is populated with data the exact same way.
Everything works as it should when running the report directly in Report Builder 3.0. However, problems arise when exporting the report to Excel. The first time it's exported, the totals are doubled. Each consecutive time, the multiplier is incremented by one. (1st - doubled; 2nd - tripled; 3rd - quadrupled, etc.). To work around this problem, I override the Init event and clear the dictionary variable (see below for code--notice I'm using the keyword Shared which I found out about on another posted question). Problem solved for Excel as now the dictionary is garbage collected and it will not recalculate from what already exists in memory.
But now a different problem exists. When executing the report via the Web, the matrix populates with data and the totals are correct although the chart only populates with data within a week date range from the current date. Very strange! If I increase the date range to longer than a week, I get a blank chart. The link above implies this may be due to the "report on demand" engine of SSRS but offers no solution to the problem in my mind. I need a solution that will work (showing correct totals and populating chart with data) when exporting the report to Excel and when simply viewing on the Web. I will post screenshots and more info if needed.
I've tried overriding the LoadComplete event (thinking this would be a great time to clear the dictionary as all other controls on page are loaded), but not sure this is possible in SSRS custom code and unsure of the proper syntax.
Is there a different render event I should be using instead to make this work? Anyone ever run across something similar and have a reasonable solution?
public Shared Dim dict As New System.Collections.Generic.Dictionary(Of String, Decimal)
'Override built-in OnInit function and clears dictionary; ensures correct totals when exporting to Excel
Protected Overrides Sub OnInit()
dict.Clear()
End Sub
