Microsoft Dynamics NAV 2009: Professional Reporting: Discover all the tips and tricks for Dynamics NAV report building

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:

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.

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):

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 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:

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:

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:

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.

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.

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.

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:

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:

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:

A Transaction report has the following characteristics:

The following are examples of Transaction 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:

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:

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

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

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

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 up printer selections for a report:

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.

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.

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:

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.

In Dynamics NAV, there are three types of dimensions:

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:

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:

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.

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.

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:

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.

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:

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:

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

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:

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:

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:

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.

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.