Using a SQL view in a NAV 2009 RDLC Report

What if a user asks:

Could you create a report showing the inventory by Item, but in all of our companies?

Sometimes I see the strangest and most exotic constructions or C/AL coding to answer these kinds of questions, when a more simple solution could do the same and better. 🙂

In the ‘old’ days there was only the native database (.fdb) and when you wanted to display information in a form or report coming from multiple companies using standard C/AL then there was the CHANGECOMPANY function and that was about it.

Via OCX and Automation you could also use ADO (or something similar) to query the database. And in Dynamics NAV 2009 R2 (and beyond) you can also use a DOTNET variable and all it’s features.

But, in NAV 2009, if you are on RTC, you have SQL Server at your disposal, so why not use it?

SQL Server has this thing called: View, that can be understood as an abreviation for: ‘named query’. You create a query, even a complex one, and save it as a view. Why? Because then you can use the view like a table and query it.

A view can also be understood as a virtual table. The definition of the view is saved in the database, but not the data. So when you querie a view, you are querying a query. It means, that the named query will also be executed when you query the view. So for performance there’s no real benefit.

Note:

You could also create an indexed view and so the data of the view will be saved in the database. This way you can gain performance when querying the view. But this is only possible in the Enterprise version. As from NAV 5 SP1, NAV does it for it’s SIFT-views, but in a special way so you don’t need the Enterprise version.

So, why am I explaining this?

Because in a SQL query it’s very simple to querie multiple tables in the same database and even in multiple databases. Consider the NAV Demo database. In the BE localisation we have 2 companies: “CRONUS BELGIË NV” and “CRONUS BELGIQUE SA”. A query to find the inventory in “CRONUS BELGIË NV” could be:

SELECT

company = 'CRONUS BELGIQUE',

ILE.[Item No_] AS No_,

Inventory = ISNULL(SUM(ILE.Quantity),0)

FROM

[CRONUS BELGIQUE SA$Item Ledger Entry] ILE

GROUP BY ILE.[Item No_]

So to get the inventory for both databases we just need to execute the query twice, meaning once for both companies, and past the resultsets, no? Well, you can do this via a UNION ALL:

SELECT

company = 'CRONUS BELGIQUE',

ILE.[Item No_] AS No_,

Inventory = ISNULL(SUM(ILE.Quantity),0)

FROM

[CRONUS BELGIQUE SA$Item Ledger Entry] ILE

GROUP BY

ILE.[Item No_]

UNION ALL

SELECT

company = 'CRONUS BELGIË',

ILE.[Item No_] AS No_,

Inventory = ISNULL(SUM(ILE.Quantity),0)

FROM

[CRONUS BELGIË NV$Item Ledger Entry] ILE

GROUP BY

ILE.[Item No_]

Note:

The difference between UNION and UNION ALL is that without the ‘ALL’, duplicates are removed.

So now we need to save this as a view:

CREATE VIEW [dbo].[vw_ItemInventoryMultiCompany] AS

SELECT

company = CONVERT(VARCHAR(30),'CRONUS BELGIQUE SA'),

ILE.[Item No_] AS No_,

Inventory = ISNULL(SUM(ILE.Quantity),0)

FROM

[CRONUS BELGIQUE SA$Item Ledger Entry] ILE

GROUP BY

ILE.[Item No_]

UNION ALL

SELECT

company = CONVERT(VARCHAR(30),'CRONUS BELGIË NV'),

ILE.[Item No_] AS No_,

Inventory = ISNULL(SUM(ILE.Quantity),0)

FROM

[CRONUS BELGIË NV$Item Ledger Entry] ILE

GROUP BY

ILE.[Item No_]

GO

Note:

Did you notice the CONVERT(VARCHAR(30),’COMPANYNAME’)? The reason is to make sure the column in the view will be defined allowing 30 characters.

You can query the view just like a table, for example:

SELECT * FROM vw_ItemInventoryMultiCompany

Now we are going to ‘import’ it into our NAV database.

What we need to do is to create a table that has exactly the same name as the view and exactly the same fieldnames with compatible datatypes. For example:

Note that I make it a habbit to make the fields not editable. Although it’s possible to to inserts/updates/deletes via views in SQL Server, I would not recommend that.

Now when you save the table you can run it:

Furthermore, you can use it like any other ‘virtual’ table, for example as a SourceTable in a Form/Page/Report/… For example:

This is just a very simple and basic example. You can imagine that we could make it a much more complex query using SQL to calculate, combine, group, aggregate, … data coming from multiple tables, companies, databases, xml files,…

In this example report I used a Chart to visualize the information. As you can see there’s also a request page in which the user can filter on Company and/or Item. When I created the RDLC I was playing with the yellow/green colors, but you will probably notice 🙂

You can download the report here:

vw_ItemInventoryMultiCompany

Usually when I prepare to release a blog post I do some research to see if someone else might have blogged about the same subject, and it seems in this case there are several good posts out there about using SQL views in Dynamics NAV.

Some very good post are the following: