Queries and Reports

"Data helps solve problems."

– Anne Wojcicki

"The greatest value of a picture is when it forces us to notice what we never expected to see."

– John Tukey

In Microsoft Dynamics 365 Business Central, reports and queries are two ways to extract and output data for the purpose of presentation to a user (reports can also modify data). Each of these objects use tools and processes that are Business Central-based and work on data extraction (XMLports, which can also extract and modify data, will be covered in Chapter 8, Advanced AL Development Tools). In this chapter, we will focus on understanding the strengths of each of these tools and when and how they might be used. We will cover the Business Central side of both queries and reports in detail to describe how to obtain the data we need to present to our users. We will cover output...

Queries

Reports have always been available in Business Central as a data retrieval tool. Reports are used to process and/or manipulate the data through the INSERT, MODIFY, or DELETE functions, with the option of presenting the data in a formatted, printable format. In older versions, data selection could only be done using AL code or DataItem properties to filter individual tables as datasets, and to perform loops to find the data that's required for the purpose. Then, the query object was created with performance in mind. Instead of multiple calls to SQL to retrieve multiple datasets which would then be manipulated in AL, Queries allow us to utilize familiar Business Central tools to create advanced T-SQL queries.

A Business Central developer can utilize the new query object as a source of data both in Business Central and externally. Some of the external uses of Business Central queries are as follows:

• A web service source for SOAP and OData
• Feeding data to external reporting...

Building a simple query

Sometimes, it is necessary to quickly retrieve detailed information from one or more ledgers that may contain hundreds of thousands to many millions of records. The query object is the perfect tool for such data selection as it is totally scalable and can retrieve selected fields from multiple tables at once. The following example (using Cronus data) will show the aggregated quantity per bin of lot-tracked items in stock. This query can be presented to a user by means of either a report or a page:

1. We will define the logic we need to follow and the data that's required to support that logic, and then we will develop the query. It is necessary to know what inventory is in stock, which also contains a lot number. This is accomplished using the Item Ledger Entry table.
2. However, the Item Ledger Entry record does not contain any bin information. This information is stored in the Warehouse Ledger Entry table.

3. The Location Code, Item No., and Lot No. columns are...

Query and query component properties

There are several query properties we should review. Their descriptions follow.

Query properties

The properties of the query object can be accessed by highlighting the first empty line after the object name and clicking on Ctrl + spacebar. The properties of the query we created earlier will look like this:

We'll review three of these properties:

• OrderBy: This provides the capability to define a sort, data column by column, and ascending or descending, giving the same result as if a key had been defined for the query result, but without the requirement for a key.
• TopNumberOfRows: This allows for the specification of the number of data rows that will be presented by the query. A blank or 0 value shows all rows. Specifying a limit can make the query complete much faster. This property can also be set dynamically from the AL code.
• ReadState: This controls the state (committed or not) of data that is included and the type of lock that is placed on the data read.

DataItem properties

A query line can be one of three types: DataItem, column, and filter. Each has its own property set:

Again, we'll review a selected subset of these properties:

• SqlJoinType: If enabled by the DataItemLinkType property, this property allows the specification of one of five different SQL join types (inner, left outer, right outer, full outer, or cross join). More information is available in the Help sections. Look for the SqlJoinType property and SQL Advanced Options for Data Item Link Types.
• DataItemTableFilter: This provides the ability to define filters that will be applied to the DataItem.

Column properties

The following screenshot is a column property screen showing the Bin Code column for our simple query:

The properties shown in the preceding screenshot are as follows:

• Method: Can be Sum, Count, Avg, Min, Max, Day, Month, or Year. The result in the column will be based on the appropriate computation. See the Help section for Method Property for more information.
• ReverseSign: This reverses the sign of the column value for numeric data.

• ColumnFilter: This allows us to apply a filter to limit the rows in the query result. Filtering here is similar to, but more complicated than, the filtering rules that apply to DataItemTableFilter. Static ColumnFilters can be dynamically overridden and can also be combined with DataItemTableFilters. See the Help section for the ColumnFilter property for more detailed information.

Reports

Some consider the standard library of reports that's provided in the Business Central product distribution from Microsoft to be relatively simple in design and limited in its features. Others feel that the provided reports satisfy most needs because they are simple, but flexible. Their basic structure is easy to use. They are made much more powerful and flexible by taking advantage of Business Central's filtering and SIFT capabilities. There is no doubt that the existing library can be used as a foundation for many of the special reports that customers require to match their own specific business management needs.

The fact is that Business Central's standard reports are basic. To obtain more complex or more sophisticated reports, we must use features that are part of the product or feed processed data for external reporting tools such as Excel. Through creative use of these features, many different types of complex report logic may be implemented.

We'll create...

What is a report?

A report is a vehicle for organizing, processing, and displaying data in a format that's suitable for outputting to the user. Reports may be displayed on-screen in Preview mode, output to a file in Word, Excel, or PDF format (or, when appropriately designed, output in HTML, CSV, or XML format), emailed to a user (or other consumers of this information), or printed to hard-copy—the old-fashioned way. All of the report screenshots in this book were taken from Preview mode reports.

Once generated, the data contents of a report are static. When a Business Central report is output in Preview mode, the report can have interactive capabilities. Those capabilities only affect the presentation of the data; they do not change the actual data that's included in the report dataset. Interactive capabilities include dynamic sorting, visible/hidden options, and detail/summary expand/collapse functions. All of the specifications for the data selection criteria for a...

Four Business Central report designers

Any Business Central report design project uses at least two Report Designer tools. The first is the Report Designer, which is part of the Visual Studio Code development environment. The second is the developer's choice of Visual Studio, the SQL Server Report Builder, Microsoft Word. Refer to the Microsoft Dynamics 365 Business Central development environment requirements for more information about the choice of tools for handling RDLC report layouts for Business Central. We must also be careful to make sure that we obtain the proper version of the report tool and that it's compatible with the Business Central version we are using. The SQL Server Report Builder is installed by default during the Business Central system's installation. There is also a free version of Visual Studio, the Community Edition, which is available at https://msdn.microsoft.com/en-us/visual-studio-community-vs.aspx.

For our work, we will use a combination of...

Business Central report types

The standard Business Central application uses only a few of the possible report styles, most of which are in a relatively basic format. The following are the types of reports that are included in Business Central:

• List: This is a formatted list of data. A standard list is the Inventory - List report (report 701):

• Document: This is formatted similarly to a pre-printed form, where a page (or several pages) contains a header, detail, and footer section with dynamic content. Examples of document reports include customer invoice, packing list (even though it's called a list, it's a document report), purchase order, and accounts payable cheque. The following screenshot is a customer Sales-Invoice document report preview:

List and document report types are defined based on their layouts. The following three report types are defined based on their usage rather than their layouts, and are as follows:

• Transaction: This provides a list of ledger entries...

Report types summarized

The following table describes the different basic types of reports that are available in Business Central:

Type	Description
List	This is used to list volumes of similar data in a tabular format, such as a list of Sales Order Lines, a list of Customers, or a list of General Ledger Entries.
Document	This is used in record-per-page header plus line item detail dual layout situations, such as a Sales Invoice, a Purchase Order, a Manufacturing Work Order, or a Customer Statement.
Transaction	This generally presents a list of transactions in a nested list format, such as a list of General Ledger Entries grouped by G/L Account, Physical Inventory Journal Entries grouped by Item, or Salesperson To-Do List by Salesperson.
Test	This prints in a list format as a pre-validation test and data review, prior to a Journal Posting run. A Test Report option can be found on any Journal page, such as the General Journal, Item Journal, or the Jobs Journal...

Report naming

Simple reports are often named the same as the table with which they are primarily associated, plus a word or two describing the basic purpose of the report. Common key report purpose names include the words Journal, Register, List, Test, and Statistics. Some examples include General Journal - Test, G/L Register, and Customer - Order Detail.

When there are conflicts between naming based on the associated tables and naming based on the use of the data, the usage context should take precedence in naming reports, just as it does with pages. One absolute requirement for names is that they must be unique; no duplicate names are allowed for a single object type. Remember, the caption (what shows on the printed report heading) is not the same as the name (the internal name of the report object). Each one is a different report property.

Report components – overview

What we generally refer to as the report or report object that's created with SQL Server Report Builder or Visual Studio Report Designer is technically referred to as an RDLC Report. (From here on out, we will focus on the Visual Studio report layout tool. The tools are somewhat different, but the end results are basically the same.) An RDLC Report includes information that describes the logic to be followed when processing the data (the data model), the dataset structure that is generated by Visual Studio Code, and the output layout that's designed with Visual Studio. RDLC Reports are stored in the Business Central database. Word report XML layouts are also stored in the Business Central database. Business Central allows there to be multiple RDLC and Word formats for a single report. We will use the term "report" when we are referring to the output, the description, or the object.

Reports share some attributes with pages, including...

Report structure

The overall structure of a Business Central RDLC Report consists of the following elements:

• Report properties
• Report triggers
• Request page:
  • Request page properties
  • Request page triggers
  • Request page controls:
    • Request page control triggers
• DataItems:
  • DataItem properties
  • DataItem triggers
  • Data columns:
    • Data column properties
• SSRB (RDLC) layout:
  • SSRB (RDLC) controls:
    • SSRB (RDLC) control properties
• Word layout:
  • Word layout template
  • Word controls:
    • Word control properties

Report data overview

Report components, report properties and triggers, request page properties and triggers, and DataItems and their properties and triggers define the data flow and overall logic for processing the data. Another set of components, Data Fields and working storage, are defined as being subordinate to the DataItems (or request page). These are all designed and defined in Visual Studio Code.

Data Fields are defined in this book as the fields that are contained in the DataItems (application tables). Working Storage (also referred to as working data or variables) fields are defined in this book as the data elements that are defined within a report (or other object) for use in that object. The contents of working storage data elements aren't permanently stored in the database. All of these are collectively referred to in the Business Central Help as columns.

These components define the data elements that are made available to Visual Studio as a dataset, which will be used...

Report layout overview

The report layout is designed in Microsoft Word using data elements that are defined in dataset DataItems by the Report Designer, and then made available to Microsoft Word. The report layout includes the page header, body, and page footer.

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.

All of the report formatting is controlled in Microsoft Word. The font, field positioning, 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, color, and many other display details.

Of course, if the display target changes dramatically, for example, from a desktop workstation display to a browser on a phone, the appearance of the report layout will change dramatically as well. One of the advantages of the Business Central reporting layout...

Report data flow

One of the principal advantages of the Business Central report is its built-in data flow structure. At the beginning of any report, we will define the DataItems (the tables) that the report will process. We can create a processing-only report that has no DataItems (if no looping through database data is required), but that situation often calls for a code unit to be used. In a report, Business Central automatically creates a data flow process for each DataItem or table reference. This automatically-created data flow provides specific triggers and processing events for each DataItem, as follows:

• Preceding the DataItem
• After reading each record of the DataItem
• Following the end of the DataItem

The underlying black-box report logic (the part we can't see or affect) automatically loops through the named tables, reading and processing one record at a time. Therefore, any time we need a process that steps through a set of data one record at a time, it is often easier...

Report components – detail

Earlier, we reviewed a list of the components of a report object. Now, we'll review detailed information about each of those components. Our goal here is to understand how the pieces of the report puzzle fit together.

Report properties

A subset of the Report Designer's report properties are shown in the following screenshot. Some of these properties have essentially the same purpose as similarly named properties in pages and other objects:

The properties in the preceding screenshot are defined as follows:

• Caption: This is the name that is displayed for this report; Caption defaults to Name.
• CaptionML: This is the Caption translation for a defined alternative language.
• Description: This is for internal documentation.
• UseRequestPage: This can either be Yes or No, and controls whether or not the report will begin with a Request Page so that user parameters can be entered.
• UseSystemPrinter: This determines whether the default printer for the report should be the defined system printer, or whether Business Central should check for a setup-defined user/report printer definition. More on user/report printer setup can be found in the Business Central application help.

• EnableExternalImages: If Yes...

Microsoft Word – report properties

The Word properties window is available from the ribbon on top of the screen:

In the Layout ribbon, we can set Margins, Orientation, and paper Size. Other properties such as Font, styles, and effects can be set directly from the Home ribbon:

Another way to access property details is to highlight the element of interest, and then right-click and select the Properties option for that element. This opens up a control-specific Properties window that includes a menu of choices of property categories to access. In the following screenshot, the Font properties are open, and the available options for the Font properties are displayed:

Some may feel it's easier to find and change all of the properties' options here.

Report triggers

The following screenshot shows the Report Designer's report triggers, all of which are available in a report:

Descriptions for the report triggers are as follows:

• OnInitReport(): This executes once when the report is opened.
• OnPreReport(): This executes once after the request page completes. All the DataItem processing follows this trigger.
• OnPostReport(): This trigger executes once at the end of all of the other report processing, if the report completes normally. If the report terminates with an error, this trigger does not execute. All of the DataItem processing precedes this trigger.

Request page properties

The request page properties are a subset of page properties, which are covered in detail in Chapter 4, Pages – The Interactive Interface. Usually, most of these properties are not changed, simply because the extra capability is not needed. An exception is the SaveValues property which, when set to Yes, causes entered values to be retained and redisplayed when the page is invoked another time. A screenshot of a subset of the request page properties follows:

Request page triggers

Request pages have a full complement of triggers, thus allowing complex pre-report processing logic. Because of their comparatively simplistic nature, request pages seldom need to take advantage of these trigger capabilities. The following screenshot shows the triggers of a request page:

DataItem properties

The following screenshot shows the properties of a report DataItem:

The following are descriptions of frequently used report DataItem properties:

• DataItemTableView: This is the definition of the fixed limits to be applied to the DataItem (the key, ascending or descending sequence, and what filters to apply to this field).

If we don't define a key in DataItemTableView, then the users can choose a key to control the data sequence during processing. If we do define a key in the DataItem properties and, in the ReqFilterFields property, we do not specify any filter Fieldnames to be displayed, this DataItem will not have a FastTab displayed as part of the Request Page. This will stop the user from filtering this DataItem, unless we provide the capability in AL code.

• DataItemLinkReference: This names the parent DataItem to which this one is linked.
• DataItemLink: This identifies the field-to-field linkage between this DataItem and its parent DataItem. That linkage...

DataItem triggers

Each DataItem has the following triggers available:

The DataItem triggers are where most of the flow logic is placed for a report. Developer defined functions may be freely added but, generally, they will be called from within the following three triggers:

• OnPreDataItem(): This is the logical place for any preprocessing to take place that can't be handled in report or DataItem properties or in the two report preprocessing triggers.
• OnAfterGetRecord(): This is the data read and process loop. The code that's 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 DataItem. This is where we would typically access data in related tables. This trigger is represented on our report data flow diagram as any one of the boxes labeled Data Item processing Loop.
• OnPostDataItem(): This executes after all the records in this DataItem are processed,...

Creating a report in Business Central

Because our Business Central report layouts will all be developed in Microsoft Word, our familiarity with Visual Studio Code will only get us part way to having Business Central report development expertise. We've covered most of the basics of the Visual Studio Code part of Business Central report development. Now, we need to dig into the Microsoft Word part. If you are already a Microsoft Word expert, you won't need to spend much time on this part of the book. If you know little or nothing about the Microsoft Word layout tools, you will need to experiment and practice.

Learn by experimentation

One of the most important learning tools available is experimentation. Report development is one area where experimentation will be extremely valuable. We need to know which Report layouts, control settings, and field formats work well for our needs and which do not. The best way to find out is by experimentation.

Create a variety of test reports, beginning with the very simple, and make them progressively more complex. Document what you learn as you make discoveries. You will end up with your own personal report development Help documentation. Once we've created a number of simple reports from scratch, we should modify test copies of some of the standard reports that are part of the Business Central system.

We must always make sure that we are working on test copies, not the originals!

Some reports will be relatively easy to understand, and others that are very complex will be difficult to understand. The more we test, the better we will be able to determine...

Report building – phase 1

Our goal is to create a report for our WDTU data that will give us a list of all the scheduled radio show instances that have been organized within Radio Show by Radio Show Type, as shown in the following screenshot:

An easy way to recreate the preceding screenshot is to simply use IntelliSense. Type the letter D in the Code window, enter a name, and then the target table name (in this case, Radio Show Type, then Radio Show, and then Playlist Header).

Before we go any further, let's make sure we've got some test data in our tables. To enter data, we can use the pages we built earlier. The specifics of our test data aren't critical. We simply need a reasonable distribution of data so that our report test will be meaningful. The following triple screenshot provides an example minimal set of data:

Since the Visual Studio Code part of our report design is relatively simple, we can do it as part of our phase 1 effort. It's simple...

Report building – phase 2

As we mentioned earlier, there are several tools we can use for Business Central report layout development. The specific screen appearance depends somewhat on which tool is being used.

To begin our report development work in Microsoft Word, we must build our AL dataset definition. Then, we need to navigate to the generated Word document and use the right mouse button to select Open Externally to open up Microsoft Word. We will see the following screen:

To design reports with Microsoft Word, the Developer tab needs to be exposed, as explained in Chapter 1, Introduction to Business Central.

On the right-hand side of the screen is the XML Mapping, which can be activated from the Developer ribbon:

Let's start by adding the report name to our layout. Expand NavWordReportXMLPart and then expand labels. Right-click on the ReportTitle menu and then click on Insert Content Control, as shown in the following screenshot:

Next, we will add some fields to the...

Report building – phase 3

Finally, we are ready to lay out the data display portion of our Radio Shows by Type report. The first step of this phase is to lay out the fields of our controlling DataItem data in such a way that we can properly group the subordinate DataItems' data.

Once again, open the Microsoft Word layout from Visual Studio Code. Put the cursor on a new row at the end of the document and insert a table with two rows and a few columns, as shown in the following screenshot:

Select the second row in the Microsoft Word table and make it a repeater by clicking on the RadioShow DataItem in the XML Mapping pane and selecting Repeating:

In the second-row columns, we will select the fields from XML Mapping, as shown in the following screenshot:

In the first-row columns, we will add captions. If you want to, you can make the table look nicer by using the design options in the Microsoft Word ribbon:

If we save the Word document and publish our solution...

Modifying an existing report with Report Designer or Word

The basic process we must follow to modify an existing report is the same, regardless of whether the report is one of the standard reports that comes with Business Central or a custom report that we are enhancing in some way. An important discipline to follow in all cases where we are modifying a report that has been in production is not to work on the original, but on a copy. In fact, if this is a standard report we are customizing, we should leave the original copy alone and do all our work on a copy. Not only is this safer because we will eliminate the possibility of creating problems in the original version, but it will make upgrading easier later on. Even when working on a new custom report, it is good practice to save intermediate copies for backup. This allows you to return to a previous working step should the next development step not go as planned.

While it is certainly possible to add a new layout to an existing dataset...

Request page

A request page is a page that is executed at the beginning of a report. Its presence or absence is under the developer's control. A request page looks similar to what's shown in the following screenshot, which is based on one of the standard system reports, that is, the CUSTOMER - ORDER DETAIL report, report 108:

There are three FastTabs in this page. The Customer and Sales Order Line FastTabs are tied to the data tables associated with this report. These FastTabs allow the user to define both data filters and flow filters to control report processing. The Options FastTab exists because the software developer wanted to allow some additional user options for this report.

Adding a request page option

Because we have defined the default sort sequences (DataItemTableView), except for the first DataItem, and we have not defined any requested filters (ReqFilterFields), the default request page for our report has only one DataItem FastTab. Since we have not defined any processing options that would require user input before the report is generated, we have no Options FastTab.

Our goal now is to allow the user to optionally input text that will be printed at the top of the report. This could be a secondary report heading, instructions on interpreting the report, or some other communications to the report reader. To add this capability, perform the following steps:

1. Open Report 50101 in Visual Studio Code.
2. Add a global variable named UserComment with a data type of Text. We will not define the Length field, as shown in the following screenshot; this will allow the user to enter a comment of any length:

3. Add this variable as a data column to be passed to Visual...

Processing-only reports

One of the report properties we reviewed earlier was ProcessingOnly. If that property is set to Yes, then the report object will not output a dataset for displaying or printing—it will simply process the data we program it to process. The beauty of this capability is that we can use the built-in processing loop of the Business Central report object, along with its sorting and filtering capabilities, to create a variety of data updating routines with minimal programming. Use of report objects also gives us access to the request page to allow user input and guidance for the run. We could create the same functionality using code unit objects and by programming all of the loops, filtering, user interface request pages, and so on, ourselves. However, with a processing-only report, Business Central gives us a lot of help, and makes it possible to create some powerful routines with minimal effort.

At the beginning of the run of a processing-only report, there...

Creative report plagiarism and patterns

In the same fashion as we discussed regarding pages in Chapter 4, Pages – The Interactive Interface, when we want to create a new report of a type that we haven't made recently (or at all), it's a good idea to find another report that is similar in an important way and study it. We should also check if there is a Business Central pattern defined for an applicable category of report. As the minimum, in both of these investigations, we will learn how the developers of Business Central solved a data flow, totaling, or filtering challenge. In the best case, we will find a model that we can follow closely, respectfully plagiarizing (copying) a working solution, thus saving ourselves much time and effort.

Often, it is useful to look at two or three of the standard Business Central reports for similar functions to see how they are constructed. There is no sense in reinventing the design for a report of a particular type when someone...

Summary

In this chapter, we focused on the structural and layout aspects of Business Central report objects. We studied the primary structural components, data, and format, along with the request page. We also experimented with some of the tools and modestly expanded our WDTU application.

In the next chapter, we will begin exploring the key tools that pull the pieces of the Visual Studio Code developer experience and the AL programming language together.

Questions

1. The following are defined in Visual Studio Code (choose three):
   • DataItems
   • Field display editing
   • Request Page
   • Database updating
2. Reports can be set to ProcessingOnly status dynamically by AL code. True or false?
3. Reports are fixed displays of data that's extracted from the system, designed only for hardcopy output. True or false?
4. Business Central report data flow includes a structure that makes "child" DataItems fully processed for each record that's processed in the "parent" DataItem. What is the visible indication that this structure exists in a report Dataset Designer form (choose one)?
   • Nesting
   • Indentation
   • Linking
5. Queries can be designed to directly feed the Visual Studio Report Designer. True or false?
6. Union Joins are available using a special setup parameter. True or false?
7. A report that only does processing and generates no printed output can be defined. True or false?
8. Which of the following are properties of queries (choose two)?
   • TopNumberOfRows...