Environment Microsoft Dynamics Nav 2015 Professional Reporting Pdf


Tuesday, May 7, 2019

Microsoft Dynamics NAV Professional Reporting - Sample Chapter - Free download as PDF File .pdf), Text File .txt) or read online for free. Chapter No. Microsoft Dynamics NAV is a multi-lingual, multi-currency business management solution that organizations use to manage their accounts. Read "Microsoft Dynamics NAV Professional Reporting" by Steven Renders available from Rakuten Kobo. Sign up today and get $5 off your first purchase.

Microsoft Dynamics Nav 2015 Professional Reporting Pdf

Language:English, Spanish, Indonesian
Country:Sierra Leone
Genre:Science & Research
Published (Last):29.12.2015
ePub File Size:27.38 MB
PDF File Size:13.57 MB
Distribution:Free* [*Regsitration Required]
Uploaded by: BUSTER

Discover tips and trick for Dynamics NAV report building About This Book Create and customize reports in Dynamics NAV using RDLC, Word, Power BI. The goal of this book is to introduce and explain the reporting capabilities of Dynamics NAV in detail. Starting from the beginning, this book will introduce you to. I'm very excited to announce the coming release of my new book: Microsoft Dynamics NAV Professional Reporting. Because of the.

Only then will you truly understand if what you developed is ready or not.

Test it on different clients: Windows, web, and tablet. Using the example of the inventory by location report, a test verifies if the inventory is correct and corresponds to the inventory on the item card.

Are there any locations or items missing from the report? You use the report dataset designer to create the dataset, which opens from the object designer in the Dynamics NAV Development Environment. You can choose to use either Visual Studio or Report Builder to create the layout. What do I use to develop the data model? This is how you open it: In the development environment, on the Tools menu, choose Object Designer.

In Object Designer, choose Report, and then choose New. How do I create the report layout? There are two types of report layouts: The RDLC layout is the most flexible. By this, I mean that, from a technical point of view, you have the ability to use expressions to determine how and when data should be visualized. The Word layout is restrictive and imposes limitations on the way you create the dataset.

Built-in and custom layouts A report can have multiple layouts. These are the built-in layouts, because they are a part of the report object and are stored inside the report object. You can see this when you export the report object to a text file, as the RDLC and Word layout are then included.

A user can also create a custom layout with the Dynamics NAV application, which is based on the built-in layout. The idea is that a user can customize the built-in layouts according to their needs. In this way, a user can switch between different layouts for the same report. These custom layouts are not stored in the report object, they are stored in a separate table: In a multi-tenant Microsoft Dynamics NAV deployment, the built-in report layouts are stored in the application database because they are part of the report objects.

Therefore, built-in report layouts are available to all tenants. Custom report layouts are stored in the business data database, therefore they are specific to the tenant. This enables you to create separate report layouts for each tenant. I recommend not creating too many report layouts. It is very convenient to have multiple layouts but it can get confusing and difficult to manage.

Building the data model The data model of a report is designed in the report dataset designer and will become the dataset for the layout. The runtime dataset is flat and is generated from the data items tables. The layout will be rendered on top of the dataset. Flat means that the dataset consists of rows and columns. For example, when you combine two tables that have a one-to-many relationship, such as the sales header and sales line, the dataset will consist of the columns from both tables and a row for every line, in which the columns from the header table are repeated.

So, at runtime, the dataset looks different when compared to the definition of the dataset in the report dataset designer, where data items are indented. I will explain in more detail later in this chapter how the dataset is flattened and columns from different data items are combined. The following screenshot is the dataset of the Report Mini Sales - Order.

As you can see, it contains many tables and columns:. As you can see, the dataset at runtime consists of rows and columns. There is a column for every column in the dataset designer, and a row for every record from the data items.

This is referred to as a flat dataset, as against the data items, which are indented. When you select About This Report, the system tells you that you have to run the report again to see the data. Actually, you have to enable this feature before you can see the dataset. This means, of course, that you have to run your report twice, just to be able to see the contents of the dataset. A user can then forward this information to the helpdesk or whoever is providing support.

If you click on the About This Report feature in the request page of the report, then it is also enabled. After that, at runtime, you can click on About This Report and the dataset will contain data. In this way, you don't have to run the report twice to be able to see the dataset. Understanding the report dataset designer In this section, I will explain how to use the report dataset designer to create a data model for your report.

Building the dataset Building the dataset is an important process because it determines where and how the data becomes available in the runtime dataset and so will have an impact on how you design the layout. Data items and columns fields, variables, and expressions You design the dataset in the Report Dataset Designer, based on data items and columns. Data items link to tables in the database and columns link to fields or expressions:. A column can be a field in a table, but it can also be an expression, a variable or a text constant.

Let's start with an example and create an item list report. The idea is to display a list of items, so I will use the Item table as my data item and I will add the number, description and inventory as columns. The Field Menu allows you to select any of the data item fields and add them to the report without having to type in the Name of the field.

In the Field Menu window, select one or more fields that you want to add to the report dataset. Select multiple fields by holding down the Shift key or the Ctrl key. Choose the OK button to add the selected fields to the dataset. You will notice that, when using the Field Menu, the field name consists of the field name, an underscore, and the data item name. After you have selected the data items and columns you want to add in the dataset, open the properties window.

Here you can set the data item and field properties. When you select a data item or a field, the property window displays the properties of the selected data item or field. When you go to the last line in the dataset designer, referred to as the first empty line, the property window displays the report properties. The following image shows the properties of a data item and of a field:. You can also include a variable in the report dataset. Including captions and labels You are not only going to display data in the report layout but also field names and textual information.

These names and text will be displayed in the user's own language, since Dynamics NAV is a multilanguage application. You can therefore use captions and labels when you design the dataset. Captions and labels are sent as parameters to the report layout. They are not actually included in the dataset, because their value is the same for every record and we don't want this information to repeat because that would increase the size of the dataset unnecessarily. To include a caption: Select the row in the dataset.

Enter Yes in the IncludeCaption property. Alternatively, use the IncludeCaption checkbox in the report dataset designer:. If you select a row that contains an expression or a variable, then you will get the following error when you activate the IncludeCaption property for that row:. You can only use the IncludeCaption property on table fields. For other fields, you can define a label. To include a label: Then, in the Label properties, use the CaptionML property to translate the label into other languages.

Alternatively, you can use the assist-edit button to open the label Multilanguage Editor:. How to see the properties To see the properties of a column or data item, you have to first select it.

Make sure it is selected by clicking on it with your mouse. Then, you can click on the properties button at the top of the screen, or press Shift and the F4 function key. Now, the property window opens and displays the appropriate properties. In some cases, for example in document reports, you may want the captions to be in the language of the recipient, not in the language of the user. In previous versions of Dynamics NAV, captions were added by default in the dataset, so using IncludeCaption and labels is a performance improvement.

How is the dataset flattened? At runtime, a report dataset consists of rows and columns. If you only have one data item, then the columns in the dataset designer become the columns of the runtime dataset and the rows from the data item or table become the rows in the dataset. In this situation, the runtime dataset looks the same as when you simply run the table. If you combine multiple data items, then the data items at design time can be indented, or not, and this results in a different dataset at runtime.

Since the runtime dataset is two dimensional, consisting of rows and columns, information is repeated over multiple rows. In this section, I will explain and demonstrate how the runtime, flat, dataset is generated, using indented or non-indented data items. Using multiple data items in a report is a common pattern in most reports and understanding how the multi-data-item dataset at design time is converted into the flat two-dimensional dataset at runtime is very important.

Understanding this process is, in my opinion, the most important part of RDLC report development, because it determines how you build the layout and which filters you need to apply in the RDLC layout.

Unrelated tables or multiple data items, without indentation As an example, let's start with a dataset that consists of two data items, Vendor and Customer:. When we run this report and display the dataset with the About This Report feature, it shows this:. In the dataset there is a column for every column in the report dataset designer: When the report processes the data items it starts with the first one on top , which is Vendor.

For all vendors, it fetches their No and Name and adds it to the dataset. The customer columns remain empty. Then, after the Vendor data item has been completely processed, the system starts with the Customer data item and does the same. The result sets of the two data items then follow each other in the dataset, stuck together. Now imagine that I need to create a request page for this report to include an option to display details.

Then, I would create a variable HideDetails, add it to the request page, and also add it to the dataset. If I added this variable to the dataset as an extra column, then we would have a problem.

Are we going to add it to the Customer or Vendor data item? If we add it to the customer data item, it will be available at runtime, but not in the vendor rows. Now, as this is a variable that contains a constant value, we only need it once and there's no point in repeating its value on every row, because that would increase the size of the dataset and so decrease performance.

The solution is to include an extra data item that will only add one row to the dataset. To do this, you can use the integer table, as shown in the following screenshot:. The integer data item adds one row at the end of the dataset and this contains the value of the HideDetails variable. Use the following expression to retrieve this value in the layout of the report: Unless you add the integer data item as the first data item in the report dataset designer, the row is added to the beginning and the expression becomes this: Remember that we have an extra row in the dataset containing our variable HideDetails.

This row should be filtered out in the tables in the layout that display the Vendors and Customers. An example of this report is available in the object: Packt - CH Using an integer data item, and filtering it to add one or more rows in the dataset, is a common pattern in report design. In that way, you can set it at runtime, depending on an option in the request page. In document reports, this is usually how the NoOfCopies option is implemented.

I will come back to this pattern in the Chapter 5, Document Reports. Related tables or multiple data items with indentation Let's create a report with a dataset that contains two data items as an example: Customer and Customer Ledger Entry.

In document reports, this pattern is applied a lot, because, in a document report, we have many data items that need to be visualized in different tables or sections of the report.

The Customer Ledger Entry needs to be linked to the Customer, so for each customer we can see their individual entries. Linking data items is done by indenting them in the report dataset designer and then, in the indented data item setting, the link fields in the property DataItemLink, as follows:.

Customers that don't have ledger entries are shown, but the ledger entry columns are empty. Customers that have ledger entries are shown and, for every ledger entry, there's a row in the resulting dataset. As you can see, if a customer has multiple ledger entries, then, for every ledger, a row is added to the dataset and the columns for the customers are repeated on each of these rows.

This is called the flattening of the dataset, the columns of the parent record are repeated for every child record.

To filter out customers that don't have ledger entries, you can use the PrintOnlyIfDetail property. You need to set this on the top data item, in this example, the Customer:. The property PrintOnlyIfDetails specifies whether to print data in a report for the parent data item when the child data item does not generate any output. If there are more than two data items, then the report iterates through each parent child relationship in the same way.

Packt - CH If you are going to create a layout for this dataset and you want to see the ledger entries per customer, you do this by creating a group in the table and grouping on Customer No.

This is a very common design for the datasets in Dynamics NAV reports. It is used with header and line tables, master and ledger tables, and also in document reports.

If you omit the DataItemLink and DataItemLinkReference between the Customer and Customer Ledger Entry table, which effectively disconnects the two tables, then the resulting dataset is much larger, since it includes all possible combinations of headers and lines, with absolutely no regard to their possible table relations.

When using multiple data items with indentation, you can also apply the technique of including an integer data item. In that case, investigate the resulting dataset before you create the report layout because, depending on the result, you might want to move the integer data item to the top or bottom to get a better dataset. These triggers are fired when specific events happen and allow you, as a developer, to have code executed at those moments.

In this section, I will explain the different triggers and in what order they are fired. What happens when a report runs? When you run any report, the OnInitReport trigger is called first.

This trigger performs any processing that is necessary before the report is run, and so before any data is read, and before the request page is shown to the user.

Next, the request page for the report is run, if it is defined. Here, you select the options that you want for this report. If you decide to continue, the OnPreReport trigger is called. At this point, no data has yet been processed. The Company Information table is usually queried in this trigger to retrieve company information like the name, VAT number, company logo, and so on. When the OnPreReport trigger has been executed, the first data item is processed.

When the first data item has been processed, the next data item, if there is any, is processed in the same way.

When there are no more data items, the OnPostReport trigger is called to do any necessary post-processing. The report trigger sequence The OnPreDataItem trigger is executed before any data is retrieved from the database.

This trigger is used to filter the data item dynamically. The OnPostDataItem trigger is executed after the data item has been processed, meaning after all records have been fetched from the table.

This trigger usually contains no code, or just code to clean up variables or filters. The OnAfterGetRecord trigger is executed after a record is fetched from the table, but before it is added to the dataset. This is because the OnAfterGetRecord trigger will execute for every single record that is retrieved from the database.

Microsoft Dynamics NAV 2015: Professional Reporting

What is a ProcessingOnly report? Processing table data is not limited to processing-only reports. Reports that print can also change records. This section applies to those reports as well. It is possible to specify a report to be "Processing Only" by changing the ProcessingOnly property of the Report object. The report functions as it is supposed to processing data items , but it does not generate any printed output.

When the ProcessingOnly property is set, the request page for the report changes slightly, as the Print and Preview buttons are replaced with an OK button. The Cancel and Help buttons remain unchanged. When the ProcessingOnly property is set, you cannot create a layout.

If you want to remove the layout from a report set the ProcessingOnly property to yes. You will get a prompt that the layout will be removed. Then, set the ProcessingOnly property back to No. There are advantages to using a report to process data rather than a code unit: The request page functionality that allows the user to select options and filters for data items is readily available in a report, but difficult to program in a code unit. Using the report designer features ensures consistency.

Instead of writing code to open tables and retrieving records, report data items can be used. Creating the layout Now it is time to see how to create a layout for a report. Let's start by comparing the two.

If set to No, then the system will use Visual Studio to create the report layout, otherwise Report Builder will be used. By default, the option is set to No, even when Visual Studio is not installed. You have to manually set this to yes if you want to use Report Builder.

Report Builder is normally installed together with Dynamics NAV when you install the development environment. Visual Studio has to be installed separately. Report Builder is free and can be downloaded from the Microsoft website. More information about which version of Visual Studio is right for your environment is available here: Creating a simple layout in Report Builder In this section, we are going to use Report Builder to create a report layout.

Make sure you set the option Use Report Builder to Yes. I will start with the report Packt - CH, which contains a data item for the integer table, and I will create a simple layout to display an item list.

It contains:.

Join Kobo & start eReading today

Built-in Fields: The built in fields can be considered as constants or variables. They are not a part of the dataset and can be used in the layout, in textboxes or in expressions. Parameters contain the labels and captions that were added via the IncludeCaption property on a column, or via the label designer.

Data Sources: At runtime it links to the report dataset, generated by the report dataset designer. Here you can see formatting toolbars and buttons. You can use them to format the layout of textboxes in your report. Here you can see the toolbox. Here you can customize the Report Builder look and view. At the bottom, you can see the Grouping pane. It contains Row and Column groups. You can use them when you want to add grouping to your report layout.

On the right you can see the Properties. Depending on what you select in the layout, you can see and edit its properties.

Wizards for prototyping Report Builder contains wizards to help you to create a report layout.

Search form

Let's use them to create our item list. In the Arrange fields window you can now see the columns on the left. Let's drag the fields into Values:. For the inventory, click on the little arrow and select Sum. Click on Next. In the Choose a Style window, you can select a style template. Click on Finish. Now, we are going to save and test the layout. Then close report builder. If you forget to save it, Report Builder displays the following message:. As you can deduce from this message, Dynamics NAV has generated a report.

Then, we made changes to it, via the wizard. Now, it's asking us to save our modifications. This report. This happens when you click on a line in the report dataset designer:. Click on Yes if you click on No the changes are not imported and will be lost.

After you click on Yes, the system will parse the report. Some errors will be detected at this stage. Now you need to save the layout in the report object in the Dynamics NAV database.

When you save it, with the compile option checked, the system will parse the complete report and some errors might be detected. The request page opens. Click on Preview:. To further enhance the report layout, you can reopen it via View, Layout. You don't have to close Report Builder if you know in advance the layout is not ready and you only want to test or preview it.

In that case, save the layout in Report Builder and minimize Report Builder, don't close it. Then, click on a data item to import the layout, confirm and save, and run the report.

If you need to make more changes to the layout, simply return to Report Builder, make your changes and follow the same steps to import your changes back into the report dataset designer. It's very easy to create a simple layout using the wizards in Report Builder.

I use it a lot for prototyping. You can then further enhance the report layout in Visual Studio. Creating a simple layout in Visual Studio In this section we are going to use Visual Studio to create a report layout. Make sure you set the option Use Report Builder to No. I will start with the Item List report I created with Report Builder, but first, I will remove that layout by setting the processingonly property to yes.

The following message is displayed:. After confirming and deleting the current layout, don't forget to set the property back to No. Next, click on View, Layout to open Visual Studio:.

In Visual Studio you can see similar sections to those in Report Builder. You will need to do this every time you open Visual Studio.

If Report Data is not available in the View window, then click somewhere in the body of the report to make it available in the View menu. Visual Studio automatically populates the toolbars with options depending on what you have selected in the layout. To create a table layout, I will use the Toolbox. To open the Toolbox click on View, Toolbox:. Here, I will drag the table onto the report body. Alternatively, right-click in the report body and select Insert, Table:.

To populate the table I will use the captions on the first row, which is the header row, and I will use the value fields from the dataset. From the parameters, drag the parameter into the textbox on the header row. Then, in the corresponding textbox on the detail row, click on the drop-down box to select the value:. There are many other ways of doing this, but I find this the easiest. Otherwise sometimes Visual Studio might create extra columns.

Repeat this process for all the columns. Then select the first row via its handle and make it bold. Next, we need to save our layout in Visual Studio, minimize it, and then go back into the report dataset designer and import it into the report object, just like we did with Report Builder. Then you can save and run the report to see the result. Visual Studio features Visual Studio contains many features to make report editing a pleasant experience. I will introduce these features in this and the following chapters.

Report formatting, toolbars, and document outline In Visual Studio, you can change the layout of the report designer. You can move around the report data, properties and other sections. You can enable and disable toolbars, and so on. It's much more flexible than the report builder. There is also the Format menu:. This menu contains many formatting options. They are especially useful when you need to align textboxes, for example in a document report.

There's also the Solution Explorer:. It contains the complete solution. Be careful, because only the report. Any changes to the other items in the Solution Explorer are not saved. One advantage is that we can add extra template reports in the Solution Explorer. These templates can contain tables, lists, and matrices which you can pre-format and then copy and paste into your report layout.

I will explain later how you can create and include these templates. Building and testing the layout I would like to point out that there is a big difference between what you see and experience when you print an enhanced report onscreen and what you see and expect, when you actually print it on paper, or export it to Excel, Word or PDF.

When you run a report in the report viewer then it is fully functional. What I mean by this is that all interactive features the report contains are available to the end user. When the user prints the report on paper, that interactivity is lost. If the main usage of a report is to print it, as document reports usually are, then don't spend much time adding interactivity to the report, because it will almost never be used and so will not be a very good return on investment.

Don't enhance too much From personal experience, I have seen many customers migrate towards enhanced reports as they are eager to implement a lot of interactive features in reports, and because it looks like a good thing to do. It is possible, so why not implement it? Remember that adding functionality to reports should be based upon business requirements and not only because it looks nice.

The more functionality you implement in reports, the more difficult it will be to maintain these reports. Apart from the functional reasons online reports are better than printed reports, there's also the financial aspect. By using online reports, you will spend less on paper, ink, storage, binding, and distribution. For example, drill down is available in Excel, but not in the PDF format. Testing pagination and layout in different rendering extensions Some reports look very different in online mode as compared to being printed on paper or exported to Excel or PDF.

For example, totals at the bottom, page breaks, running totals, headers, and footers are items to pay attention to.

That's why it's very important to test all of these scenarios and explain to the end user how to use the report. Even when the user specifically asks for only Excel and PDF it is important to test other renderings and run any issues that you see by the users, to make sure they don't come back later if they see a problem with the other rendering options. Testing the report in different clients Windows, Web, and tablet Just as you test different rendering extensions for a report, you should also test it in different clients.

The actual report content will be the same, but the request page might be different or sometimes not shown at all. Reporting design guidelines Microsoft has published design guidelines you can follow when you create a layout for a report. This is especially important when you develop document and list reports. By following these guidelines, you can make sure that your reports are:.

Applying these guidelines has the advantage that you can use a minimum set of rules, by using the default formatting options available in Visual Studio. By the end, you will create different types of charts so you can visualize key performance indicators inside the Dynamics NAV application. This book is an essential guide in to understanding what is involved in creating reports in Dynamics NAV and its reporting capabilities. Downloading the example code for this book. You can download the example code files for all Packt books you have purchased from your account at http: If you purchased this book elsewhere, you can visit http: Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more. Start Free Trial No credit card required. View table of contents. Start reading. Basic knowledge of Dynamics NAV will be helpful. Style and approach This book is an essential guide in to understanding what is involved in creating reports in Dynamics NAV and its reporting capabilities.

Free access for Packt account holders Instant updates on new Packt books Preface What this book covers What you need for this book Who this book is for Conventions Reader feedback Customer support Downloading the color images of this book Errata Piracy Questions 1.

What is a report? The request page The report viewer Report development phases The data model phase The layout phase The testing phase Report development tools What do I use to develop the data model? How do I create the report layout? Unrelated tables or multiple data items, without indentation Related tables or multiple data items with indentation Report triggers What happens when a report runs? The report trigger sequence What is a ProcessingOnly report?In this way, a user can switch between different layouts for the same report.

The report functions as it is supposed to processing data items , but it does not generate any printed output. Moving on, the book teaches you how to visualize data and be able to understand and read a report as it is intended. In each group, you will find links to reports: These steps can be visualised in the following diagram: Really very slow performance compared to the same scenario with CAS Policy enabled.

Chapter 7, Performance Optimization Techniques, contains tips, tricks, and recipes on how to optimize or performance tune a report.

SARAN from Indiana
Please check my other articles. One of my extra-curricular activities is ba game. I do enjoy exploring ePub and PDF books correctly .