This blog is about all aspects of Microsoft Dynamics NAV, offering technical and customer-oriented how-to articles. Topics of special emphasis include upgrades, manufacturing, and reporting.
In Part 1 of this series of posts, we examined the underlying technologies behind RDLC reporting. In Part 2, we looked at the basic process of converting Dynamics NAV classic reports to an RDLC layout.
Since then, we’ve been working through the specific issues you will encounter during the conversion process.
Since part 5, we’ve been walking through the conversion of Report 5703 – Transfer Order as an example of the issues you’ll face in converting a document report – the type of report likely to pose your biggest challenge in transitioning to RDLC layouts. This is the last post you will need to follow in order to complete the conversion of this specific report.
In Converting Dynamics NAV Classic Reports To RDLC – Part 9 of this series, we examined both the classic dataset and RDLC table structure that resulted from the Create Layout Suggestion process for Report 5703 – Transfer Order, and we found that the two were not in sync.
Indeed, it might be argued that synchronizing your classic dataset and the table structure of your RDLC report is the most important task in the conversion process.
As with any conversion effort, it is important to understand both the source and target of the conversion. For this report, when the Show Internal Information checkbox is selected, and the number of dimension rows associated with the each order header record is 2, and the number of dimension rows associated with each order detail record is 3, the theoretical flattened source data structure is:
Header 1 Fields + Header 1 Dim. Row 1 Fields + Blank Detail Fields + Blank Detail Dim. Fields Header 1 Fields + Header 1 Dim. Row 2 Fields + Blank Detail Fields + Blank Detail Dim. Fields Header 1 Fields + Blank Header Dim. Fields + Detail 1 Fields + Detail 1 Dim. Row 1 Fields Header 1 Fields + Blank Header Dim. Fields + Detail 1 Fields + Detail 1 Dim. Row 2 Fields Header 1 Fields + Blank Header Dim. Fields + Detail 1 Fields + Detail 1 Dim. Row 3 Fields Header 1 Fields + Blank Header Dim. Fields + Detail 2 Fields + Detail 2 Dim. Row 1 Fields Header 1 Fields + Blank Header Dim. Fields + Detail 2 Fields + Detail 2 Dim. Row 2 Fields Header 1 Fields + Blank Header Dim. Fields + Detail 2 Fields + Detail 2 Dim. Row 3 Fields
This leaves us with two potential subsets of records within our flattened dataset:
Depending on the report options and the underlying data, this report might encounter many other variations, as well, including the complete absence of dimension rows at either the order header or order detail level.
So what kind of RDLC table structure do we need to accommodate this somewhat complex situation?
First, with the potential for a one-to-many subset of header dimensional records on the same level as the the one-to-many subset of order detail records (i.e. both linked to order header record and thus at the same level in the data hierarchy), your gut instinct should tell you that you need two tables, not one.
Listen to your gut and add a new table. Position this new table above your existing table, so it is the first reporting object in the report’s body section.
This new table will contain only a detail row (no table or group headers or footers) with fields for the order header dimension records, as can be seen here in Microsoft’s RDLC layout for Report 5703 – Transfer Order:
There is no magic to the new table. It simply prints the Header_DimensionsCaption and DimText fields that originate from the classic version of the report. However, there are a couple of steps you’ll need to take to make it work properly in your own report (i.e. in addition to adding the new table and detail row, adding two Textboxes to the detail row, and hooking them up to the Header_DimensionsCaption and DimText fields).
First, you need to return to the classic version of your report and add two new hidden TextBoxes:
As you should know by now, the reason we’re placing these hidden Textboxes in sections of our classic report is to ensure that the fields they reference make it into our RDLC dataset.
What will we do with these data fields on the RDLC side? We’ll use them as follows:
IIf(Fields!DimensionLoop1_Number.Value > 0,False,True)
This ensures our new table will only show if there is header dimension data.
…which ensures that only valid header dimension data is processed by the table (i.e. it filters out all records in the dataset that weren’t generated from DimensionLoop1).
Within the table, you also need to apply a visibility expression to the TextBox in the table’s first column, i.e. the one that displays the Header_DimensionsCaption field. The expression is:
=IIF(Fields!DimensionLoop1_Number.Value = 1,False,True)
…which ensures that the header dimension caption displays only once, for the first header dimension row.
The main table in the Microsoft RDLC version of Report 5703 – Transfer Order appears as follows:
The rows in this table consist of:
There is very little RDLC magic to this table, either, as all the data rows/fields already flow through to the RDLC dataset from the classic report. The steps required to recreate this table structure in your own report are:
1. In the table properties, navigate to the General tab and set the values as shown:
2. Add a table header row.
3. Move the column headings from your existing group header row into the new table header row.
4. Move the data fields from your existing details row into your existing group header row.
5. Edit the General tab of the existing group header row and group on Transfer Header No., OutputNo, and Transfer Line No., as shown below:
By grouping on these three fields, we eliminate the duplicates cause by using a flattened dataset.
6. Make sure your group is sorted on the same three fields, in the same order, as you’re using for the filter.
7. Add the following visibility expression to your existing detail row:
=IIF(Fields!DimensionLoop2_Number.Value > 0,False,True)
8. Add TextBoxes to the detail row for the transfer line dimension caption and dimension value fields. Note, you will have to merge cells and adjust widths, and of course hook the TextBoxes up to their source fields (which may have different names in your report than in ours).
9. Add the following visibility expression to the Textbox that contains the transfer line dimension caption (whatever its actual name):
=IIf(Fields!DimensionLoop2_Number.Value = 1,False,True)
…which ensures the caption will print only once.
10. Add a Table footer row to your main table and move the shipment code fields (caption and value) previously in your group footer row into this new row. Again, you will have to merge cells and make some layout adjustments to get this right.
11. Delete your old group footer row (which should now be empty).
You will undoubtedly still have some layout tweaking to do, but if you have implemented all this correctly, your RDLC version of Report 5703 – Transfer Order should now be fully functional. Its output should look like this:
We have a few more report types we’d like to cover in this series on converting classic reports to RDLC layouts, but we’ve come to realize that by lumping them all together, we’re making it difficult for people to find information on how to convert specific types of reports. So, going forward, were going to address each type of report conversion in its own post or series of posts.
You just finished reading Converting Dynamics NAV Classic Reports To RDLC - Part 10! Consider leaving a comment!
For info on all aspects of Microsoft Dynamics NAV, visit our main site at Liberty Grove Software.
Other Microsoft Sites
I'm a Customer
I'm a Partner
Use the official Twitter tags:
#MSDYNCOMM | #CONV13