A Couple of RDLC Performance Optimization Tips - Dynamics Nav

A Couple of RDLC Performance Optimization Tips

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:

Data Source	Name
COMPANYNAME	CompanyName
ExcludeBalanceOnly	ExcludeBalanceOnly
PrintReversedEntries	PrintReversedEntries
PrintClosingEntries	PrintClosingEntries
PrintOnlyCorrections	PrintOnlyCorrections
GLFilter	GLFilter
STRSUBSTNO(Text000,GLDateFilter)	PeriodGLDtFilter
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:
Name
DetailTrialBalCaptionLbl
PageCaptionLbl
BalanceCaptionLbl
PeriodCaptionLbl
NetChangeCaptionLbl
GLEntryDebitAmtCaptionLbl
GLEntryCreditAmtCaptionLbl

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):

Data Source	Name
FORMAT(StartBalance,0,1)	StartBalance
FORMAT("VAT Amount",0,1)	VATAmount_GLEntry
FORMAT("Debit Amount",0,1)	DebitAmount_GLEntry
FORMAT("Credit Amount",0,1)	CreditAmount_GLEntry
FORMAT(GLBalance,0,1)	GLBalance

And a ML Label has been created and referenced in Visual Studio where needed:
DecimalFormat              #,##0.00

OUTCOME and STATISTICS

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
R4.MOD05.txt