NAV Reporting

Goodbye NAV-REPORTING blog

2009-10-23T22:55:09Z

To make it more simple for you guys to find and discover blogs post from the NAV team members we are combining our blogs into the NAV Team Blog, so we will no longer be posting on the NAV-REPORTING blog. All post from this blog have already been migrated to the NAV Team Blog.

If you still want to follow blogs especially on the reporting topic, please follow this link: http://blogs.msdn.com/nav/archive/tags/Reporting/default.aspx

You can also set up RSS feeds for only Reporting topics on the NAV Team Blog: http://blogs.msdn.com/nav/rss_tag_Reporting.xml

See you all at the NAV Team Blog.

Thanks,

Claus Lundstrøm, Program Manager, Microsoft Dynamics NAV

Printer Selections in Role Tailored client

2009-10-19T15:48:00Z

Today Printer Selections Form 64 is not transformed to Printer Selections Page 64 since virtual table 2000000039 is not accessible from Role Tailored client.

I thought of different options for how we could get page 64 to work and decided to try the new Client Extensibility feature in NAV 2009, although this could also be done with filing a temporary table from COM.

I would like to share this option with you.

Page “70809 Printer Selections FactBox” is using a client-addin, based on C#, to read the local installed printers and offers the way to select the name and save it to NAV 2009.

Page “64 Printer selection” is enhanced with the new fact box.

You can download the files here:

You can use the file for your own risk. There is no support for it.

(Note: Before you can use the page 70809 you have to install the client extension library “printerslections.dll” on your machine. The easiest way to do this is using the Add-Importer from Christian. )

Thanks,

Rene - Microsoft Dynamics NAV MVP (navmvp@live.at)

Send email with PDF attachment in NAV 2009

2009-10-08T16:26:00Z

In this post I would like to explore the possibilities to create an email from the Role Tailored client and attach an invoice as a PDF file to the email, unfortunately we have do not have this functional build into our Demo application, but let me show you how this can be do with little effort.

First I suggest you download the fob file which contains the 5 different options I will go through here.

When downloaded the fob file you will see that I have added 5 new actions

1. SendAsPDF(Use of codeunit to rename) recommended solution
2. SendAsPDF(Access to Server needed)
3. SendAsPDF(With Temp file name)
4. SendAsPDF(User prompted to save)
5. SaveAsPDFrecommended solution(if you just want the PDF file)

Let me go through the different options starting from the bottom, since I recommend option 1, but I would also like to share other options for doing this, since these might be valuable for you.

Option 5: SaveAsPDF
In this option you will get prompted if you want to open or save the PDF. The PDF file created will be based on the select Invoice in the Posted Sales Invoices List Place

In this option all I do is to have the server create the PDF file for me and use the new download function in NAV 2009 to retrieve the PDF file created on the server.

Option 4: SendAsPDF(User prompted to save)
In this option, you will first be prompted to save the file.
Here it is important to select to “”SAVE” the PDF file on the disk, to have the correct name of the PDF file. If you select to “OPEN” the PDF filename will be given a temp name.

After you have saved the PDF we now create the email message you will get 3 messages similar to this when this happens:

You get these message because we connect to an external component(Outlook) to the Role Tailored client. It is of course up to you if you want to set this to “Always allow”, but this would remove these messages, the next time you open the Role Tailored client.

When you have allowed these to run, email will be created with the PDF file attached.

In this option all I do is to download the PDF to the client and then use the Mail codeunit to create the email

Option 3: SendAsPDF(With Temp file name)
In this option, you will not be prompted to save the PDF file.
And the email will be created immediately. This would probably be the preferred compared to downloading this to the user disk, but we will use the PDF file created on the server, and since this file get a TEMP name, like this: “__TEMP__570eb0279b9d4b1fa837caf3a14acbf7” this option is not really good.

Let us look at the option 1 and 2 where this issue is solved.

Option 2: SendAsPDF(Access to Server needed)
In this option you will not be prompted to save the file either, but here the end user will need to have access the server folder where the PDF is stored on the server. In some situation you might want this, but for security reasons you might also not want to give this access to the all user.

Option 1: SendAsPDF(Use of codeunit to rename) recommended solution
Again in this option you will not be prompted to save the PDF file, but the PDF file will be automatically added to the email. In this option we have build a codeunit to rename the TEMP file created on the server, and end user will not need to have access to any folders on the server.

So all in all I recommend option 1 for attaching PDF file to an email. And once again I have made all the code available here, so feel to be explore how I build this. If you feel there is an option that I missed feel free to leave a commit or use the contact form Email.

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

Microsoft Dynamics NAV 2009 SP1 ready for download

2009-09-01T12:04:15Z

We have now released Service Pack 1 for Microsoft Dynamics NAV. So you will now all be able to use the new Reporting features in this Service Pack. Previously I have described all new reporting features in this Service Pack. Please find all these features described here: What is new in Dynamics NAV 2009 SP1 for Reporting.

You can find the download for Microsoft Dynamics NAV 2009 SP1 here:

CustomerSource: https://mbs.microsoft.com/customersource/downloads/servicepacks/MicrosoftDynamicsNAV2009SP1.htm

PartnerSource:
https://mbs.microsoft.com/partnersource/downloads/servicepack/MicrosoftDynamicsNAV2009SP1.htm

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

Building a Matrix Report in Microsoft Dynamics NAV 2009 SP1

2009-08-28T17:26:00Z

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

What is new in Dynamics NAV 2009 SP1 for Reporting

2009-07-07T12:33:00Z

With our Microsoft Dynamics NAV 2009 SP1 Community Technical Preview released it is now time to drill into the new features we have been for working on for reporting in NAV 2009 SP1.

1. Drill Through to a report from a report

In NAV 2009 it was possible to Drill Through from a Report to a Page, see how this is done here.
With the possibility to Drill Through from a report to Page, we had a strong request from partners and customers to add the possibility to Drill Trough from a Report. So in NAV 2009 SP1 it is now possible to Drill Through from a Report to a Report. So how is this done? Well this is not done the way we Drill Through to Pages, but with the use of filters.
A Drill Through to a report could look something like this:

="DynamicsNAV:////runreport?Report=104&Filter=Customer.%22No.%22:"+Fields!Customer__No__.Value

The first part “DynamicsNAV:////runreport?Report=104” is our well know way to link to reports and pages. If you are new to this linking in NAV 2009 read more here.

The last part “&Filter=Customer.%22No.%22:"+Fields!Customer__No__.Value” is what is new for NAV 2009 SP1. The first part will open Report 104, and with the last part, report 104 will be filtered on the “Customer__No__.Value”. If we add this code to a Customer Top 10 List report, we can Drill Through to report 104 both from the List and the Pie Chart. I have made a small video how that is done:

2. Paging

In NAV 2009 the dataset creation was done on server, before it was sent to the requesting client. With this approach, the memory consumption on the server could potentially be very large with multiple user requesting reports at the same time. For this reason we have implemented paging in SP1. With paging the memory footprint on the server is minimal. When the dataset for a report is generated, we are now sending small chunks of the dataset to the client, and thereby building up the dataset on the client. With this approach the client is now taking the memory hit and not the server. I have made small video that illustrates how the dataset was build up in NAV 2009 and in NAV 2009 SP1:

3. About this Report

In NAV 2009 we had the possibility to zoom into the dataset of a report. We also had a very short short as well SHIFT+CTRL+ALT+F12 :-) but in SP1 we have reintroduced the zoom feature for Pages as well. We decided to call this “About this Page”. “About this Page” and can be found in the help menu on all pages. For reporting we aligned this, so in Report Preview we now have a menu called “About this Report”. Also the shortcut is aligned, so this is now CTRL+ALT+F1.

4. Progress Indicator

With paging introduced in SP1, it gave us the possibility to add a Progress Indicator, while the dataset is being created and sent in chunks to the client.

You will now be able to see how long the dataset generation has taken
You will be able to see how many rows has been received from the server
And at last you will be able to cancel a report, something which was not possible in NAV 2009

Note: When dataset has been sent to the client Progress Indicator will disappear and Report Viewer will starter rendering the report. Based on the logic in your layout this can take more time than the actual dataset generation. You will be able to stop this generation by clicking the stop button in the Report Viewer while the report is being generated.

5. Matrix reports

We now also supports Matrix reports, so we can do things like this:

6. Visual Web Developer 2008

We now have full support for Visual Web Developer 2008, so it is no longer needed for you to do the “hack” I described here. Note: As of April 1st. Visual Web Developer 2005 has reached EOL, which means it can no longer be downloaded from Microsoft.com

7. External Images

In NAV 2009 it was possible to add both embedded images and images from the NAV database. With NAV 2009 SP1, it will now also be possible to add external images to your report.

I hope you appreciate the new reporting features which will be available in NAV 2009 SP1. And as always I’m happy to get feedback for reporting features you would like for us to implement in future releases. So use the Contact Form to write directly to the core reporting team in Navision(Dynamics NAV) :-).

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

Report Design in Microsoft Dynamics NAV 2009

2009-07-03T22:37:00Z

We have now released training material for designing reports in NAV 2009.

This training will teach the learners about the concepts of Report Design in NAV 2009 using VS Report Designer along with the limitations and workarounds in Report Design. It covers the new options with practical examples on creating reports in report design, creating and defining data set within NAV 2009 to be used in Visual Studio. In addition, it covers how to implement dynamics features like interactive sorting, conditional formatting, visualizing data using graphs, document maps, creating expressions. Furthermore the training contains information on best practices and recommendations for creating interactive dashboard reports containing KPI’s.

At Training Material Completion
After reviewing this training, individuals will be able to:

Understand the new architecture and reporting features in Microsoft Dynamics NAV 2009.
Create table and matrix reports for the Microsoft Dynamics NAV RoleTailored client in Microsoft Visual Studio.
Add interactive features, such as Visibility Options, Interactive Sort, Document Maps, and Pictures to the report.
Use expressions and custom code in RoleTailored client reports.
Integrate reports in the RoleTailored client user experience.
Run RoleTailored client reports in various ways.

You will find the training material at CustomerSource here:
https://mbs.microsoft.com/customersource/training/trainingmaterials/student/course80146.htm

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

Filtering out blank fields

2009-05-12T16:28:25Z

Some of you have noticed that the Chart displayed in “Customer Order - Summary” contained a series label called Series 1.

Well the reason for that being shown is because we have missed to filter out all customer with have no data in the field: Chart_SalesOrderAmountLCY.Value

So how is that actually done? Well let me show you:

1. Design Report 70000

2. Select “View / Layout”

3. Right click the chart in the bottom of the report

4. Select “Filters” tab

5. Type the follow:

Expression:
=System.String.IsNullOrEmpty(Fields!Chart_SalesOrderAmountLCY.Value)

Operator:
=

Value:
=False

6. With this filter we now filter out where "Chart_SalesOrderAmountLCY.Value” is equal blank.

7. Now save and compile the report and display in RoleTailored client. Now only customers with outstanding orders are shown in the chart.

I have updated the demo reports package to reflect this change.

The reports can be downloaded from here.

Thanks,

Claus Lundstrøm, Program Manager, Microsoft Dynamics NAV

Creating Dashboards in NAV 2009

2009-03-30T16:13:52Z

Hi!

I want to share with you also another report using some of the new capabilities in point of creating dashboards in NAV 2009.

Based on reporting services and NAV data source it is possible to create dashboards for every department. This offers a perfect extension to the default charts directly in the role center.

The report shows you one example based on the sales department to get a quick and/or deep overview about the current situation.

If you are a developer you can also use the report for learning purposes:

Use charts
Use coding in expressions
Use DrillDown
Use own color palette in charts

You can download the report from here:

Thanks,

Rene - Microsoft Dynamics NAV MVP (navmvp@live.at)

Demo Reports

2009-03-30T15:25:00Z

I want to share with you some reports that are using some of the new capabilities that are available for use with RDLC reports in Microsoft Dynamics NAV 2009.

We have prepared 4 reports which uses these capabilities:

Customer - Order Summary:

Use of Conditional Formatting (Table colors)
Use of Conditional Formatting (Colors in cells)
Use of Charts
Use of Hyperlink External(Click the Dynamics Logo in the bottom)
Use of Embedding picture into report

Sales Pipeline:

Use of Conditional Formatting (Icons, expand to see the icons)
Use of Conditional Formatting (Table colors)
Use of Visibility toggle
Use of Hyperlink (Click the Dynamics Logo in the bottom)
Use of Embedding picture into report

Quantity Explosion of BOM:

Use of Visibility toggle. Notice that this is Visibility toggle for “Expand All” replaces the previous table shown. Look for Table 1 and Table 2 in Visual Studio to see how that is done.
Use of Conditional Formatting (Table colors)
Use of Drill Through to Page(Click “Where used?”)
Use of Hyperlink (Click the Dynamics Logo in the bottom)
Use of Embedding picture into report

Customer - Top 10 List:

With Bar Chart

With Pie Chart

Use of Visibility toggle. Now you can switch between Bar chart and Pie Chart in the Report Viewer and are not required to run the Request Page to make this change.
Use of Percent
Use of Point Labels
Use of Hyperlink (Client the Dynamics Logo in the bottom)
Use of Embedding picture into report

The reports can be downloaded from here.

Thanks,

Claus Lundstrøm, Program Manager, Microsoft Dynamics NAV