NAV 2009 - The structure of reports in VS report designer

One thing is to make a report look pretty. But before you can do that, you have to make it work first. This post is about how to get the data structure right when designing reports in VS report designer for the new client in NAV 2009.

If you are completely new to the new environment, then take a look at this post first:

NAV 2009 - Report Designer - Introduction to the new environment

This post also assumes that you are already familiar with designing reports in the classic report designer.

Start with a classic report

The new environment in Visual Studio (VS) report designer relies completely on the data structure you define in the classic report designer. So you must first design data-items and link them in the right way. You must also use the classic report designer to decide what to display on the report – but not where to display it. Only data that you add to sections in the classic report designer is available in VS report designer. You can add fields and other data wherever you like in sections – as long as the data is there. Then you use VS report designer to place the data where you want it. 

Sections in VS report designer - 1 Header, 1 Body and 1 Footer

A report in Visual Studio always has exactly 1 Body section.  You cannot add more than 1. Optionally it may have 1 Header and 1 Footer. You cannot add further headers or footers.
Enable Header and Footer sections by right clicking in a blank area, then enable / disable header and Footer section:

Warning: If you disable the Header Footer  the section with all its content will be deleted.
When the report runs, it will run the Header section once, then the Body section once, and then the Footer section once. It will not – like in the NAV report designer – run the Body section for each iteration / record. Looping through records is done by creating a table in the body section. So understanding tables is really important to getting the right data in your report.

Tables
This is there we loop through the data in a report. To create a table, insert it from the toolbox. As default it will have a Header, Detail and Footer section.
To enter something in a cell, either right click it, select Properties, and specify Value. Or drag and drop items from your Data set. Remember that your data set (“Website Data Sources”) consists of what you have added to the sections in the classic report designer.

Table Sections:
Header:
This is a typical place to put captions. If you drag an item containing text from your data set into a header, the environment automatically adds “First” to it. “First” means that it will show the first value in the data set, as opposed to looping through each record in the data set.
Detail:
This is where you have the iteration of the data in the report.
Footer:
This is a typical place to put totals. If you drag an item containing a numerical value from your data set into a footer, the environment automatically adds “Sum” to it, and the report will print the total from the report.

Example / exercise – simple report with one data item

The following example shows how to use the basic sections in a table in a report with 1 data item.

1)     Create a new report with one data item Customer. On Sections, add Customer No. Name and Balance (LCY):

Unless you plan to run this report from the classic client it doesn’t matter where or how you add fields to the sections, or if you add header / footer  or any other types of sections. You only add fields to the section because then they will then become part of the data source in VS report designer.

2)     Click on View -> Layout to open VS report designer. Add a table from the toolbox. Look at “Website Data Sources” to see the fields available in the data set. This is the fields that you put on the sections. So if you want to add fields here, then go back to the sections, add them there, and next time you open the layout those fields will be there.

3)     Drag and drop Caption fields into the Header, Normal fields into the Detail, and Balance_LCY_ into one of the Footer-cells:

That’s all you have to do to create a simple report. So now let’s add some more structure to the report.

Multiple data items:
This is where it can easily go wrong if you forget a step or two. First, we need to go back to the “old” environment and see how this affects your data structure.
The data structure and links are decided by the data items on the report and the fields you put on the sections (View -> Sections). About adding fields to sections, there are two things to be aware of:
1)    To include something in the data set, it must be on the sections
2)    If you add sections for another data item, then this affects the data set and the way the report runs
If you created the report from Example 1 above, then add the following:
Add a new data item “Cust. Ledger Entry”. Indent and link it to the Customer data item as you would normally. If you now run the report, it will run just as before. Then go to sections and add one or more fields from “Cust. Ledger Entry”. Run the report again, and this time you will see a difference. When you added fields to the sections, you also expanded the data set. The table in the layout will now print a row for each record, and because you have added data to the data set, it will print one row for each. You need to group this data in order for it to make sense. This is where it can get a bit tricky.

Grouping

The table in VS report designer will just print all the data in the data set. Unless you group the data correctly, the result will not make any sense. To insert a group, highlight a row in your table, right click it, then “Insert Group”:

 

For a group you must specify the Expression, which tells the table what to group by. When creating a group it’s for the indented table – in this case “Cust. Ledger Entry”. So we want to group it by Customer No. From the Customer table. So select that as the expression:

 

Also, just to simplify the report, un-tick “include group footer”.
This completes the structure of the table. But the report will print one group header and group fields, so leaving these empty will mean a lot of blank lines. To complete this report, drag fields into both the new group header and into details.

This is where the report can easily get wrong if you miss a small detail, and where it may be necessary to try a few times before the report looks the way it should. The result of the report is a combination of data items, sections, and the table you created in VS report designer. So if something is not right, the problem may be any where in those areas. One tip: If the report prints a lot of blank lines, then check if you have any blank rows in the table. Here is a simple table layout that prints customer s and Cust. Ledger Entries:

 

 

Table elements

As described in the previous sections, you have to use the right table elements in the right places. This is what the icons of the table elements mean, and how and where to use them:

   Table header
Will print once at the top of the table.

   Group header
This is for when you have an indented data item. The header will show data from the top-level data item, for example Customer No.

   Table Detail
This corresponds to a body-section, either from a main table or from an indented table.

    Group Footer
This is also for indented data items. How this will print, depends on how you have set up the group in the table.

   Table Footer
Will print once, at the end of the table

 

 

Lars Lohndorf-Larsen

Microsoft Dynamics UK

Microsoft Customer Service and Support (CSS) EMEA

These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.

Comments

• Anonymous
  January 08, 2009
  In Microsoft Dynamics NAV 2009 we released the possibility to create reports with SSRS layout. SSRS(SQL