We have received several requests related to optimization tips for the Microsoft Dynamics NAV RDLC report experience. So in this blog post we provide you with a couple of tips for how you can refactor a report in order to increase its performance in terms of dataset reduction - the example is based on the standard report 4 “Detailed Trail Balance” from Microsoft Dynamics NAV 2015 Cumulative Update 4 German localized DVD version (DEU).
NOTE: This approach is valid if you are crystal clear on the intrinsic technical limitations when printing client-side as was widely discussed and dissected in this blog post from March 2014, and it is intended to provide a general guidance as-is in the form of a proof of concept.
TIP #1 : Create a header for constant and static values
This is an old trick (described also in books and other collateral since Microsoft Dynamics NAV 2009), and it consists of pushing static values such as e.g. COMPANYNAME or TABLECAPTION in a DataItem based on an “Integer” record (virtual table) that will be repeated only one time (MaxIteration DataItem property needs to be set to 1).
The “Integer” DataItem must be the very first DataItem in the report so that while refactoring the report in Visual Studio, developers could always refer to this by using the =First( statement in VB.NET.
The great advantage is to avoid costly redundancy for static values (e.g. COMPANYNAME) all along the dataset since only the first record in the column is populated. This will reduce the dataset dimension, in terms of data quality, and consequently obtain a faster load of the dataset client side and raising higher the out of memory exception bar (the application is able to process more rows).
In this example, standard Report 4 has been refactored and 8 columns has been moved as part of the Header “Integer” DataItem:
|GLAcc.TABLECAPTION + ': ' + GLFilter||GLAccTableCaption|
TIP #2 : Use labels
Multianguage (ML) labels are pushed to Report Viewer at runtime outside the dataset. This happens when the report is run, such as when there is code such as this:
CurrReport.LANGUAGE := Language.GetLanguageID("Language Code");
inside the DataItems (typically OnAfterGetRecord triggers). This does not have any influence in the label items since they are already loaded by Report Viewer as constant value in the Parameters read-only collection.
Luckily, this is not always the case and we could use ML labels as constant values instead of redundant and repeated values added as Columns in the DataItems. Since labels are not part of the dataset, this will be reduced in the number of columns improving performance in loading the dataset client-side and raising higher the out of memory exception bar (the application is able to process more rows).
In this example, standard Report 4 has been refactored and 9 columns has been deleted and values added as ML Labels:
TIP #3 : Refactor decimals and use FORMAT(decVar) where and if possible
This is tricky. Microsoft Dynamics NAV RDLC decimal values are pushed to the dataset together with their formatting. This means that the dataset will have a column that store the value and another one that store the value format. This is costly since it will always add an extra column for every decimal value in the dataset.
In some cases, you can transform the decimal to its equivalent string by applying the C/AL Statement FORMAT(decVar,0,1) in the Microsoft Dynamics NAV development environment, and then use a ML label for all the decimal value format (or several ML labels if there are different decimal formatting inside the report). In Visual Studio, remember to transform back the string into a valid decimal value using the VB .NET =CDec( function.
In this example, the standard report 4 has been refactored, and 5 columns containing the same formatting clause has been silently deleted (not created):
And a ML Label has been created and referenced in Visual Studio where needed:
|Report PRE||37 Columns|
|Report POST||22 Columns (8 Header columns)|
|Gain||Dataset reduced by 41 % (in quality – header – by 62 %)|
We have attached our version of report no. 4 as a text file so you can more easily compare this to your own version.
These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.
|Duilio Tacconi||Microsoft Dynamics Italy|
|Alessandra Pandini||Microsoft Dynamics Italy|
Microsoft Customer Service and Support (CSS) EMEA