A client of ours recently submitted a support request looking for help in producing their monthly customer statements. The issue was with an error message which would pop up during the processing: “Rendering output for report failed and the following error occurred: An error has occurred during report processing.” Hm. Not too revealing, is it?
Looking at the issue, we discovered that this report was always sluggish. It would typically take more than 5 minutes to run. With the number of customers and transactions growing with time, the report would take longer and longer to render – until one day it just refused to print altogether.
When we tried the same report on a smaller subset of records, it worked fine (albeit slow) – which led us to thinking that there was an issue with the volume of data that NAV tries to send to SSRS while processing the report.
As some of you might know, Dynamics NAV transmits its data to reports in one big rectangular recordset, with no structure in it. This can be confirmed by opening SSRS layout for any of NAV reports with hierarchical data items and checking its Report Data. Notice that the Data Source for the report contains a single Data Set called “DataSet_Result”, where all the report fields are lumped together:
This is achieved by either joining or uniting all the report data items, whichever is appropriate in each case.
As a result of joining everything to everything else, the graphics used to print logos and other pictorials are attached to every single record sent to the report. For instance, check the “About This Report” window of a preview of standard NAV Sales Invoice report (ID 206). You will notice asterisks on every line in one of the three fields: CompanyInfo1Picture, CompanyInfo2Picture or CompanyInfo3Pircture. (The three fields are just a technicality – they are there to control the position of the company logo on the page).
An asterisk in there tells us that company logo is sent over to SSRS as many times as there are lines in the dataset. And graphics are by far the largest objects taking part in this data exchange. Should you print many documents with many lines, this might result in transmitting hundreds or thousands of copies of the same company logo – only to print the same image on every page of the report! Let us guesstimate: we need to print 1024 invoices with 10 lines on each. Our logo is 102.4Kb in size. This works out as 1024 x 10 x 102.4Kb = 1048576Kb = 1Gb. Hefty!
There must be a better solution than that, and there is – provided the graphics objects you need to show on the report are not changing from page to page. Since this was the case for our customer in question, we came up with an idea of adding a dedicated “graphics only” data item at the top of the report. The trick there is to make sure it sits at the same indentation level as the top-most report data item. In this way, the new data item will be united with the rest of the report (not joined!) and thus sent to the report engine only once!
Here are more detailed steps for the solution:
o Add a new single iteration data item at the top of the report; you could just iterate over Company, which is always a single record.
o Group all of the graphics fields under the new data item.
o Issue CALCFIELDS() command where required.
o Inside the report layout, point the Picture controls to new data items. In report header/footer, use the FIRST() command to access the data.
o In the top-level tablix control, specify a filter to only show records different to the new data item (can just send a word “graphics” in a dedicated data field under the new data item. It is guaranteed to be empty for all the other data items).
The resulting dataset should look as follows:
And finally, here is how the newly structured data would be represented in the “About This Report” window:
Contact our Sales Team today to schedule a demonstration of Microsoft Dynamics NAV.