Creating a report in Business Central

Because our Business Central report layouts in this chapter will all be developed in Microsoft Excel, 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 Excel part. If you are already a Microsoft Excel expert, you won’t need to spend much time on this part of the book. If you know little or nothing about the Microsoft Excel 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 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.

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 which standard Business Central report designs can be borrowed for our work, and where we are better off starting from scratch. Of course, we should always check to see if there is a pattern that is applicable to the situation that we are working on.

Report building – phase 1

Our goal is to create report 50101 "Shows By Type" for our WDTU data, which will give us a list of all the scheduled radio show instances that have been organized within Radio Show by Radio Show Type. For the layout part, we will use Excel.

We will begin with adding a new file named ShowsByType.report.al and using the treport snippet. Tabbing through the file, we will first adjust the report ID to 50101 and the name to Shows By Type. The snippet has provided us with one dataitem and column control each, which we can now delete. A better way to add DataItems is to simply use IntelliSense. Type the letter D, and the snippet for dataitem will appear. Hit Tab, type a reference name, and then the target table name (in this case, Radio Show Type, then Radio Show, and then Playlist Header).

The basic dataset will look as follows:

Figure 5.18 – Indented data items indicating the intended parent/child relationship

In the rendering control, we will specify the Excel layout as follows:

layout(ExcelLayout)
{
    Type = Excel;
    LayoutFile = './ShowsByTypeExcel.xlsx';
    Caption = 'Excel Layout';
    Summary = 'Shows by Type Excel';
}

Then, we will declare its layout name as the default rendering layout in the report properties:

DefaultRenderingLayout = ExcelLayout;

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 three screenshots provide an example minimal set of data. The first one is a copy from Chapter 2, Figure 2.20, where we already had entered some Radio Show Types:

Figure 5.19 – The Radio Show Type sample data

Radio Show List could be populated like this:

Figure 5.20 – The Radio Show sample data

As the third step, we enter the following rows into Playlist Document List:

Figure 5.21 – The Playlist Document List sample 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 because we aren’t building any processing logic, and we don’t have any complex relationships to address. We just want to create a nice, neat, nested list of data.

The next step is to define the data fields we want to be available for processing and in the layout:

Figure 5.22 – Nested DataItems with columns for data

Each of the subordinate nested DataItems must be properly linked to its parent DataItem. The parent DataItem is called out explicitly by using DataItemLinkReference, allowing for multiple child DataItems at the same level. The PlaylistHeader DataItem is joined to the RadioShow DataItem by the "Radio Show No." and "No." fields. The RadioShow DataItem is joined to the RadioShowType DataItem by the "Radio Show Type" and RadioShowType fields, that is, Code. The RadioShow portion of the dataset that’s returned is limited by setting the PrintOnlyIfDetail value to true, as shown in the following screenshot. This choice will cause the RadioShow record to not be sent to output for reporting if no subordinate PlaylistHeader records are associated with RadioShow:

Figure 5.23 – Subordinate DataItems linked with DataItemLink

In Figure 5.22 and Figure 5.23, there is an additional feature that should be explained. Column data sent to a layout does not necessarily need to be directly related to table data but can be variables or calculations. Note the calculation in the Hours column. In order for the data to be displayed in an hour:minute format in Excel, the data must be converted from the Business Central standard of milliseconds to a 24-hour fraction. The numerical factoring is 86,400,000.

The other data that we can pass to Microsoft Excel are the labels. Labels will be used later as captions in the report and are enabled for multi-language support. Let’s create a title label and column headings that we will hand over the fence to the layout. Go to the end of the .al file and type L or use Ctrl + the spacebar:

:

Figure 5.24 – Labels show as Captions in generated Excel Workbook

Now that we have our AL dataset definition completed, we should save and publish our work before doing anything else.

Figure 5.25 – Don’t forget UsageCategory so it is searchable!

Then, before we begin our layout work, it’s a good idea to check that we don’t have some hidden error that will get in our way later. The easiest way to do that is just to run what we have now. What we expect to see is a basic Shows by Type request page display, allowing us to run a report with an Excel layout defined.

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 Excel, we must compile and build the app. Then, we need to navigate to the generated Excel document and use the right mouse button to select Open Externally in order to start editing in Microsoft Excel. We will see the following screen:

Figure 5.26 – Open the generated Excel file directly from VS Code

The Excel workbook will have four sheets: Data, TranslationData, CaptionData, and Aggregated Metadata. Aggregated Metadata will not show when the Excel sheet is generated from Business Central by a user. The two tabs we will focus on are the Data and CaptionData tabs.

Figure 5.27 – The Data and CaptionData tabs generated by Visual Studio

Click the plus symbol at the bottom of the screen to the right of the auto-generated sheets to create Sheet1, and rename it Shows by Type FILTER. First, we are going to add the report title by referencing the label defined in the AL code. This label is in the CaptionData tab. The order of the labels in Excel will be the same as defined in the dataset; we know ReportTitle is going to reside in the first cell under the Value heading. In cell A2, use the FILTER function to limit the Value column on a predetermined Caption. Note that since there is no data available yet in this template, the field will be empty after entering the formula.

Figure 5.28 – Use of FILTER with sheet and column names is preferred

This isn’t especially impressive, but it’s not bad if this is your first try at creating a Business Central report. In general, it is better to reference the sheet name and XML column heading rather than referencing exact cells, the exception being a caption that is repeated in the Data tab. The FILTER function is also useful to return multiple rows of data.

We could experiment with various properties of the heading fields, choosing different fonts, bolding, colors, and so on. As we only have a small number of simple fields to display (and could recreate our report if we have to do so), this is a good time to learn more about some of the report appearance capabilities that Microsoft Excel provides.

Report building – phase 3

Finally, we are ready to lay out the data display portion of our Shows by Type report. The first step of this phase is to decide how to lay out the data in the report. The two methods we can choose from are using the pivot and suggested chart, and using the FILTER function to list repeated data.

Open the Data sheet. In the Excel actions, select the Insert ribbon and select PivotTable | From Table/Range.

Figure 5.29 – Create PivotTable

In the PivotTable from table or range screen, leave Table/Range on Data and New Worksheet:

Figure 5.30 – PivotTable from table or range screen

This will create Sheet2 (renaming it Shows by Type PIVOT) with the PivotTable Fields selection screen showing. Select Type, Title, and Hours. Type and Title should go in Axis (Categories) or Axis (Rows), depending on the Microsoft Excel version, and Hours should appear as Sum of Hours in Values. The table will have the titles grouped by show type, with the hours subtotaled and grand totaled. Next, we will add a pie chart. To do this, click the Recommended Charts icon in the Insert ribbon and select the pie chart.

Both the pivot table and the chart will be interactively linked such that you can show or hide titles or types and they will update on the fly.

Your tab should now resemble the one on the following screenshot:

Figure 5.31 – Sheet by Type PIVOT with selections

Add a new tab/worksheet and rename it Shows by Type FILTER. This sheet will have the following elements: the report title caption, table column captions, and repeating rows of data. The FILTER function in Microsoft Excel displays all the values in an array in a repeating table. The report title and table column captions will only return single values, whereas the data array will return as many rows as are returned in the Data sheet.

The FILTER function in Excel filters an array based on a true/false statement:

=FILTER(array,include,[if_empty])

Argument	Description
array (required)	The array or range to filter
include (required)	A Boolean statement based on an array with the same dimensions as the first parameter
if_empty	The value of the return if all values in the included array are empty or nothing

Table 5.1 – Excel FILTER function arguments

For the report title, we use the exact same function as we did on the Shows by Type PIVOT sheet. Don’t forget to bold the title cell using the standard Excel font formatting. For the table column captions, we will use the same filter but with the Caption filter to match each column. Feel free to format the table headings to make the column headings stand out.

Figure 5.32 – Repeater table column caption

For the repeater portion, we will use the FILTER function. The generated list will show the values from the Data tab as long as the primary dataset column (Code_RadioShowType) is not blank. Otherwise, it will display a blank cell.

Figure 5.33 – Repeater row FILTER statement

To make the Hours column more readable, format the cells likely to be populated (B4 to B6 in the Shows by Type PIVOT sheet and below C3 in the Shows by Type FILTER sheet) with the h:mm time format.

Figure 5.34 – Format the Hours data in Excel to be easier to read

Save the Excel template and then build and publish the extension to Business Central. Search for the report using Shows by Type and run it.

On the request page, click Download and save the generated report locally.

Figure 5.35– The Shows by Type request page

Now, you can review your two sheets with data from Business Central. The first one is the Shows by Type PIVOT sheet:

Figure 5.36 – The Shows by Type PIVOT sheet

In general, for analytical reports with subtotals and totals, pivot tables and related charts are most likely the method of choice. For more list or table-driven reports based on repeater or static elements, use FILTER or other reference functions to the data and caption sheets, as we did in the Shows by Type FILTER sheet:

Figure 5.37 – The Shows by Type FILTER sheet

Depending on what we want to achieve, it may be preferable to not create a new report from scratch but to extend an existing one from Business Central. The decision depends on some factors that the next section will cover.

Extending an existing report with SQL Report Builder or Microsoft Office

The decision to extend an existing report in Business Central or create a custom report is dependent on several factors:

• Are you just looking to just create a different layout (RDLC, Word, or Excel)? Extend the report.
• Are you only going to add columns or DataItems without radically altering the report flow? Extend the report.
• Is there new or additional logic, triggers, or processing required not related to events available? Create a copy in the custom object range.

In most cases where anything more than the most basic changes, it is best practice to make the necessary modifications to a copy of the original. Not only is this safer because it eliminate the possibility of creating problems with the original version, but it will make upgrades easier. On the other hand, as our application is updated we must also remember to manually copy any interim improvements or bug fixes from the original report.

A vast majority of Business Central report layouts can be modified by a developer using SQL Report Builder because most standard reports have been developed with RDLC layouts. Over thirty standard reports also have Word layouts available in the initial distribution of Business Central. These include reports 1304 "Standard Sales - Quote", 1305 "Standard Sales - Order Conf.", 1306 "Standard Sales - Invoice", and 1307 "Standard Sales - Credit Memo". Less than 10 reports have Excel layouts, but they include aged accounts, trial balance, and customer/vendor top trends reports ranging from ID 4402 to 4409.

Note

As the Excel reports have been published with the Dynamics BC Excel Reports extension, we cannot find them in the AL Explorer in Visual Studio Code. In environments where this extension has been installed, we can find them and export their layout on the Report Layouts client page.

It is likely that future releases of Business Central will have additional report layouts available both in Word and Excel format. In the meantime, if we want other reports, whether they be standard or custom, to have Word or Excel layout options available, we will have to create them ourselves. The primary advantage of having Word or Excel layout options for reports is to allow for the modification of the layouts by a trained user or developer. As the modifications must still conform to good (and correct) report layout practices, appropriate training, careful work, and considerable common sense are needed to make such modifications, even though the tool is Microsoft Office.

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, the Customer – Order Detail report, report 108:

Figure 5.38 – Request page for a report object

There are three FastTabs on this page. The Options FastTab exists because the software developer wanted to allow some additional user options for this report. The Filter: Customer and Filter: 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.

Adding a request page option

Because in our report we have defined the default sort sequences (DataItemTableView), except for the first DataItem, and we have not defined any requested filters (RequestFilterFields), 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 “Shows by Type” 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:

Figure 5.39 – Global variables defined below the Layout section

3. Add this variable as a data column. column must be a subordinate of the DataItem. We do not need a caption defined as we will use the variable name for this field in the report layout.

Figure 5.40 – column is of a variable, not a table field

4. Add the request page to the report, between the dataset and rendering sections.

Figure 5.41 – Building the request page definition

5. Save and compile the report by running this Visual Studio Code command: AL: Package.
6. Access Microsoft Excel through Open Externally.
7. On both the PIVOT and FILTER sheets, just below the report title, select the cell.
8. Enter the following function into the cell: =Data[@UserComment].
9. Save the Excel workbook and exit Microsoft Excel. Then, publish the project.
10. Run Shows by Type.

To test the updated request page, we need to run the report in the client.

Running the report

There are several ways to run reports during development. The most obvious way is to build and publish the extension to Business Central and use the Tell Me feature. There are additional methods without using Tell Me, which are useful if you have not or cannot set the UsageCategory and ApplicationArea properties to make an object searchable. Regardless of the method the report is called, it is still required that the code is published to the environment. The most basic is to use the URL. The syntax for running an object is as follows: https://businesscentral.dynamics.com/<environment name>]/?[company=<companyname>]&[page|query|report|table=<ID>].

An example to run our report would be https://businesscentral.dynamics.com/Sandbox/?company=CRONUS%20International%20Ltd.&report=50101.

You can also run an object directly from the Visual Studio Code project using AL Explorer. Select the object you want to view and click Run:

Figure 5.42 – AL Explorer will execute the direct URL to an object

Within the client itself, you can use Tell Me to navigate to the Report Layout page. From this list, it is possible to use the Run Report action. If during development it is desired to immediately review a table, page, report, or query object, it is possible to use the launch.json file. There are two properties that must be used in conjunction with each other: startupObjectType and startupObjectId.

No matter which of the previous methods are utilized, the request page will display the new option to enter a comment.

Figure 5.43 – The Shows by Type request page with User Comment

The report heading then shows the comment in whatever font, color, or other display attribute the developer has defined:

Figure 5.44 – The comment variable is passed in the dataset

Because we didn’t specify a maximum length on our UserComment field, we can type in as much information as we want. Try it out; type in an entire paragraph for a test.

Processing-only reports

One of the report properties we reviewed earlier was ProcessingOnly. If that property is set to true, 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. The 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 is very little user interface variation compared to a normal printing report. The processing-only request page looks much like it would for a printing report, except that the Print and Preview options are not available. Everything else looks the same. Of course, we have a big difference of no visible output at the end of processing.

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. At 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 else has already invented a version of it. Not only that, but they have provided us with the plans and given us the AL code, as well as the complete structure of the existing report object.

When it comes to modifying a system such as Business Central, copying from an existing source is a very effective research and design tool. In the case of reports, our search for a model may be based on any of several key elements. We may be looking for a particular data flow approach and find that the Business Central developers used the Integer table for some DataItems (as many reports do).

We may need a way to provide some creative filtering, similar to what is done in an area of the standard product. We might want to provide users options to print either detailed or a couple of different levels of totaling, with a layout that looks good, no matter which choice the user makes. We may be dealing with all three of these design needs in the same report. In such a case, it is likely that we are using multiple Business Central reports as our models: one for this feature, another for that feature, and so forth.

If we have a complicated, application-specific report to create, we may not be able to directly model our report on a model that already exists. However, often, we can still find ideas in standard reports that we can apply to our new design. We will almost always be better off using a model rather than inventing a totally new approach.

If our design concept is too big a leap from what was done previously, we should consider what we might change in our design so that we can build on the strengths of AL and existing Business Central routines. Creating entirely new approaches may be very satisfying (when it works) but, too often, the extra costs exceed the incremental benefits.

Reports extract data using a looping mechanism in order to extract data from multiple tables. Each loop, related dataitem, and even record variable usage would consist of a separate SQL statement sent to the database. This is not always the most efficient method, and Business Central offers an option that is much closer to using Transact SQL statements: the query object.