Writing RDL Reports in Microsoft Dynamics NAV

So, you’ve been working with Microsoft Dynamics NAV for a while and you’ve got some skills writing reports in the classic client. Now you’ve made the leap to NAV 2009 and the RoleTailored Client – but you discover that the report writing goalposts have been completely moved.

All of a sudden you need to develop reports in Visual Studio – and it looks like a bit steep up that learning curve.

So, how do we get some value out of RTC reports? How can we make some use of the new features without having to relearn everything we know about report writing?

This isn’t a post about how to re-create complicated reports e.g. invoices for RTC, but rather giving some pointers about how to exploit the new features for some quick gains.

Classic client installed? Check.

Visual Studio installed (which may be the “SQL Server Business Intelligence Development Studio” – love that name, which is a component of SQL installs these days)? Check.

The Basic Principle

Anything that you want to appear in the Visual Studio designer must appear on the sections of the classic report. When you run an RTC report, the dataset is generated by the classic report and is passed to the RDL layout for formatting.

Controls on the sections of the classic report have a new property – DataSetFieldName. This is the name of the field that will be created in the dataset that you’ll be working with in Visual Studio. The name must be unique. If you leave it blank, NAV will automatically generate a name. Just be aware that if you copy and paste a control, you will need to manually change the name of one of the controls.

Matrix Controls

Perfect for those occasions when you want to show “X by Y and Z”. For example, what is the Remaining Amount on invoices by Customer No. and by Due Date? All you need to do is create a new report with those three fields from the Cust. Ledger Entry table in the section of classic client report, which can easily be accomplished with the report wizard.

image

Open the Visual Studio designer (View, Layout) and you are presented with a blank page. From the toolbox on the left hand imageside of the screen, drag a Matrix control. Now from the dataset (also on the left) drag Due Date to Columns, Customer No. to Rows and Remaining Amount to Data.

Notice that when you put the Remaining Amount in the Data space it is automatically changed to =Sum(…Remaining Amount…). As it is matrix we will be interested in the sum of the entries for each combination of customer and due date. The rows and columns will expand automatically to accommodate the data in the report.

Now close Visual Studio, saving when prompted. Back in classic client, reload the RDLC definition when prompted and save the report.

Now to test the report. You can either add the report to the menu and run it from there or, while you are still testing, run by opening RTC and typing the following into the Run box:

DynamicsNAV:////RunReport?Report=x where x is the ID of your report.

imagePreview the report and admire…

OK, so it’s still quite ugly and the rows and columns are not in order – but the theory is sound. Remaining Amounts by Customer and by Due Date.

It won’t take much fiddling with the properties of the matrix to get this report looking presentable.

Hit the save icon at the top and you’ll find another of the big benefits of RTC reports – you can save straight to Excel or PDF.

Other Cool Stuff

Hopefully you can see that you can use an RTC report to display data in a more useful format without having to invest a huge amount of time in the intricacies of RDL reports. What else can you do?

Matrices; saving to Excel and PDF; charts – work with charts in a very similar way to Excel, 22 different chart types and complete with 3D effects; images and colours; optionally hiding/showing sections of reports; dynamically sort by different columns in a table…

Leave a Reply