Reporting Services on Dynamics NAV

 

When using Reporting Services with Dynamics NAV there are a number of things to take into consideration.

 

One of the main things to consider is the way the SQL Tables are structured with Dynamics NAV. For each company in the database there will be a set of company specific tables. These are prefixed Company Name$. So for example the Customer table for a company called CRONUS International Ltd. is CRONUS International Ltd_$Customer.  Note the '.' at the end of the company name has been replaced with an '_'

This is based off of a setting in NAV that defines which special characters are replaced, and is done to make life simpler in SQL.

 

This means that when writing reports you need to know the company name of the NAV Company you want to report on. Where there is only 1 Company in the database this isn't to painful, however as a developer it means every time you want re-use a report into a different company you need to go back in and change the Company name part of each table. It also means that, assuming you are going to deploy your reports to a test environment prior to a live deployment' that your NAV Company in the test system has to be the same as in your live one, a practice that can cause other confusions when using the NAV Client.

 

To get round this problem you can do one of 2 things.

1. You can generate a SQL View to present the data you want to report on and leave the company name out. This at least makes the reports cross company compatible as you only have to build the views once for each company.
2. If you are basing your Reports on data prepared by stored procedures you also have the option of using dynamic SQL and passing the Company Name as a Parameter at run time.

Both of these options also are fine where you have multiple companies in the database as you can add an identifier to the SQL View and use a Parameter in the report to filter for the correct data.

However, as with a project I am currently working on, when you have multiple companies and the prospect of more being added and some being closed out on a regular basis the prospect of keeping the SQL Views up to date can force you to use the second option only, i.e. use dynamic SQL and pass the company name as a parameter.

 

Another thing to consider is that not every field you can see in the NAV client is actually held on the underlying SQL Tables. NAV flowfields are one such example. Form experience this can make what appears to be a simple report very complicated.