A Couple of RDLC Performance Optimization Tips


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:

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

Comments (5)

  1. Micha says:

    Nice information, thanxs,

    but … Tip #3 does not work, if you use the decimals for functions like "Sum" and so on ….

  2. Micha says:

    upps, i haven' read this:

    B .NET =CDec( function.

    Sorry

  3. Sebastiaan Lubbers (Mercash) says:

    Also all TextConst for the captions could have been removed after this. But seriously nice article.

  4. Sebastiaan Lubbers (Mercash) says:

    For tip #3 you should use FORMAT(<Field>,0,9). Works with US and NL regional.