Using SSRS with Dynamics NAV—Creating Views to Write SSRS Queries

Our consultants write hundreds of SSRS reports each year. Now that we’re working with Dynamics NAV, we want to use our SSRS and SQL skills without having to train everyone in the intricacies of C/AL development.

While C/AL uses SSRS to render reports, we only see value in going native C/AL when reports are tied to database updates or are a key part of a process—such as test reports or posting printouts.

As I covered in Understanding the NAV Database, one of the challenges in working with NAV is that each company has its own set of tables. And this is one area where C/AL comes in handy.

Take, for example, “Purch. Inv. Header” (for posted purchase invoices). Using the Cronus sample companies, we have three different tables:

When writing a query for SSRS, you can deal with this in several ways:

1. You could write your report for just one company (which works fine if you only have one company).
2. You could create stored procedures with Dynamic SQL. (Your procedure would take the company as a parameter and then build the SQL. It would be ugly.)
3. You could create views of the underlying tables and write your queries over those views.

If you’re dealing with more than one company, the third option is your best bet. In this post, I’ll show you how to execute it.

Let’s illustrate using the example above. Instead of using the tables, we create a straightforward UNION of all three tables.

In addition, instead of using full company names (with the ridiculous punctuation), we create abbreviated names for each company (e.g. Cronus USA –> CUS):

Now, let’s look at the output. We can see all the data, identified by the appropriate company:

Once we’ve built the view, we can treat it like a table. For example, if we build a view on the above called dbo.[vNAV_Purch_ Inv_ Header], we can write a query like this:

(Hint: Company choice is a parameter, not a table name.)

While most ERP systems have hundreds of table, usually you only need a couple dozen views (at most) to handle the vast majority of your reporting needs. But that doesn’t mean you should build all those views manually. It’s too time consuming and prone to error.

(Further, we like to place these views in a different “reporting” database. That way, we don’t create objects in our NAV database that NAV doesn’t know about.)

To that end, I’ve created a simple stored procedure (below) that includes a list of companies and tables we can create views over. We can run this procedure any time, and the views will automatically be altered or created:

While I’ve used these views with good success, I haven’t tested them with large data volumes. I suspect that if you combined multiple companies in one report, performance could suffer. Unfortunately, UNIONS aren’t great for performance.

Solving the performance issue is beyond the scope of this blog post. It will require either an indexed view or perhaps a set of reporting tables to resolve.

One more note: If you look at Execution Plan when selecting a “company” from within the view, you’ll see that the number of actual and estimated records is the same—whether you go directly to a table or use a view/company combination.