Programming Microsoft Dynamics NAV 2009 — Save 50%
Using this Microsoft Dynamics NAV book and eBook - develop and maintain high performance applications to meet changing business needs with improved agility and enhanced flexibility.
In this article David Studebaker, we will review the components that make up reports. We'll look in detail at the triggers, properties, and controls that are part of NAV reports. We will study the Report Designer tools that are a combination of pure NAV (the C/SIDE Report Designer) and the Visual Studio Report Designer that is tightly integrated into NAV 2009. We'll create some reports with the Report Designer tools. We'll also modify a report or two using the Report Designer. We'll examine the data flow of a standard report and the concept of reports used for processing only (with no printed or displayed output).
Report components overview
What we generally refer to as the report or report object is technically referred to as a Report Description. The Report Description is the information describing the layout for the planned output and processing logic to be followed when processing the data. Report Descriptions are stored in the database in the same way as other table or form/page descriptions.
As with pages, we will use the term "report" whether we mean the output, the description, or the object. Reports share some attributes with pages including aspects of the designer, features of various controls, some triggers, and even some of the properties. Where those parallels exist, we should take notice of them. The consistency of any toolset, including NAV, makes it easier to learn and to use. This applies to developers as well as to the users.
The overall structure of an NAV RTC Report consists of the following elements. Any particular report may utilize only a small number of the possible elements (for example, Section Triggers are not used by the RTC), but many different combinations are feasible and logical.
- Report Properties
- Report Triggers
- Data Items
- Data Item Properties
- Data Item Triggers
- Data Item Sections
- Section Triggers
- Data Field Controls
- Visual Studio Layout
- VS Controls
- VS Control Properties
- VS Controls
- Request Page
- Request Page Properties
- Request Page Triggers
- Request Page Controls
- Request Page Control Properties
- Request Page Control Triggers
The components of a report description
A report description consists of a number of primary components, each of which in turn is made up of secondary components. The primary components Report Properties and Triggers and Data Item Properties and Triggers define the data flow and overall logic for processing the data. These are all designed in the C/SIDE Report Designer.
A subordinate set of primary components, Data Field Controls and Working Storage, are defined within the DataItem Sections, which are also designed in the C/SIDE Report Designer.
Data Fields are defined in this article as the fields contained in the DataItems (that is application tables). Working Storage (also referred to as Working Data) fields are defined in this article as the data elements that are created within a report (or other object) for use in that object. Working Storage data elements are not permanently stored in the database.
These components constitute the data elements that will be made available to the Visual Studio Report Designer (VS RD).
The VS RD cannot access any data elements that have not been defined within the Report Sections (each of which must be associated with a DataItem).
The Report Layout is designed in the VS RD using the data elements made available to the VS RD by the C/SIDE RD, defined in the DataItem Sections. The Report Layout includes the Page Header, Body, and Page Footer. In most cases, the Body of the report is based on a layout table.
Note that the VS RD layout table is a data grid used for layout purposes and is not the same as a table of data stored in the NAV database. This terminology is confusing. When the NAV Help files regarding reports refer to a table, you will have to read very carefully to determine which meaning for "table" is intended.
Within the Report Body, there can be none, one, or more Detail rows. There can also be Header and Footer rows. The Detail rows are the definition of the primary, repeating data display. A report layout may also include one or more Group rows, used to group and total data that is displayed in the Detail row(s).
All of the report formatting is controlled in the Report Layout. The Font, field positioning, visibility options (including expand/collapse sections), dynamic sorting, and graphics are all defined as part of the Report Layout. The same is true for pagination control, headings and footers, some totaling, column-width control, and a number of other display details.
Of course, if the display target changes dramatically in the future versions of NAV, for example, from a desktop workstation display to a browser supporting cellular phone, then the appearance of the Report Layout will change dramatically as well. One of the advantages of SSRS is to support such a level of flexibility. But, if you expect that degree of variability in output devices, you will have design accordingly.
There is another primary functional component of a report description, the Request Page. It displays as a page when a report is invoked. The purpose of the Report Request Page is to allow users to enter information to control the report. Control information entered through a Request Page may include filters, control dates, other control parameters, and specifi cations as well as defining which available formatting or processing options to use for this instance of the report (that is for this run). The Request Page appears once at the beginning of a report at run time.
Report Data Flow
One of the principal advantages of the NAV report is its built-in data flow structure. At the beginning of any report, you must define the data item(s), the tables that the report will process. It is possible to create a working report that has no data items, but that situation normally calls for a codeunit to be used. There are rare exceptions to this, such as a report created for the purposes of processing only, perhaps to control branching or choice of objects to be run subsequently. In that case, you might have no data item, just a set of logic whose data flow is totally self-controlled. Normally in a report, NAV automatically creates a data flow process for each data item. This automatically created data flow provides specific triggers and processing events:
The underlying "black-box" report logic (the part we can't see or affect) loops through the named tables, reading and processing one record at a time. That flow is automatic, that is we don't have to program it. Therefore, any time we need a process that steps through a set of data one record at a time, it is quite likely we will use a report object.
If you've ever worked with some of the legacy report writers or the RPG programming language, it is likely that you will recognize this looping behavior. That recognition may allow you to understand how to take advantage of NAV reports more quickly.
The reference to a database table in a report is referred to as a Data Item. One of the capabilities of the report data flow structure is the ability to nest data items. If Data Item 2 is nested within Data Item 1 and related to Data Item 1, then for each record in Data Item 1, all of the related records in Data Item 2 will be processed. The next screenshot shows the data item definition screen.
This example uses tables from our ICAN system. The design is for a report to list all the Gifts by Donor for each Donor Type. Thus Donor Type is the primary table (DataItem1). For each Donor Type, we want to list all the Donors that have given Gifts to ICAN (DataItem2). And for each Donor of each Donor Type, we want to list their Gifts which are recorded in the Gift Ledger (DataItem3).
On the Data Item screen, we initially enter the table name Donor, as you see in the following screenshot. The Data Item Name, to which the C/AL code will refer, is DataItem1 in our example here. When we enter the second table, Donor, then we click on the right arrow at the bottom of the screen. That will cause the selected data item to be indented relative to the data item above (the "superior" data item). That causes the nesting of the processing of the indented data item within the processing of the superior data item.
In this instance, we have renamed the Data Items just for the purpose of our example, in order to illustrate data flow within a report. The normal default behavior would be for the Name in the right column to default to the table name shown in the left column (for example, the Name for Donor would be displayed as < Donor > by default). This default Data Item Name would only need to be changed if the same table appeared twice within the Data Item list. For the second instance of Donor, for example, you could simply give it the Name Donor2.
For each record in the superior data item, the indented data item will be fully processed. Which records are actually processed in the indented table will depend on the filters, and the defined relationships between the superior and indented tables. In other words, the visible indentation is only part of the necessary definition. We'll review the rest of it shortly.
For our example, we enter a third table, Gift Ledger, and enter our example name of DataItem3.
The following chart shows the data flow for this Data Item structure. The chart boxes are intended to show the nesting that results from the indenting of the Data Items in the preceding screenshot. The Donor Data Item is indented under the Donor Type Data Item. That means for every processed Donor Type record, all of the selected Donor records will be processed. That same logic applies to the Donor records and Gift Ledger records (that is, for each Donor record processed, all selected Gift records are processed).
The blocks visually illustrate how the data item nesting controls the data flow. As you can see, the full range of processing for DataItem2 occurs for each DataItem1 record. In turn, the full range of processing for DataItem3 occurs for each DataItem2 record.
In Classic Client reporting, the formatting and output for each record processed happened in sequence as the last step in processing that record. In other words, once a record was read and processed, it was rendered for output presentation before the next record was read.
In the NAV 2009 Role Tailored Client, report processing occurs in two separate steps, the first tied primarily to what has been designed in the Classic RD, the second tied to what has been designed in the VS RD. The processing of data represented in the preceding image occurs in the first step, yielding a complete dataset containing all the data that is to be rendered for output.
This intermediate dataset is a flattened version of the hierarchically structured dataset represented in the Classic RD. Each record in the new dataset contains all the fields from the sections (parent, child, grandchild, and so on) "de-normalized" into a completely flat data structure. This structure also includes Grouping, Filtering, Formatting, MultiLanguage, and other control information required to allow the Visual Studio Report Viewer to properly render the defined report.
That flattened dataset is then handed off to the Visual Studio Microsoft Report Viewer. The Microsoft Report Viewer provides the new NAV 2009 reporting capabilities such as various types of graphics, interactive sorting and expand/collapse sections, output to PDF and Excel, and other advanced reporting features based on RDLC created by the VS RD design work.
The elements of a report
Earlier we reviewed a list of the elements of a Report object. Now we're going to learn about each of those elements. Our goal here is to understand how the pieces of the report puzzle fit together to form a useful, coherent whole. Following that, we will do some development work for our ICAN system to apply some of what we've reviewed.
The Classic RD Report Properties are shown in the following screenshot. Some of these properties have essentially the same purpose as those in pages and other objects. Many of these Report Property settings only apply to Classic reports and are replaced by equivalent Report Properties in the Visual Studio RD.
- ID: The unique report object number.
- Name: The name by which this report is referred to within C/AL code.
- Caption: The name that is displayed for this report; Caption defaults to Name.
- CaptionML: The Caption translation for a defined alternative language.
- ShowPrintStatus: If this property is set to Yes and the ProcessingOnly property is set to No, then a Report Progress window, including a Cancel button, is displayed. When ProcessingOnly is set to Yes, if you want a Report Progress Window, you must create your own dialog box.
- UseReqForm: Determines if a Request Page should be displayed to allow the user the choice of Sort Sequence and entry of filters and other requested control information.
- UseSystemPrinter: Determines if the default printer for the report should be the defined system printer, or if NAV should check for a setup-defined User/Report printer definition.
- ProcessingOnly: This should be set to Yes when the report object is being used only to process data and no report output is to be generated. If this property is set to Yes, then that overrides any other property selections that would apply in a report-generating situation.
- TransactionType: This can be in one of four basic options: Browse, Snapshot, UpdateNoLocks, and Update. These control the record locking behavior to be applied in this report. The default is UpdateNoLocks. This property is generally only used by advanced developers.
- Description: This is for internal documentation; it is not used often.
- TopMargin, BottomMargin, LeftMargin, RightMargin: Does not apply to an RTC report. There are applicable VS RD properties.
- HorzGrid, VertGrid: Does not apply to an RTC report. VS RD layout has its own grid for control positioning.
- Permissions: This provides report-specific setting of permissions, which are the rights to access data, subdivided into Read, Insert, Modify, and Delete. This allows the developer to define report and processing permissions that override the user-by-user permissions security setup.
The following printer-specific properties do not apply to an RTC report. Several can be overridden by user selections made at run time.
- Orientation: There is an applicable VS RD property
- PaperSize: There is an applicable VS RD property
- PaperSourceFirstPage, PaperSourceOtherPages: This is controlled in Page Setup for RTC reports
- DeviceFontName: This is controlled by the Report Viewer
The Visual Studio RD Report Properties are shown in the following screenshot:
The following screenshot shows the Report triggers available in a report:
- Documentation(): Documentation is technically not a trigger, but a section which serves only the purpose of containing whatever documentation you care to put there. No C/AL code is in a Documentation section. You have no format restrictions, other than common sense and your defined practices.
- OnInitReport(): It executes once when the report is opened.
- OnPreReport(): It executes once after the Request Page completes. All the Data Item processing follows this trigger.
- OnPostReport(): If the report is completed normally, this trigger executes once at the end of all of the other report processing. All the Data Item processing precedes this trigger.
- OnCreateHyperlink(): It does not apply to an RTC report.
- OnHyperlink(): It does not apply to an RTC report.
There are general explanations of Report Triggers in the online C/SIDE Reference Guide (Help); you should also review those explanations.
|Using this Microsoft Dynamics NAV book and eBook - develop and maintain high performance applications to meet changing business needs with improved agility and enhanced flexibility.|
eBook Price: £26.99
Book Price: £43.99
The following screenshot is very similar to the example we looked at when we reviewed Data Item Flow. This time though, we allowed the Name assigned to the Data Items to default. As a result, the names are assigned equal to the DataItems they reference.
Good reasons for changing the assigned names include making them shorter for ease of coding or making them unique, which is required when the same table is referred to multiple times in a report. For example, suppose you were creating a report that was to list first Open Sales Orders, then Open Sales Invoices, and then Open Sales Credit Memos. As all the three of these data sets are in the same two tables (Sales Header and Sales Line), you might create a report with Data Item names of SalesHeader1, SalesHeader2, and SalesHeader3, all referencing Sales Header Data Items.
Data item properties
The following screenshots show the properties of the three Data Items in the previous screenshot. The first one shows the Donor Type—Properties:
The following screenshot shows Donor—Properties:
The screenshot that follows shows the Gift Ledger—Properties:
These are the descriptions of each of the properties mentioned:
- DataItemIndent: This shows the position of the referenced Data Item in the hierarchical structure of the report. A value of 0 (zero) indicates that this Data Item is at the top of the hierarchy. Any other value indicates the subject Data Item is subordinate to (that is nested within) the preceding Data Item. In other words, a higher valued DataItemIndent property is subordinate to any Data Item with a lower valued DataItemIndent (for example, a DataItemIndent of 1 is subordinate to 0).
If we look at the DataItemIndent property listed in each of the three preceding screenshots, we see that Donor Type has a DataItemIndent=0, Donor has a DataItemIndent=1, and Gift Ledger has a DataItemIndent=2. Referring back to the earlier discussion about data flow, we see that the specified Donor table data will be processed through for each record processed in the Donor Type table, and the specified Gift Ledger table data will be processed through for each record processed in the Donor table.
- DataItemTable: This is the name of the database table assigned to this Data Item.
- DataItemTableView: This is the definition of the fixed limits to be applied to the Data Item (the key, ascending or descending sequence, and what filters to apply to this field). If you don't define a key, then the users can choose the key they want. This allows control of the data sequence to be used during processing.
If you define a key in the Data Item properties and, in the ReqFilterFields property, you do not specify any Filter Field names to be displayed, this Data Item will not have a tab displayed as part of the Request Page. That will stop the user from filtering this Data Item, unless you provide the capability in C/AL code.
- DataItemLinkReference: This names the Data Item in the hierarchy above the Data Item to which this one is linked. The linked Data Item can also be referred to as the parent Data Item. As you can see, this property is Undefined for Donor Type, because Donor Type is at the top of the Data Item hierarchy for this report.
- DataItemLink: This identifies the field-to-field linkage between this Data Item and its parent Data Item. That linkage acts as a filter because only those records in this table will be processed that have a value that matches with the linked field in the parent data item. In our sample, the Donor Data Item does not have a DataItemLink specified.
If this is not changed, no field linkage filter will be applied and all of the records in the Donor table will be processed for each record processed in its parent table that is the Donor Type table. In order to correct this, so that only Donors of the proper type are processed subordinate to each Donor Type, the DataItemLink needs to be changed to define the linkage.
- The following screenshot shows the screen where the DataItem Link is defined:
The next screenshot shows the Donor—Properties screen after the DataItemLink has been defined. Defining the DataItemLink in this way will cause the Donor table to be filtered by the active Donor Type Code for each loop through the Donor Type table. This will group Donors by Donor Type for report processing.
- NewPagePerGroup, NewPagePerRecord : Does not apply to an RTC report. There are replacement VS RD properties.
- ReqFilterHeader, ReqFilterHeadingML : Does not apply to an RTC report. There are replacement VS RD properties.
- ReqFilterFields: This property allows you to choose certain fields to be named in the Report Request Page, to make it easier for the user to access them as filter fields. So long as the Report Request Page is activated for a Data Item, the user can choose any available field in the table for filtering, regardless of what is specified here. Note the earlier comments for the DataItemTableView property are relative to this property. A screenshot of a sample Report Request page follows:
- TotalFields, GroupTotalFields : Does not apply to an RTC report. Built-in VS RD functions are to be used instead.
- CalcFields: This names the FlowFields that are to be calculated for each record processed. As FlowFields do not contain data, they have to be calculated to be used. When a FlowField is displayed on a page, NAV automatically does the calculation. When a FlowField is to be used in a report, you must instigate the calculation. That can either be done here in this property or explicitly within C/AL code.
- MaxIteration: This can be used to limit the number of iterations (that is loops) the report will make through this Data Item to a predefined maximum. An example would be to set this to 7 for processing with the virtual Date table to process one week's worth of data.
- DataItemVarName: This contains the name shown in the right column of the Data Item screen, the name by which this table is referenced in this report's C/AL code.
- PrintOnlyIfDetail: This should only be used if this Data Item has a child Data Item, that is one indented/nested below it. If PrintOnlyIfDetail is Yes, then sections associated with this Data Item will only print when data is processed for the child Data Item.
- This property is set to Yes only for the Donor Data Item (see the immediate previous screenshot). That is done so that if there is no Gift Ledger for a particular Donor, the Donor will not print. If we wanted to print only Donor Types that have Donors, we could also set the PrintOnlyIfDetail property to Yes for the Donor Type Data Item.
Data item triggers
Each Data item has the following triggers available:
- Documentation() is actually the same instance of this section that showed when we looked at the report triggers. There is only one Documentation in any object.
The data item triggers are where the bulk of the flow logic is placed for any report. Additionally, developer defined functions may be freely and voluminously added, but, for the most part, they will be called from within these three triggers.
- OnPreDataItem() is the logical place for any preprocessing to take place that couldn't be handled in report or data item properties or in the two report preprocessing triggers.
- OnAfterGetRecord() is the data "read/process loop". Code placed here has full access to the data of each record, one record at a time. This trigger is repetitively processed until the logical end of table is reached for this table. This is where you would likely look at data in the related tables. This trigger is represented on our report Data Flow diagram as any one of the boxes labeled data item processing Loop.
- OnPostDataItem() executes after all the records in this data item are processed, unless the report is terminated by means of a User Cancel or execution of a C/AL BREAK or QUIT function, or by an error.
If you have read this article you may be interested to view :
- Report components in NAV 2009: Part 2
- Report components in NAV 2009: Part 3
- A Short Tour through NAV 2009: Part 1
- A Short Tour through NAV 2009: Part 2
- A Short Tour through NAV 2009: Part 3
- NAV 2009: Reports
|Using this Microsoft Dynamics NAV book and eBook - develop and maintain high performance applications to meet changing business needs with improved agility and enhanced flexibility.|
eBook Price: £26.99
Book Price: £43.99
About the Author :
David Studebaker is Chief Technical Officer and a founder of Liberty Grove Software with his partner Karen Studebaker. Liberty Grove Software, a Microsoft Partner, provides development, consulting, training, and upgrade services internationally for Microsoft Dynamics NAV resellers and end user customers.
David has been recognized by Microsoft as a Certified Professional for NAV in all three areas: Development, Applications, and Installation & Configuration. He has been honored by Microsoft as a Lead Certified Microsoft Trainer for NAV.
David just celebrated his first half century of programming, having started programming in 1962. He has been developing in C/AL since 1996. David has been an active participant in each step of computing technology from the first solid state mainframes to today's technology, from binary assembly language coding to today's C/AL and C#.
David's special achievements include his role as co-developer of the first production multi-programmed SPOOLing system in 1967. David has worked on a diverse set of software applications including manufacturing, distribution, retail, engineering, general accounting, association management, professional services billing, distribution/inventory management, freight carriage, data collection and production management, among others. Prior to co-authoring this book, David was the author of Programming Microsoft Dynamics NAV (for the Classic Client) and Programming Microsoft Dynamics NAV 2009 (for the Role Tailored Client).
David has had a wide range of development, consulting, sales and management roles throughout his career. He has been partner or owner and manager of several software development businesses, while always maintaining a hands-on role as a business applications developer.
David has a BS in Mechanical Engineering from Purdue University and an MBA from the University of Chicago. He has been writing for publication since he was an undergraduate. David has been a member of the Association for Computing Machinery since 1963 and was a founding officer of two local chapters of the ACM.