Microsoft Dynamics NAV 2009: Professional Reporting

By Steven Renders
    Advance your knowledge in tech with a Packt subscription

  • Instant online access to over 7,500+ books and videos
  • Constantly updated with 100+ new titles each month
  • Breadth and depth in over 1,000+ technologies
  1. Charts and Dimensions

About this book

Microsoft Dynamics NAV is widely used in enterprises with its enhanced reporting features, but there still isn’t a book that covers reporting in depth, until now. This book will show you what’s possible and exactly how to develop reports for Dynamics NAV 2009. Learn why reports are designed in a specific way and then apply this knowledge to your advantage.

Microsoft Dynamics NAV 2009: Professional Reporting is an essential guide to understanding the effort involved in creating reports in Dynamics NAV 2009, and all of the available reporting possibilities. Reading this book will result in a better understanding and it will help you save time during development.

The book starts with an overview of what kind of reports you can create without a developer using charts, dimensions, and analysis views. The next chapter will introduce the new architecture of Role Tailored Client (RTC) reports and the basics on how to create them or have them generated. Moving on we will discuss data visualization, where you will learn how and when to implement all the available Visual Studio controls. Document reports and other types of specific reports are covered next, and you will get to know all the best workarounds for getting them to work correctly.

Knowing other related features is vitally important to obtain a helicopter view of all the reporting possibilities of Dynamics NAV, and the next chapter will introduce Business Intelligence concepts and available technologies that could be used in combination with the Dynamics NAV database. Technology is evolving rapidly and new versions of Dynamics NAV are in the pipeline, so the book will also provide you with a view to the future and how to prepare for it.

Publication date:
October 2011
Publisher
Packt
Pages
352
ISBN
9781849682442

 

Chapter 1. Charts and Dimensions

In most implementations, the focus is set on customizing the Dynamics NAV application to meet the needs of the current processes in the organization and sometimes the future needs are also considered. The effort that is required for reporting in general is often underestimated and unfortunately assigned to the least experienced consultants, who have to create/adapt the document reports conforming the look and feel of the customer.

Personally, I believe reporting is one of the most important aspects of an implementation. And so, it should be considered from the beginning, in the analysis phase of the project. The kind of information you want to be able to retrieve from your ERP system and the way you want to retrieve this information has a deep impact on the implementation of the system. Doing this correctly from the beginning of a project can and will save a lot of time, money, and frustration. The unfortunate reality is that many prospects and/or customers look at reporting first when they want to reduce the budget of an ERP implementation project.

In this chapter, we will learn:

  • The usage and difference between chart panes and chart parts in the Role Tailored Client

  • Types of reports that are available in Dynamics NAV 2009

  • Printing reports

  • Creating reports without the development tools

  • What dimensions are and their setup

 

Role Centers


Dynamics NAV 2009 is all about the Role Tailored Client and the Role Tailored Client always opens with a Role Center page. A Role Center is like a dashboard. It is the starting page in Dynamics NAV 2009 and on it you will find the links to all the kinds of information you are looking for.

This is an example of the Role Center for the Order Processor role:

As you can see, the Role Center contains several panes of information. There's an activity pane that contains cues. Each of these cues represents a pile of documents and the height of the pile and the number on the pile represent the number of documents behind the pile. When you click on the pile, or cue icon as it is called, you are redirected to the corresponding page. On the Role Center page, you can have several other types of panes, but the ones that are interesting from the reporting point of view are the charts. On this Role Center, we can see two charts: the Open Sales Orders per Location chart and the Sales Orders per Date chart.

As a user, you can customize your Role Center. This way, you can, for example, visualize other charts and so customize your Role Center as a personal dashboard page.

For example, the next screenshot shows the Role Center for the President of a small business. Of course, this page can also be customized by the user, for example to include charts:

 

Charts


In the Role Tailored client there are two kinds of charts that can be added to any page by the user. To do this, the user can add chart parts and/or charts panes to a page. But there are some restrictions:

  • A chart pane i s only available on a List page

  • A chart part h as to be available in the design of the page

These charts that can be added to the Role Tailored Client at runtime give the user the perception of self service business intelligence in the Role Tailored Client.

Note

You can also add other types of charts to the RTC, for example via Control Addins. These types of charts are not covered in this chapter.

Chart panes

In any List page of the Role Tailored client a user can go to Customize This Page and add a chart pane to the page. When you do this, a chart pane is added that can be used to design a chart at runtime. The following screenshot presents these customization options. Enable the Chart Pane:

When you do this, a blank chart is added to the List page, below the list of fields. In this chart, the user can select one measure and one or two dimension fields:

The measure and dimension fields are all fields from the underlying table of the list page. Flowfields are also available to choose from:

  • The measure represents a numerical value you want to visualize in the chart.

  • The dimensions represent the axes you want to use to analyse the selected measure.

For example, in the Item List page, the user can create this kind of chart:

By selecting the Inventory flowfield as the measure and the Item Discount Group and Base Unit of Measure fields as dimensions, you have now visualized the inventory in a three dimensional bar chart. This provides you with an easy to understand graphical representation of the Inventory.

When you right-click on the chart, you can either copy or export it as a bitmap.

Limitations of chart panes

Chart panes are added by the user at runtime, and they are limited to the user only. Chart panes are a part of the user personalization features of the Role Tailored Client. Furthermore, after you stop and restart the Role Tailored Client, the chart pane is removed.

Chart panes can be demanding on performance. Chart panes are combinations of one measure field and one or two dimension fields, without filters. As you can probably imagine, when there are many records in the underlying table, running these chart panes can become very demanding on server resources. That's probably why they are removed after you restart the RTC.

Chart parts

A chart is added by using the Customize This Page feature in the RTC. Click on the Customize button at the top right of the window in the RTC as follows:

Now, you can add a chart to the page as follows:

When you then click on Customize Part, after selecting Blank Chart, you can select the chart you want to show in this chart part from the list of chart parts.

Now, where are these chart parts coming from? Actually, the chart parts are stored in the table: 2000000078 Chart.

You can create a new chart by importing it into this table. To help you in this process you can use the Classic Client (CC) or the Role Tailored Client (RTC):

  • To use the Classic Client, navigate in the CC to Administration/Application Setup/RoleTailored Client and click on Charts

  • To use the RTC, navigate in the RTC to Departments/Administration/Application Setup/RoleTailored Client and click on Charts

A window opens that you can use to import a new chart into the Chart table. Give the chart a unique ID, and a name. Then, you must import an XML file, which will contain the definition of the chart. Furthermore, you can enter none, one, or more companies, to limit the chart to become only available in certain companies of the database.

There are some limitations when creating a chart:

  • A chart must be based on exactly one table. You can't combine data from multiple tables.

  • You have two basic chart types: Point or Column.

  • Green is the only color currently supported.

  • Charts can use one of two operators: Sum or Count.

  • You can only apply static filters in charts, that is, you must type in (hardcode) the filter when you generate the chart.

A chart has a simple layout. You choose the table to base it on, then a field from that table to show along the X-axis, and, if needed, a filter on this table. Then, you select what data from the table to show on the Y-axis (also called a measure). A measure can be based on Sum or Count. You can display multiple measures in the same chart. Charts are defined by XML documents that are stored as BLOB fields. You can export a chart to an XML document, then modify the XML document, and import it back as a new chart.

The table, measures, and dimensions on which you base your chart are entered in the XML document.

This is an example of a chart definition:

<ChartDefinition xmlns="urn:schemas-microsoft-com:dynamics:NAV:MetaObjects" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" Type="Column">
   <Title>
      <Text ID="ENU">Items by location</Text>
   </Title>
   <Table ID="32">
      <Filters>
         <Filter>
            <Field Name="Item No." />
            <Value>70000|70001|70002|70003</Value>
         </Filter>
      </Filters>
   </Table>
   <XAxis ShowTitle="true">
      <Title>
         <Text ID="ENU">Location Code</Text>
      </Title>
      <Field Name="Location Code" />
   </XAxis>
   <YAxis ShowTitle="false">
      <Measures>
         <Measure Operator="Sum">
            <Field Name="Quantity" />
         </Measure>
      </Measures>
   </YAxis>
</ChartDefinition>

A chart automatically becomes a 3D chart by adding a Z-axis to the chart definition, for example like this:

   <ZAxis ShowTitle="false">
      <Field Name="Item No." />
   </ZAxis>

The presence of a Z-axis will automatically make the user able to rotate the chart:

Note

This XML file is an example coming from this website: http://blogs.msdn.com/b/nav/archive/2008/08/20/nav-2009-how-to-generate-charts-kpis.aspx.

On this blog, you can also download a Chart Generator tool. This tool contains objects that can make it easier to generate charts using the Classic Client and not by editing the XML file directly, which is very error sensitive. The tool contains the following objects:

Type ID Name

1 72000 Chart Generator

1 72001 Chart Generator Filter

1 72003 Chart Generator YAxis

2 72000 Chart Generator List

2 72001 Chart Filters

2 72003 YAxis List

2 72004 Chart Generator Card

5 72000 Chart Generator Mgt

Charts can be generated from form 72004 "Chart Generator Card". The Chart Generator tool is only supplied as an example of how such a tool could be made. It is completely un-supported and to be used at your own risk and responsibility.

A definition of the XML file can be found in an XSD file that you can find on the installation DVD of the Dynamics NAV application. It has been added on this DVD in version NAV 2009 R2. Before the R2 release, there was no .xsd file available.

This XSD file can be found in this location on the DVD: DVD\Documentation\Utility\ChartMetaData.xsd.

This .xsd schema describes the possible building blocks of the XML definition for a chart. A schema is a blueprint of an XML document that defines the elements, data types, and nesting structure of the page.

The following is a screenshot of the XSD file, when you open it in Visual Studio and display it in the XML Schema Explorer:

What can we conclude after investigating the .xsd file?

Well, first of all, some of the limitations we had before on charts are removed in NAV 2009 R2.

The ChartType is limited to Column, Point, and Line, as you can see here:

<xs:simpleType name="ChartType">
<xs:restriction base="xs:string">
<xs:enumeration value="Column" />
<xs:enumeration value="Point" />
<xs:enumeration value="Line" />
</xs:restriction>
</xs:simpleType>

The ChartMeasureOperator is now limited to the following operations:

  • None

  • Count

  • Sum

  • Min

  • Max

  • Avg

  • Relative

  • RelativePct

And the default is Sum, as you can see here:

<xs:simpleType name="ChartMeasureOperator">
<xs:restriction base="xs:string">
<xs:enumeration value="None" />
<xs:enumeration value="Count" />
<xs:enumeration value="Sum" />
<xs:enumeration value="Min" />
<xs:enumeration value="Max" />
<xs:enumeration value="Avg" />
<xs:enumeration value="Relative" />
<xs:enumeration value="RelativePct" />
</xs:restriction>
</xs:simpleType>

Compared to the previous release of Dynamics NAV, there are new options—Relative and RelativePct:

  • Relative can be used to compare values on a scale between 0 and 1.

  • RelativePct will do the same on a scale between 0 and 100%.

Secondly, the ChartTableDefinition now has an extra attribute: Key. This will I think determine the sort order when possible, but also have an impact on performance.

Chart security

A question you might ask yourself is: What about charts and security? Can I make sure only specific users will be able to add a chart to their Role Center page?

Well, the answer to this question is: no and yes. No, because there's no specific feature that was added to NAV 2009 to set up chart security. The way to set up security for these charts is by using security filters. As you might have seen, the existing charts all have a specific naming convention in their ID's. It is the Table Number followed by an index. This means that if you also follow this naming convention, you can use the chart ID as a security filter value when setting up roles and security in NAV 2009.

Record level security is a system that allows you to limit the access that a user has to the data in a table by specifying that the user only has permission to access certain records in the table. Record level security is implemented by applying security filters to the tables and the table data that a user has access to. You can specify, for example, that a user can only read the records that contain information about a particular customer and cannot access the records that contain information about any of the other customers.

Note

Record level security is only available in the SQL Server Option for Dynamics NAV. But because the RTC only works on a SQL Server database this should not be a problem.

Record level security filters do not support wildcards. This means that you cannot use * and ? in the filters. You can use the other symbols, delimiters, and operators, such as, <, >, |, &, .. and =.

The maximum length of a security filter is 250 characters, but all of the delimiters, symbols, and operators such as, <, >, |, &, .. and = also count as characters and can considerably reduce the length of the security filters that you can enter.

Furthermore, security filters are concatenated and therefore the sum of all the security filters applied to a user or a role cannot exceed 250 characters.

Chart performance

When you are a developer who designs the XML for a chart object, or when you are a user who's adding a chart to a page in the Role Tailored Client, you should consider the performance impact of the chart. In a chart object, although the data comes from one table, this table could contain thousands, tens of thousands, or even more records. Doing calculations on such huge amounts is normally not a problem for SQL Server, but for Dynamics NAV it might be.

That's why you should carefully design the chart. You can do this by selecting the appropriate key in the chart definition, and by supplying the appropriate filters.

The user should also be aware that the more charts he or she adds to a page, the bigger the impact on performance.

 

Reports


Besides charts, the Role Center also provides access to reports:

Depending on the Role Center you are on, the list will contain different reports. Also, on the other types of pages you will be able to access reports. For example, from the Customer Card page you can access reports in the menu at the top of the window:

When you go to the Departments in the RTC, you can go to any department, for example Sales & Marketing. When you click the Department, in the content window different categories will appear.

Click on Reports and Analysis: here, you will get an overview of all the reports that are related to Sales and Marketing. The reports are divided into different groups:

  • Analysis & Reporting

  • Sales

  • Order Processing

  • Marketing

  • Inventory & Pricing

These groups correspond to the groups defined in the Menu Item when the Sales and Marketing Menu was designed in the MenuSuite designer. In each group, you will find links to reports:

This kind of classification of reports is available in every section of the Departments suite in the RTC.

Reports have several purposes in Dynamics NAV:

  • Reports are used to print information from a database in a structured way. For example, you can create a report that lists all the customers and all the orders placed by each customer.

  • Every application document must be created as a report. For example, to print an invoice, you must create a report that is automatically filled out with the relevant information.

  • Reports can also be used for other tasks and not just for printing. A report can be used to automate many recurring tasks such as updating all the prices in an item list. This could be achieved by writing C/AL code in a Codeunit, but using a report is much easier because you can take advantage of the powerful data modeling facilities available in the report designer.

There are different types of reports available in Dynamics NAV.

List reports

A List report prints a list of records from a table. A List report usually contains a single data item. This data item represents the table being listed. The table is either a Master table or a Supplemental table. Each column contains a field from the table. Most data are printed from that table and sometimes brought in or calculated from other tables. The name of the List report is usually the name of the table followed by the word List.

The following are examples of List reports:

  • Report 101, Customer – List

  • Report 301, Vendor - List

Note

Some of the reports mentioned here might not be available in all localizations of Dynamics NAV 2009. For example, the Customer List report is not a part of the US version of Dynamics NAV.

Test reports

A test report is printed from a Journal Table. Its purpose is to test each Journal Line according to certain criteria that are used for posting so that all the errors can be found and fixed before posting. As soon as an error is found during posting, processing stops and the error must be fixed before posting can be tried again. Therefore, a test report is a good way to find errors in a journal that has multiple errors. The name of the Test report is usually the name of the corresponding Journal, followed by the word Test.

The following are examples of Test reports:

  • Report 2, General Journal – Test

  • Report 1005, Job Journal – Test

Posting reports

A Posting report prints from the Register table. It lists all the transactions (ledger entries) that are posted into that Register. This allows the accountant to detect exactly who posted the transaction, on what date, and in what sequence. This kind of report can be very useful for auditing. A Posting report can be printed as part of the Post and Print option in a Journal. The name of the Posting report is usually the name of either the Register table or the Master table of the corresponding ledger entries.

When you post a document in Dynamics NAV, for example a sales or purchase order, the system creates entries in the General Ledger. At the same time, specific ledger entries are created in separate tables, like for example the customer ledger entry, item ledger entry, resource ledger entry, VAT ledger entry, and so on. The specific ledger entry tables that are affected by a posting will depend on the type of information on the document. For example, if there were no items involved then no item ledger entry needs to be created.

When the posting routine posts a document, multiple entries may need to be created in the same specific ledger entry table. This is then stored and summarized in the register table. For example, in the item register table a record will be created by the posting routine with the starting number and ending number of the generated item ledger entries. Also, the user involved and the date are stored.

That way the register table can be used to analyze the transactions that took place concerning specific documents. The ledger entry tables will contain more detailed information and are used a lot when creating transactional reports.

The following are examples of Posting reports:

  • Report 3, G/L Register

  • Report 103, Customer Register

  • Report 1015, Job Register

Transaction reports

A Transaction report has the following characteristics:

  • It lists all the ledger entries for each record in the Ledger table.

  • It contains a subtotal for each Master table record, and a grand total for all tables printed.

  • It is used to view all transactions for a particular Master record.

  • It has no standard naming convention. A Transaction report usually has one or more data items, including the Master table and the corresponding Ledger table.

The following are examples of Transaction reports:

  • Report 4, Detail Trial Balance

  • Report 104, Customer – Detail Trial Bal.

  • Report 1007, Job – Transaction Detail

Document reports

A Document report prints a document. Document reports differ from most other reports, in that, many of the fields are not displayed in columns. An example of this type of report is the Sales Invoice. The header information in a Sales Invoice is printed as if filling out an invoice document. This header information is repeated at the top of each page, and no page has information from more than one header.

The lines for the invoice print more like an ordinary report in rows and columns. The lines correspond to the header on the same page, and lines from other invoices are not displayed on the same page.

The following are examples of Document reports:

  • Report 206, Sales—Invoice

  • Report 116, Statement

  • Report 405, Order

Other reports

Reports are more loosely defined than other application objects because they are frequently customized for a particular client. However, most reports consist of a tabular listing with records listed horizontally and each field displaying in its own column. Many times, there is a group heading or total to split the lines among various categories and to subtotal the lines by the categories.

The following are examples of other reports:

  • Report 113, Customer/Item Sales

  • Report 313, Vendor/Item Purchases

  • Report 1012, Jobs per Customer

 

Printing reports


How can reports be printed? What are the options to select a printer (printer selections) and which properties can be set when printing a report? What's the difference between printing reports from the Classic Client and from the Role Tailored Client, and how can you use the report viewer? These are all questions I will answer in this section.

To print a report:

  1. Open the report you want to print.

  2. Click File | Print, or click the Print button on the toolbar.

  3. You can also print a report by clicking Print in the report windows.

You can print the contents of any window that has a Print button. When you choose File, Print, the system opens a window where you can enter details about how the printing will be done.

In the uppermost field, you can see the name of the printer that is selected in the Windows Control Panel as your default printer. The printer selection will be valid for all of Microsoft Dynamics NAV until you choose a different one, or unless you associate a certain printer with a certain user and/or report in the Printer Selections window.

When you print a report and you have specified print and layout settings in the design of the report layout, you can also specify print and layout settings in the request page, in the printer properties of the selected printer, and in the print layout view of the report viewer at runtime. So then, which print settings will get priority?

You can specify page settings and page orientation for a printed report from the following UI locations:

  • The Report Properties dialog box on the client report definition (RDLC) report layout in Visual Studio

  • The Properties dialog box for the printer

  • The Print dialog box that is displayed immediately before you print

  • The Page Setup dialog box from the Print Preview page in the RoleTailored client

The page settings that are used for the printed report depend on the mode in which you print the report.

You can use the following modes to print a report from the RoleTailored client:

  • Run a report that has the UseReqForm property set to No, and therefore, does not display a request page

  • Print a report from the request page without previewing the report

  • Print a report from the Print Preview page

The printer that you use determines how to handle the case in which the actual paper size in the printer is different from the page size that is specified.

The following table describes how you access each of the dialog boxes in which you can specify page settings and orientation:

Print mode

To access the Report Properties dialog box

To access the Properties dialog box for the printer

To access the Print dialog box

To access the Page Setup dialog box

Run a report that does not have a request page

When you design the report layout in Visual Studio, on the Report menu, click Report Properties.

In Control Panel, double-click Printers. Right-click the selected printer and then click Properties.

Cannot access.

Cannot access.

Print a report from the request page without previewing

When you design the report layout in Visual Studio, on the Report menu, click Report Properties. 

In Control Panel, double-click Printers. Right-click the selected printer, and then click Properties.

On the request page, click Print.

Cannot access.

View a preview of a report

When you design the report layout in Visual Studio, on the Report menu, click Report Properties.

In Control Panel, double-click Printers. Right-click the selected printer and then click Properties.

In the Print Preview window, click the Print icon.

In the Print Preview window, click the Page Setup icon.

Determining print report settings

When you are printing a report without a request page, then the following table applies:

If

Then the printed report paper size and margins are determined by

And the printed report orientation is determined by

The settings in the Report Properties dialog box on the layout of the report specify a standard paper size.

The settings in the Report Properties dialog box on the RDLC layout.

The settings in the Properties dialog box for the printer.

The settings in the Report Properties dialog box on the layout of the report specify a custom paper size.

The settings in the Properties dialog box for the printer.

The settings in the Properties dialog box for the printer.

When you are printing a report from the request page, the following table applies:

If

Then the printed report paper size and margins are determined by

And the printed report orientation is determined by

The settings in the Report Properties dialog box on the layout of the report specify a standard paper size.

The settings in the Report Properties dialog box on the RDLC layout, but is overridden if you change the settings in the Print dialog box.

The settings in the Report Properties dialog box on the RDLC layout, but is overridden if you change the settings in the Print dialog box.

The settings in the Report Properties dialog box on the layout of the report specify a custom paper size.

The settings in the Properties dialog box for the printer, but is overridden if you change the settings in the Print dialog box.

The settings in the Report Properties dialog box on the RDLC layout, but is overridden if you change the settings in the Print dialog box.

Tip

The values in the Print dialog box are initially set to the values from the Report Properties dialog box on the RDLC report layout.

When you are printing a report from the print preview page, the following table applies:

If

Then the printed report paper size and margins are determined by

And the printed report orientation is determined by

The settings in the Report Properties dialog box on the layout of the report specify a standard paper size.

The settings in the Report Properties dialog box on the RDLC layout, but is overridden if you change the settings in the Page Setup dialog box.

The paper size settings in the Report Properties dialog box on the RDLC layout, but is overridden if you change the settings in the Page Setup dialog box.

The settings in the Report Properties dialog box on the layout of the report specify a custom paper size.

The settings in the Report Properties dialog box on the RDLC layout, but is overridden if you change the settings in the Page Setup dialog box.

The paper size settings in the Report Properties dialog box on the RDLC layout, but is overridden if you change the settings in the Page Setup dialog box.

Tip

The values in the Page Setup dialog box are initially set to the values from the Report Properties dialog box on the RDLC report layout.

Printer selection

When you print invoices, credit memos, and so on, it is important to choose the correct printer. You use the Printer Selection table to control this. In this table you can assign certain printers to certain users and/or reports so that the invoice report, for example, is always printed on printer X. You can also specify that only user Y will always print credit memos on printer X, for example.

Once you have set up the connections among users, reports and printers, the program will automatically use the information to direct future printouts.

You can set up reports so that they must be printed on a specific printer from the Role Tailored client. For example, you can print reports on a special company letterhead or on different paper sizes, or you can print reports on the default printer of a specified employee.

You can set different values to obtain different outputs. If you set a specific printer selection, then it takes precedence over a more general printer selection. For example, you can set a printer selection that has values in the User ID, Report ID, and Printer Name fields. This printer selection takes precedence over a printer selection that has blank entries in the User ID or Report ID fields.

The following table describes the combination of values to specify when you set up printer selections for a report:

To

Set these values

Print a report to a specific printer for all users

Specify values in the Report ID and Printer Name fields and leave the User ID field blank

Print all reports to a specific printer for a specific user

Specify values in the User ID and Printer Name fields and leave the Report ID field blank

Set the default printer for all reports

Specify a value in the Printer Name field and leave the User ID and Report ID fields blank

Print a specific report to a specific printer for a specific user

Specify values in all three fields

To set up printer selections for a report:

  1. In the Role Tailored client, in the navigation pane, click Departments, click Administration, click IT Administration, click General, and then click Printer Selections.

  2. In the Action Pane, click New to add a printer selection for a specific report.

  3. In the User ID field, select whether the printer selection applies to a specific user.

  4. In the Report ID field, select the ID of the report to print. The Report Name field is automatically populated with the report name.

  5. In the Printer Name field, select from the list of available printers. The list of available printers is generated from the printers that are currently installed. Different users on different computers may see different options.

  6. Click OK to close the page.

The specified report is now set up to print to the selected printer. You can always change the printer selection in the Print dialog box.

Tip

In Codeunit 1 Application Management there's a function FindPrinter that makes use of Printer Selection table:

Note

The printer selection functionality of Dynamics NAV was broken when Dynamics NAV was released. It was fixed again in version Dynamics NAV R2.

Report selections

When you work with the various documents for sales and purchases (orders, quotes, invoices, credit memos, and so on), you can print different reports. For example, you print a sales invoice when you click the Posting button on an invoice in a sales application area and select Post and Print.

The program has preselected which report will be printed when you click the Print button on various types of purchase and sales headers. For example, when you click the Print button on an order, the Order Confirmation report is automatically printed.

The Report Selections table contains the specification of which report will be printed in different situations. The Report Selection table also contains the report ID and report name for the report that will be printed when you work with a given document type.

You can, of course, choose to have the program print a different report than the preselected one. You can also add reports to the Report Selection table to have the program print more than one report per document type.

Note

The advantage of Report Selections is to have one location where you manage which reports will be executed when a user selects an action in the RTC. When you create a new report that will replace an existing document report, like for example the Sales Invoice, then you do not need to redesign the page objects linking to the old report. All you have to do is make one change in the Report Selection table.

 

Creating reports without development tools


A functional consultant, usually someone without technical or development skills, has the ability to set up dimensions in Dynamics NAV. With these dimensions, reports can be created and customized: Chart of Accounts Analysis, Account Schedules, Analysing Financial Information Using Dimensions, and Exporting Analysis Views. Some of these reporting tools provide functionality to export information to Microsoft Excel. You could say that this is Dynamics NAV 's built-in Business Intelligence tool.

What are dimensions?

Basically, a dimension is data added to an entry so that entries with similar characteristics can be grouped and easily retrieved for analysis purposes. Dimensions can be used throughout Microsoft Dynamics NAV on entries in journals, documents, ledgers, and budgets.

You must allocate adequate time when setting up the dimensions in Microsoft Dynamics NAV. The dimensions structure must be determined in combination with the chart of accounts and posting group structure. Additionally, using dimensions and simplifying the chart of accounts provides a better analysis of financial information. Getting this right in the beginning of an implementation is important because, changing the ways that dimensions are set up a long time after Go Live is not an easy job…

Technically, dimensions are values added to an entry to act as markers for the program, which allows entries with similar dimension values to be grouped for analysis purposes.

Metadata is data about your data, like attributes that you define and link to your data (entries). You can then use these dimensions for reporting purposes.

Many different types of entries in the program can have dimensions, including the following:

  • Master records

  • Transaction document headers and lines

  • Journal lines

  • Ledger entries

  • Posted documents and their lines

Each dimension can have an unlimited number of dimension values. For example, a dimension called Department can have dimension values of Sales, Administration, Purchasing, and so on. Users define and tailor these dimensions and values to their company's needs.

Tip

Dimension setup

Setting up dimensions in Dynamics NAV should be considered a job for experienced consultants. Just because Dynamics NAV allows you to create an unlimited amount of dimensions, that does not mean that you should. When you create dimensions without proper understanding of the business process and the reporting requirements of the customer, that will lead to Dynamics NAV collecting a massive amount of information and in the end it might bring confusion instead of a better understanding. The key is to keep it simple.

In Dynamics NAV, there are three types of dimensions:

  • Global

  • Shortcut

  • Budget

Global

Global dimensions are the most used and important dimensions because of their availability throughout Microsoft Dynamics NAV. Two dimensions can be specified as global dimensions.

Global dimensions can be used as filters for the following:

  • G/L entries

  • Reports

  • Account schedules

  • Batch jobs

Both global dimensions are also available as Shortcut Dimensions 1 and 2 for use on the following:

  • Document headers

  • Entry lines

When setting up dimensions in the G/L Setup, two of them can be global dimensions. These dimension types can be used throughout the program as a filter for G/L entries and on reports, account schedules, and batch jobs. Global dimensions are the only dimensions that are stored as part of the tables they describe. For instance, the G/L Entry table has two fields for the global dimensions.

Shortcut

Shortcut dimensions are used to enter dimensions and dimension values directly on the lines in the following:

  • Journals

  • Sales and purchase documents

There are a total of eight shortcut dimensions available. The first two shortcut dimensions are automatically defined as global dimensions. The remaining six shortcut dimensions are selected from the dimensions previously set up and can be changed regularly, as needed.

Use either of the following methods to assign dimensions to document entry lines:

  • Enter dimension values for a dimension directly on the line by using the Choose Column function to add shortcut dimensions to the lines.

  • Enter dimensions information in the Document Dimensions page by clicking the Actions menu (lightning bolt icon) on the Lines Fast Tab, pointing to Line, and then clicking Dimensions.

When you enter shortcut dimensions on journal and document lines, these lines have eight fields that are designated for dimensions. The first two are always the global dimensions, but the remaining six can be selected from those set up as shortcut dimensions in the G/L Setup. Dimensions that are not set up as shortcut dimensions can also be specified, but these must be set up in a separate Dimensions window for the header or line. Shortcut dimensions that are not set up as global dimensions are not stored as actual fields of the tables they describe. Instead, they are stored in a separate table.

Budget

For each budget, four dimensions can be defined, in addition to the two global dimensions. These budget-specific dimensions are called budget dimensions.

Budget dimensions are assigned to each budget from among the dimensions previously set up and can be used to:

  • Set filters on a budget

  • Ad d dimension information to budget entries

Where are dimensions set up?

Where dimensions are stored depends on the type of entry. The following table shows different tables that contain dimensions with the types of entries with which they are associated:

Dimension Table

Type of Entry

352 Default Dimension

Master records

355 Ledger Entry Dimension

Ledger entries

356 Journal Line Dimension

Journal lines

357 Document Dimension

Document headers and lines

358 Production DocumentDimension

Production orders, lines, and components

359 Posted Document Dimension

Posted document headers and lines

361 G/L Budget Dimension

Budget entries

Dimensions are set up under the Administration area in the Departments, under Administration\Application Setup\Financial Management\Dimensions. For each dimension, a code, a name, a code caption, and a filter caption are defined.

Global dimensions and shortcut dimensions are set up under General Ledger Setup, under the Dimensions Fast Tab. For the standard CRONUS International Ltd. demonstration database, the global dimensions have been set to the Department and Project dimensions, as shown here:

When you click on General Ledger Setup, the following window opens:

Global Dimensions 1 and 2, if set up in the system, will now link to these two dimensions. For example, the Global Dimension 1 Code field in the Customer table will now have a caption of Department Code, because this dimension is set up as the Global Dimension 1 Code in General Ledger Setup.

The aspects of defining and setting up dimensions include:

  • Dimension values

  • Dimension combinations

  • Default dimensions

  • Default dimension priority

Dimensions and dimension values

Dimensions are an integral aspect of Microsoft Dynamics NAV and are used throughout the product. The ability to set up the various types of dimensions and dimension values helps companies tailor their reporting needs to their business, and through the use of dimension defaults, combinations, and priorities, companies can retain control of how dimension entries are posted. Setting up and using dimensions properly initially assists in achieving better financial analysis results later.

Each dimension can have an unlimited series of dimension values. For example, a dimension called Department can have the dimension values Sales, Administration, and so on, as department names.

Dimensions and dimension values are user-defined and unlimited, which means dimensions are tailored for each company.

Tip

Too many dimensions cause performance issues

Be careful not to create too many dimensions and dimension values. The more dimensions that you allow and use, the more dimension information that will be stored in the database and this might have a very negative impact on performance. In most cases it is recommended not to use more than four or six dimensions.

To access the dimension setup, click on the link in the Departments suite as shown in the previous screenshot. If you do not find the link in the Departments suite then as an alternative you can type the name into the search box at the top right of the RTC. The following window will open:

Dimension values

A dimension value:

  • Is a subset within a dimension.

  • Can have an infinite number of values, with unique value codes within a dimension.

Dimensions and dimension values make it possible to:

  • Gain an accurate picture of a company's activities

  • Analyse relationships between dimensions and dimension values

Users can create a hierarchical relationship between dimension values so that Microsoft Dynamics NAV will consider some dimension values as subsets of another dimension value. This relationship is achieved using dimension value types.

Dimension values are set up in the Dimension Values page, located on the Dimensions page by clicking the Related Information menu, pointing to Dimension, and then clicking Dimension Values.

When you select the option Dimension Values, as shown in the screenshot above, then the following window opens:

The explanations of how to use the fields are in the following table:

Field

Description

Code

Unique code for the dimension value.

Name

Descriptive name for the dimension value.

Dimension Value Type

Determines the manner in which a dimension value is used when posted. The options are:

  • Standard

  • Heading

  • Total

  • Begin-Total

  • End-Total

Totalling

Identifies a dimension value interval or a list of dimension values, used to total the entries for the dimension values displayed in the field to give a total balance.

Blocked

Used to block the posting of journals containing specific dimension values.

The Dimension Value Type and Totalling fields together create the hierarchical relationship in dimension values:

Option

Description

Standard

Used for standard posting of dimension values

Heading

Heading for a group of dimension values

Total

Used to total a series of balances on dimension values that do not immediately precede the Total dimension value

Begin Total

Marker for the beginning of a series of dimension values to be totalled and ends with an End-Total dimension value type

End Total

Total of a series of dimension values that starts with the dimension value type Begin-Total

The Totalling field is completed based on the selection made in the Dimension Value Types field:

  • If the dimension value type is Standard, Heading, or Begin-Total, the Totalling field must be blank

  • If Total is selected, the Totalling field must be manually populated to indicate which dimension values will be totalled

  • If End-Total is selected, the Totalling field is automatically populated when the Indent Dimension Values function is run

The Indent Dimension Values function:

  • Indents all dimension values between a Begin-Total and the matching End-Total by one level

  • Totals all dimension values within the same range and updates the Totalling field for each End-Total

You can access the Indent Dimension Values function from the Dimension Values page by clicking the Actions menu, pointing to Functions, and then clicking Indent Dimension Values.

Dimension combinations

Dimension combinations provide the ability to prevent (block) particular dimensions from being combined on a journal or in a document, and under what circumstances. The blocking may be, for example, that a specific project team cannot post certain expense types or that a certain item cannot be sold in a particular area.

In addition, the use of a particular dimension combination can be restricted, depending on which dimension value combination is being used for the two dimensions.

This page displays a matrix of all combinations of dimensions created in Microsoft Dynamics NAV. The dimensions displayed in the rows of the matrix are also represented as dimension columns.

The columns in the Dimension Combinations Matrix are called combination restriction fields. The combinations for these fields are accessed by clicking the field, and include the options shown in the following table:

Option

Description

No Limitations

The dimension combination is always allowed on entries. This is the default setting for dimension combinations.

Limited

The dimension combination is only allowed in certain circumstances, depending on which dimension values are selected when you are creating an entry.

Blocked

The dimension combination is excluded from use on entries.

Tip

If the Limited option is selected, the restricted dimension value combinations must be specified. Dimension value combinations are only applicable to the Limited option.

Dimension value combinations are set up by drilling down in the relevant restriction field on the Dimension Combinations Matrix to define restrictions in the Dimension Value Combinations Matrix page.

When you click on Drilldown, the following window opens:

The Dimension Value Combinations Matrix page is a matrix of all combinations of dimension values for a particular dimension combination. The rows represent the dimension values of the dimension selected in the rows of the Dimension Combinations Matrix page. The columns represent the dimension values of the dimension selected in the column of the Dimension Combinations Matrix page. The dimension value combination restrictions are set up by clicking the relevant field and selecting Blocked. If Blocked is not selected, the combinations are allowed for these dimension values.

Default dimensions

You can minimize the data entry involved in recording dimension information by assigning default dimensions to accounts, customers, vendors, items, and other objects in Microsoft Dynamics NAV. Once you have set up default dimensions for an account, those dimensions and related values will automatically be filled in whenever the account is used, but the dimension values can still be changed if required.

Users can specify default dimensions in various ways:

  • For an individual account on the relevant account card

  • For a particular group of accounts within an account type by using the Default Dimension—Multiple menu item on an account list, such as the Vendor List

  • For an entire account type, such as the customer account type, in the Account Type Default Dim. Page

Account type default dimensions can be used, for example, to ensure that a company-defined dimension called Customer Group is always used for customer accounts. Default dimensions can suggest conflicting dimension values. For example, if a user has set up different default dimensions for two accounts used on a journal line, different dimension values might be recommended, causing a conflict. Users can set default dimension priorities that will resolve these potential conflicts. In cases where conflicting default dimension values are of the same account type, the last entered account is favored.

View posted dimension information

You can access all the dimension information relating to a specific entry, improving traceability and inquiry response. When a journal or document containing global dimension information is posted, the dimension information is stored in two areas:

  • Directly in ledger entries

  • In a separate table

Shortcut dimensions are stored in a separate table, not in ledger entries.

The posted global dimensions and other dimensions are also recorded in a separate table, depending on whether they relate to a posted document or ledger entry.

Storing posted dimensions in a separate table allows for an unlimited number of dimensions. If dimensions are only recorded on the entries, for each new dimension created, a new field must be added to every table that contains the entries. This cannot be done without having a license with solution developer permissions.

Remember that all posted dimension values will be stored in this one table. Global dimensions are also available in the ledger entry tables.

Analysing financial information using dimensions

Account Schedule analysis capabilities are primarily based on the G/L accounts and their structure in the Chart of Accounts. The analysis view is a means of viewing data from the general ledger for particular output needs based on criteria specified within a business. For an analysis view, G/L entries are grouped by criteria such as:

  • G/L accounts

  • Period

  • Business units

  • Up to four dimensions

In other words, if a G/L entry has been posted to a particular account with one of the four dimensions selected for an analysis view, this G/L entry information will be included in the analysis view as an analysis view entry. You can include detailed dimension information in an analysis view using the Analysis View Filter.

You can for example include G/L budget entries in an analysis view to compare actual figures with expectations. The analysis view can be continually updated with new G/L entries using the following methods:

  • Clicking Update on a specific Analysis View Card

  • Running the Update Analysis Views batch job

  • Placing a check mark in the Update on posting check box on the Analysis View Card

Use the analysis view in the Analysis by Dimensions page, where it is possible to:

  • Filter amounts

  • Manipulate the presentation of amounts

  • Compare actual amounts with budgeted amounts

Tip

Automatically updating an Analysis View each time a G/L entry is posted might create a performance issue. It is actually recommended to turn off automatic updates to prevent issues with performance.

Budget entries included in an analysis view can only be updated using the first two methods.

The Analysis View Card contains the criteria for creating the Analysis View entries for the Analysis by Dimensions page.

In the following table, there's a brief explanation of the fields in this window:

Field

Description

Code and Name

A unique identifier and description of the analysis view.

G/L Account Filter

The G/L accounts that are included in an analysis view. Setting filters also specifies that only entries posted to the filter accounts are included when an analysis view is updated.

Date Compression

By using date compression, the level of detail for an analysis view is determined. For example, to analyse financial information for an analysis view on a monthly basis, use the Month date compression to sum all entries in a given month and create one single entry for the entire month.

Starting Date

All G/L entries posted on or after the entered Starting Date will be compressed to the level selected in the Date Compression field and included in the analysis view. The Posting Date for the compressed entries will be the first date of the related period.

For example, if compressing by month with a Starting Date of 1/1/10, the compressed entries for each month will have a Posting Date of 1/1/10, 2/1/10, 3/1/10, and so on.

All entries prior to the Starting Date are compressed into one entry for each G/L account, for each dimension combination. The Posting Date of these prior entries is the day before the Starting Date.

Last Date Updated

Displays the date on which the analysis view was last updated.

Last Entry No.

Contains the number of the last G/L entry posted prior to when you update the analysis view. If G/L entries have been posted since the analysis view was last updated, the analysis view will not include these entries.

Last Budget EntryNo.

Contains the number of the last budget entry entered prior to when you update the analysis view. If additional budget entries have been entered since the analysis view was last updated, the analysis view will not be up-to-date.

Update on Posting

If selected, Microsoft Dynamics NAV automatically updates the analysis view every time an entry is posted.

Include Budgets

If selected, analysis view budget entries are included when updating an analysis view. Updating both analysis view entries and analysis view budget entries simultaneously ensures that up-to-date information is used in the comparison of actual and budgeted figures.

Blocked

If selected, the analysis view cannot be updated. Neither the Update on Posting function nor the Update Analysis View batch job can be used to update an analysis view while it is blocked.

Code and Name

A unique identifier and description of the analysis view.

The Dimensions Fast Tab contains the four dimensions that can be used as filters in the Analysis by Dimensions page. These dimensions provide the ability to investigate and monitor relationships between entries and the dimension information attached to them.

To create an analysis view, follow these steps:

  1. In the navigation pane, click the Administration department.

  2. Click Application Setup and then click Financial Management.

  3. On the Financial Management page, click Dimensions, and then click Analysis Views.

  4. Click New to insert an analysis view.

  5. In the Code field, type a unique identifier for the analysis view.

  6. In the Name field, type a short description.

  7. In the G/L Account Filter field, enter the accounts to be included in the analysis view.

  8. If compressing analysis view entries, do the following:

    • In the Date Compression field, enter the period to use

    • In the Starting Date field, enter the date on or after which posted entries will be compressed

  9. Select the Include Budgets check box to update budget entries when the analysis view is updated.

  10. Expand the Dimensions Fast Tab.

  11. Enter the dimensions to be included in the analysis view.

Tip

Attention

If an analysis view is deleted, Microsoft Dynamics NAV deletes all associated analysis view entries.

You can further filter the G/L entries used to make the Analysis View entries using dimension value filters. Setting a dimension value filter establishes that only entries with the dimension values set in the filter are to be included in an analysis view.

For example, an analysis view is set up for the purposes of analysing the sales activity of particular departments. The Analysis View Filter is then used to specify that only entries with the company-defined dimension called Department and with the specified dimension values can be included in that analysis view.

To add dimension value filters, follow these steps:

  1. On the Analysis View Card page, click the Related Information menu, go to Analysis, and then click Filter.

  2. In the Dimension Code field, enter the dimension to filter.

  3. In the Dimension Value Filter field, enter the dimension value to be included in the analysis view.

  4. Repeat steps 2-3 for additional dimension value filters.

  5. Click OK to close the Analysis View Filter page.

  6. In the Action Pane, click Update.

  7. Click Yes to update the Analysis View.

  8. Click OK to close the Analysis View Card page.

In the Analysis by Dimensions Matrix page, you can view and analyse amounts derived from analysis views that they have created. You can analyse entries from various perspectives by selecting dimensions on each axis in the matrix. Entries can also be filtered to create a highly specific picture of a company's activities.

You can access the Analysis by Dimensions page on the General Ledger page by clicking Analysis by Dimensions under Analysis & Reporting, selecting the relevant analysis view, and clicking Edit Analysis Update.

Dimension-based reports

It is not possible to print an analysis view directly from the Analysis by Dimensions page. However, dimension information can be printed by combining analysis view entries with Account Schedule column layouts in the reports called Dimensions—Total and Dimensions—Detail.

Analysis views can be exported to Excel. When running an analysis view, you can use the menu at the top of the window, as you can see in the next screenshot:

Doing this exports the data into Excel and from there you can print it or use it for further analysis.

 

Summary


Microsoft Dynamics NAV 2009 provides an intuitive and customizable user interface called the Role Tailored client, which developers, partners, administrators, and super users can customize to support the job functions of different work roles in an organization.

The Role Center is the user interface in Microsoft Dynamics NAV 2009 and is like a homepage to the system. It displays the specific tasks, activities, and information that each role needs to do their job, providing them with an overview of what they've done and what is next. It enables them to focus on their tasks and organize their time.

One of the key advantages of Role Center is that it's so easy to personalize. Parts can be added, removed, or customized by users to show the information or view that suits individual needs, without the need for a programmer.

The various types of dimensions available in Microsoft Dynamics NAV (global, shortcut, budget, and default) provide companies with an effective method of analyzing information. Through the use of analysis views, companies can access financial and budget information based on specific G/L criteria. Together, dimensions and analysis views allow companies to analyze trends and compare various characteristics across a range of entries.

We have seen that Dynamics NAV comes with a lot of built-in functionality that provides different types of reports and analysis tools without having to do any customizations or development. Setting up dimensions and dimension-based reports requires some expertise, but doing it right will result in a lot of added value. You can say that the dimension functionality of Dynamics NAV is its own internal business intelligence system. Having it in place provides you with the added value of being able to create more types of analyses and reports.

Besides dimensions, Dynamics NAV comes with many reports already available, out of the box, tailored to the localization that you are using. Reports can be divided into types, to make it easier to understand why and when to use them.

The charts parts and chart panes of the Role Tailored Client provide you with an extra added value compared with other products or previous versions of Dynamics NAV. Furthermore, you can customize every page and data visualization has been built into the application, making it easier to see and understand the information you require on a daily basis, so you can make informed business decisions with confidence.

When printing a report there are a couple of settings that come into play to determine how the report will be printed and understanding that can avoid a lot of frustration.

In the next chapter, we will learn to create a simple report using the Report Designer.

About the Author

  • Steven Renders

    Steven Renders is a Microsoft Certified Trainer with skills that span the business and technical domains. He specializes in Microsoft Dynamics NAV and Microsoft SQL Server. He has more than 15 years of both business and technical experience. He provides training and consultancy that focuses on Microsoft Dynamics NAV, Microsoft SQL Server, business intelligence solutions, Microsoft SQL Server Reporting Services, and database performance tuning.

    Furthermore, he is also an expert in Microsoft Dynamics NAV, on which he has already delivered many training sessions. He was also the author of the official Microsoft training material on Dynamics NAV reporting, development, upgrading, and SQL Server performance tuning.

    He is the author of the books, Microsoft Dynamics NAV 2015 Professional Reporting and Microsoft Dynamics NAV 2009: Professional Reporting and also a reviewer of the books, Programming Microsoft Dynamics NAV 2009, Programming Microsoft Dynamics® NAV 2013, and Implementing Microsoft Dynamics NAV 2013.

    He has also presented at various Microsoft MSDN and TechNet conferences, NAV Techdays, communities, events, and the MCT Summit.

    In 2011, he started his own company, think about IT, which specializes in training and consultancy, helping companies learn, implement, understand, and solve complex business requirements related to IT, both in Belgium and abroad.

    His specialties are Microsoft Dynamics NAV, Microsoft SQL Server, Business Intelligence & Reporting, and Power BI.

    You can contact him at [email protected] and through his website (www.thinkaboutit.be). You can also view his LinkedIn profile at https://www.linkedin.com/in/stevenrenders/, and his Twitter handle is @srenders.

    Browse publications by this author
Book Title
Unlock this book and the full library for FREE
Start free trial