Building a Matrix Report in Microsoft Dynamics NAV 2009 SP1

The final Service Pack 1 for Dynamics NAV 2009 is now just around the corner, so with that I would like to share with you one of the new possibilities in the reporting area, Matrix reports.
First I will go through how to build a Matrix Report in NAV 2009 SP1, then I will demo some Matrix reports done by two of our MVPs of Dynamics NAV and one of our partners. In the end of this blog I will share all the Matrix reports. So if you are not interested in how to build a Matrix Report in NAV 2009 SP1 or already know how to do this in NAV 2009 SP1, this is the time for you to scroll to the bottom of this blog, if you just want to download the objects. :-)

If you are still here and have not scrolled to the bottom, let us get started building a Matrix Report.

Building Matrix Report in NAV 2009 SP1

In this walkthrough we will be creating a Matrix Report sorted with Items by Location.
When you have finished this Walkthrough you will understand how to build a Matrix Report in NAV 2009 SP1.

1. Select “New” in Object Designer with Report object selected

2. Add “Location” as Table

3. Select “Create a blank report“ and click “OK”

4. Insert “Location” as the first DataItem

5. Insert “Item” as the second DataItem, make sure to indent under the “Location” DataItem.

6. Save Report and give an “ID” and a “Name”

7. Now we have created the DataItems for this report, so now we need to create the Data Source for us to be able to create the Layout of the Report in Visual Studio. For this we need to go to the Section Designer. Open Section Designer

8. For now the Section Designer is Empty, so we need to add some Fields. We would need these fields:

· Items by Location – Title

· Code – Location

· Name – Location

· No. – Item

· Description – Item

· Inventory – Item

9. Make more space to have more fields in each of the Sections.

10. Add a Label with Caption=Items by Location

11. With “Location” Section selected, open Field Menu:

12. Open Field menu and select “Code” and “Name”

13. Add these 2 fields to the “Location, Body”

14. Navigate to the “Item , Body” and select “Field Menu” and select "No.", "Description" and "Inventory"

15. Add these 3 fields to the “Item, Body”

16. We have now created the Data Source for the report. Now we need to create the layout for this report.

Select “View / Layout”. Visual Studio will open.

17. In Visual Studio you will now see these elements in Data Source we can work with:

18. Lets us now design the layout.

19. In the toolbox select the Matrix control and drag this to the Report Body

20. Drag “Items_by_LocationCaption” to the top left cell in the Matrix control

21. In the “Rows” just below I want to add 2 rows directly under “Items by Location”, so we need to add a new group. Right click the “Rows” cell and select “Insert Group”

 

22. “Grouping and Sorting properties” dialog box will come up.

Select to group on “=Fields!Item_Description.Value” and click “OK”

23. In the new cell to the left of “=Fields!Item_Description.Value”, right click and select “Edit Group”

24. Select to group on “=Fields!Item__No__.Value” and select “OK”

Your report should now look like this:

25. It is now time to add the column above the data, and in this report we want to see in how many items are stored in each warehouse. Add “=Fields!Location_Name.Value”

26. And at last let us add the data cell in the bottom right corner of the matrix control. Add “=Sum(Fields!Item_Inventory.Value)” to the Data cell.

27. Now before viewing the report let us already do some resizing of the layout, for this to readable on one page. I choose to narrow the columns, so the report now looks like this:

28. Also to make this report more readable friendly, let us add a Solid Border style. Select all cells in the Matrix control, and select “Solid” for the “BorderStyle” property

29. Now let us view the report, save, import, compile and run the report. We now get this output.

Hmm, something is wrong. I.e. we have 32 bicycles on stock in each of the warehouses. Let us fix this issue.

30. Go to the DataItem “Item” and set the property “DataItemLink” to “Location Filter=FIELD(Code)”

31 . Now let’s run the report again, but now we set the following filter on the Request Page: Inventory is >0

We do this to avoid all entries with 0.

32. Now select “Preview” to see the report.

33. We now have a working Matrix Report which is sorting Items by Location.

34. Now let us imagine you would like to know the Inventory Availability on the 52 “PARIS Guest Chair, black” in the “Blue Warehouse” or on the 55 “AMSTERDAM Lamp” in the “Red Warehouse”. So let use the Drill Through to Report feature in Dynamics NAV SP1 to drill through to our existing “Inventory Availability” report 705. Open Visual Studio again by selecting “View / Layout”

35. Right click the Data field in the Matrix control and select “Properties”

36. Navigate to the “Navigation” tab

37. Select “Jump to URL” and add the following expression:

="DynamicsNAV:////runreport?Report=705&Filter=Item.%22Location Filter%22:"+Fields!Location_Code.Value+"&Filter=Item.%22No.%22:"+Fields!Item__No__.Value

What we are doing here is that we open Report 705(Inventory Availability) filtered on Location and Item No., when we click data field in the Matrix report.

38. Before we save the report, let us make it visible in the report that we have a link to another report.

With the Data field selected set these properties:

Color=Blue

TextDecoration=Underline

39. Save, Import and Compile the report. Note you might this error:

Set the EnableHyperlinks=TRUE, and compile again

40. Run the report. It will now look like this:

41. Now when we click on the 52 “PARIS Guest Chair, black” in the “Blue Warehouse”, Inventory Availability report will now open based on the our Blue Warehouse and our Paris Guest chair.

And when we click on the 55 “AMSTERDAM Lamp” in the “Red Warehouse”, Inventory Availability report will now open based on the our Red Warehouse and our Amsterdam Lamp.

Now let us look at some other Matrix Reports

1. Items by Location done by Andrey Panko, MVP for Dynamics NAV. This report is very similar to the above walkthrough, it has extra logic on the data part and more advanced coloring and a Totaling to the very right. It does not contain any Drill Through to report 705, but this could easily be added following the above steps(35-37) on how to that.

2. Items by Location done by Rene Gayer, MVP for Dynamics NAV. This report is also similar to the above reports but here Rene is using the new Drill Through to report, so if click on the Item, left most column, you open a Item Dashboard, with Key Performance Indicators, Stock level illustrated and Sales History.

 

3. GL Entries per Account per Month, done by GAC Business Solutions. This report gives a overview of the entries in GL Account pr month. Notice when clicking the GL Account you will Drill Through to GL Account card, and when clicking the amount in the Matrix you will Drill Through to the Detail Trial Balance Report filtered on month and GL Account

Please find all the mentioned reports here:

Thanks, to Andrey Panko, Rene Gayer and GAC Business Solutions for sharing their Matrix Reports.

Thanks,
Claus Lundstrøm, Program Manager, Microsoft Dynamics NAV