It is undeniable that a good reporting tool is a must-have in order to successfully manage your business.
The Dynamics NAV 2009 release brought a lot of changes in the application architecture, as well as the objects’ structure, especially as it concerns reporting. And as we move from NAV 2009 to the NAV 2013 version, RDLC (“Report Definition Language Client”, for those non-techies out there) changed its format from 2005 to 2008 respectively, bringing new controls and tools for building effective reports.
In this post, we’ll examine some of the features of the latest RDLC version which are really helpful in data representation.
In this context, we will work with the latest Dynamics NAV 2013 version.
A simple report on salesperson statistics will be used to demonstrate the new report engine’s powerful features. This is how the initial report will look without any visual enhancements:
The top level is grouped by Salesperson, printing a new page for each. Level 2 is grouped by invoice number, and level 3 represents invoice lines.
At the end of every invoice there is a total. All in all, the report looks ok, but it can look much better.
(1) New Dataset Designer
NAV 2013 has removed the classic native Section designer, leaving the report layout to be fully done in Visual Studio environment. With this change, the NAV report designer has undergone serious changes as well.
The new Dataset Designer serves for building the general report structure with the data items and fields (columns) that can be included in the final report. As for the actual layout – this can only be done in Visual Studio.
Below, you can see how the new Dataset Designer for the same report looks in NAV 2013 (left) compared to the previous version of NAV (right):
The old NAV Report designer had Section designer to build the layout. It complicated the process because the RDLC report engine would be able to “see” the data only from the fields that were present in the NAV report designer as textboxes. However, in Classic reports there was a need to make such auxiliary fields hidden.
Moreover, NAV Section Designer allowed writing code on section triggers. This code was not “seen” by the RDLC, so it should have been placed in the relevant data items’ triggers. As we can see, the new report designer has a more simplified structure that focuses on building the dataset skeleton.
Let’s look at some useful RDLC features which might make a standard report look more functional.
(2) Interactive sorting
This is a useful feature offered by the RDLC layout for NAV reports. It allows sorting the report columns as needed. Here’s how it is implemented in our sample report; in this case we are sorting the Amount column:
The small arrow next to the Amount column caption is clickable. It allows sorting our amounts column (invoice lines). In this case the sorting order is ascending.
This feature is really helpful in financial reports, e.g. when analyzing the profit by departments, or running monthly account schedules/budget reports, etc. Such dynamic sorting can be applied not only to amounts, but to the other database entities (customers, vendors, items, etc.) represented as columns in the report.
The setup for this feature is simple. In the necessary textbox, enable the Interactive Sorting property and assign the sorting cell:
It is also possible to assign the sorting to multiple cells at the same time, so that each report column will be sorted separately.
(3) Dynamic report sections
Another useful RDLC feature allows you to expand/collapse report sections. This is especially handy when the report has a complex structure with several nested data items and is “heavy” to fit on one page.
This is how our report will look like if we would like to show or hide the invoice lines, and simply receive an overview of invoices per salesperson with totals:
The cell which is “responsible” for expanding/collapsing invoice lines in our case is the No. field. By clicking the plus/minus sign, the detail rows are shown/hidden. To enable this feature, in the Visual Studio report designer, we need to select the row which we would like to toggle, and edit its properties, as follows:
Textbox15 in our example above is the name of the cell which stores the Invoice No. caption. It is right next to this cell where we will see the “plus/minus” sign to show/hide the sections.
This feature can be applied not only to a single row, but to a group of rows, or even a data region, such as “List”.
(4) Conditional formatting
The Classic NAV report designer offers limited tools for applying coloring on report sections: all a user can do is change the cell color, which will have a static appearance during the full period of the report run. RDLC is way more flexible in this feature: reports can have conditional coloring of cells, rows, columns and even data regions.
Let’s look at our report which will list salesperson invoices that have a total invoice amount less than $10,000 (depicted in red below):
In the Classic NAV report designer, textbox had no triggers at all, whereas in RDLC it is possible to code conditions for various textbox properties: font, color, visibility, alignment, etc.
(5) Report Hyperlinks
It is often convenient to navigate to another report or page from within the parent report, in order to drill down to, for example, sub ledger data or a particular document. Visual Studio allows assigning hyperlinks to NAV objects and opening them from the parent report.
If we look at our sample report, we might assume that a financial officer would like to have a look at the particular invoice with red totals. In this case we place a hyperlink to the relevant invoice from our statistics report:
The link will work the same way as in any browser, where the report invoice number is linked to a posted sales invoice number on the page. In this way, the user can quickly open the necessary invoice document.
Apart from these enhancements, it is now possible to build-in analytical data into a report: charts, diagrams, etc., as well as building complex BI reports. It is obvious that Dynamics NAV’s move to the RDLC layout designer greatly benefited the report layout flexibility. Almost all the features of SSRS report are now available in the NAV system, which makes the application reporting really powerful for company management at all levels.