Introduction
The report dataset and the visual design are the two basic components of a report object. The dataset definition is done first when making a report, then the visual design. Report data is defined in AL code. You can use Word or Excel for a Report Definition Language (RDL) layout and Visual Studio Report Designer or Microsoft SQL Server Reporting Services Report Builder for a Word or Excel layout. The Business Central Web client can then be used to share a report you've created with other programmes. You may utilise a single table or aggregate data from numerous tables to build a report. This tutorial will demonstrate how to create a report using information from various tables.
You will learn
- Instances of more than one table need a dataset definition.
- Extending a data record by including new fields.
- Setting parameters for the information objects.
- Including headings in a document.
- Layout a report using the RDL (client report definition) format in Visual Studio 2019.
- Using filters in a report to hide blank rows and fields.
- Making a report and having it run.
Case Study
ABC International Ltd. is fortunate to have Gomathi, a talented programmer, on staff. The boss has ordered Gomathi to compile a report based on information gathered from the below tables, columns used
Required Report Layout
- Information about the customers must be displayed at the beginning of the report.
- The report needs to display a listing of journal entries for each customer.
- The report has to display a list of subsidiary ledger entries under the ledger entries for each ledger entry.
- The report must present the selected customer's basic sales document headers.
- A new page must be started for each new section of information for each customer.
- Each customer's sum should be shown in the Cust. Ledger Entry table's Amount field.
- A report's data parts should not be displayed if they contain no records. If there are no sales records for a given customer, for instance, the corresponding sale header field must be omitted.
- Input fields for monetary amounts must not show the number 0.
- The document should be landscape-oriented.
Properties used in the Code
 |
Note: Install report builder from https://www.microsoft.com/en-in/
Code
report 50101 "Report for Multiple Tables"
{
UsageCategory = Administration;
ApplicationArea = All;
defaultlayout = RDLC;
RDLCLayout = 'MyRDLReport.rdl';
dataset
{
dataitem(Customer; Customer)
{
DataItemTableView = Sorting("No.");
RequestFilterFields = "No.";
PrintOnlyIfDetail = True;
column(No_Customer; "No.")
{
IncludeCaption = true;
}
column(Name_Customer; Name)
{
IncludeCaption = true;
}
column(Phone_Customer; "Phone No.")
{
IncludeCaption = true;
}
column(Address_Customer; Address)
{
IncludeCaption = true;
}
column(EMail_Customer; "E-Mail")
{
IncludeCaption = true;
}
dataitem(CustLedger; "Cust. Ledger Entry")
{
DataItemTableView = sorting("Entry no.");
DataItemLink = "Customer No." = field("No.");
column(EntryNo_CustLedgerEntry; "Entry No.")
{
IncludeCaption = true;
}
column(CustomerNo_CustLedgerEntry; "Customer No.")
{
IncludeCaption = true;
}
column(PostingDate_CustLedgerEntry; "Posting Date")
{
IncludeCaption = true;
}
column(DocumentType_CustLedgerEntry; "Document Type")
{
IncludeCaption = true;
}
column(DocumentNo_CustLedgerEntry; "Document No.")
{
IncludeCaption = true;
}
column(Description_CustLedgerEntry; Description)
{
IncludeCaption = true;
}
column(CurrencyCode_CustLedgerEntry; "Currency Code")
{
IncludeCaption = true;
}
column(Amount_CustLedgerEntry; Amount)
{
IncludeCaption = true;
}
column(OriginalAmtLCY_CustLedgerEntry; "Original Amt. (LCY)")
{
IncludeCaption = true;
}
column(RemainingAmtLCY_CustLedgEntry; "Remaining Amt. (LCY)")
{
IncludeCaption = true;
}
dataitem(DetCustLedger; "Detailed Cust. Ledg. Entry")
{
DataItemTableView = sorting("entry no.");
DataItemLink = "Customer No." = field("Customer No. "), "Cust. Ledger Entry No." = field("Entry No.");
column(EntryNo_DetailedCustLedgEntry; "Entry No.")
{
IncludeCaption = true;
}
column(EntryType_DetailedCustLedgEntry; "Entry Type")
{
IncludeCaption = true;
}
column(PostingDate_DetailedCustLedgEntry; "Posting Date")
{
IncludeCaption = true;
}
column(DocumentType_DetailedCustLedgEntry; "Document Type")
{
IncludeCaption = true;
}
column(DocumentNo_DetailedCustLedgEntry; "Document No.")
{
IncludeCaption = true;
}
column(AmountLCY_DetailedCustLedgEntry; "Amount (LCY)")
{
IncludeCaption = true;
}
column(TransactionNo_DetailedCustLedgEntry; "Transaction No.")
{
IncludeCaption = true;
}
column(JournalBatchName_DetailedCustLedgEntry; "Journal Batch Name")
{
IncludeCaption = true;
}
column(DebitAmountLCY_DetailedCustLedgEntry; "Debit Amount (LCY)")
{
IncludeCaption = true;
}
column(CreditAmountLCY_DetailedCustLedgEntry; "Credit Amount (LCY)")
{
IncludeCaption = true;
}
}
}
dataitem(SalesHeader; "Sales Header")
{
DataItemTableView = sorting("Document Type", "No.");
DataItemLink = field("No.", "Sell-to Customer No. ");
column(DocumentType_SalesHeader; "Document Type")
{
IncludeCaption = true;
}
column(No_SalesHeader; "No.")
{
IncludeCaption = true;
}
column(PostingDate_SalesHeader; "Posting Date")
{
IncludeCaption = true;
}
column(PricesIncludingVAT_SalesHeader; "Prices Including VAT")
{
IncludeCaption = true;
}
column(Amount_SalesHeader; Amount)
{
IncludeCaption = true;
}
}
}
}
labels
{
Sales_Document_Caption = 'Sales Documents';
Total_Caption = 'Total';
}
}
Execute the code
1. Press Ctrl+s to save the code
2. To create the MyRDLReport.rdl file, build the extension (Ctrl+Shift+B), and then open the file in Visual Studio 2019.
Note: You can see the RDL report created in the file explorer. Right-click and select Open externally. It will open in the Report builder.
Creating the Report's Visual RDL Layout
To continue, Gomathi will use Visual Studio Report Designer to create the report's RDL layout. She will format the report, add the data, and then configure settings for the report and its elements.
Design the RDL layout for the report
- Select Add Page Header from the shortcut menu when you right-click anywhere outside the report (in the dark area).
- Report Properties can be selected by performing a right-click anywhere outside the report (in the shaded area).
- Pick the Page Setup tab in the Report Properties window. In the Paper size section, under Orientation, pick Landscape, and then click the OK button.
- On the View menu, choose Toolbox. To include the List control in the report, first choose the body of the document. The data will be collected and grouped in this control.
- The List control should be positioned at the top of the report body and expanded to fill the whole area.
- To access the Rectangle Properties, right-click the centre of the List controls and then click the box.
- Select Cornflower Blue from the colour palette in the Fill Colour list on the Fill tab of the Rectangle Properties window, and then click OK. Any colour is an option.
Gomathi will group the data by Customer No. and configure how the groupings should be shown before setting the attributes of the List control that will hold the dataset.
To set the list control properties
- Right-click the border that is tinted to the left of the List control while it is selected, then choose Tablix Properties.
- Select DataSet_Result from the drop-down list under Dataset name on the General tab of the Tablix Properties window, and then click the OK button.
- Right-click the shaded border to the left of the list control, select Row Group, and then select Group Properties after selecting the List control.
- Choose the Add button in the Group Properties window's General tab's Group expressions: section, then pick [No_Customer] from the Group on: drop-down list. By customer number, this organises each piece of information in the List control.
- Select Between each instance of a group under the Page Breaks tab, then click OK.
Gomathi is able and prepared to enter the requisite customer information at this time. Due to the fact that only one customer will be shown at a time, Gomathi has to group all the columns containing information about the customer into header rows. Disabling the table's data and footer rows.
To add customer data
- Drag a Table control from the Toolbox pane into the List control, then resize the table so that it is roughly half the width of the List control. The client information will be in this table.
The list control and table are depicted in the following example.
The table has two rows: a header row (first row) and a data row (second row), as you can see. The second row's left border's three parallel lines designate the data row.
- To access the Tablix Properties window, right-click any table row's coloured border, select Tablix Properties, and then click OK.
- Make that the Dataset name box on the General tab is set to DataSet_Result, and then click the OK button.
- The table has three columns. Gomathi will add a fourth column to the table to hold all the customer data.
- To add the fourth column to the table, right-click the middle column header, select Insert Column, and then pick Right.
- The data row in the second table row may be deleted by selecting it, performing a right-click, selecting Delete Rows, and then selecting the OK button in the Delete Rows box.
- To add another table header row, select the last table row, right-click the left shaded border, choose Insert Row, and then select Below.
- To add a third row of table headers, repeat step 6. The table should now have three header rows.
- To open the Expression window, right-click the first cell in the table (row 1, column 1). Then select Expression.
- Double-click No_CustomerCaption in the Values column after selecting Parameters in the Category and Item columns and making sure All is chosen in the Item column. Make sure the following value is included in the Set expression for: Value box: =Parameters!No_CustomerCaption.Value. In the report, this cell will show the customer no. caption.
- Put =First(Parameters!No_CustomerCaption.Value) in the expression instead. selecting the OK button.
- To open the Expression window, right-click the second cell in the table (row 1, column 2). Then select Expression.
- Select Field(DataSet_Result) in the Category column, make sure All is chosen in the Item column, and then double-click No_Customer in the Values column. Check that the value =Fields is present in the Set expression for: Value box.No_Customer.Value. selecting the OK button. The Customer No. will be shown in this cell.
- To input captions and values in the next cells, repeat steps 8 through 12.
- The titles will be in columns 1 and 3, while the values will be in columns 2 and 4.
- To open the Properties box in Visual Studio, select all of the table's rows, not the entire table, from the View menu.
Set the BackgroundColor setting to Plum under Fill in the Properties window. Any colour is an option.
The layout that Gomathi has designed to this point resembles the following illustration.
- To construct the project, select construct Web site from the Build menu. Ensure there are no build problems by checking the Output window. Close Visual Studio.
Note
Building the project frequently while the report is being designed is a good idea to check for build issues.
Gomathi will run the report and give a brief overview of his previous work.
- Reload the window by returning to your project in Visual Studio Code.
- Set the "startupObjectId" to the Id of the report object and the "startupObjectType" to Report in the launch.json file.
- To run the report, use the F5 key.
- Select the Preview button to examine the report on the request page that appears. On the first page, the first consumer is seen. The report has individual pages for each customer, which you may view by panning over it.
Gomathi is currently filling up the information for the client ledger and the general ledger. The information will be moved to a new table.
To provide the necessary data for both ledger entry and detailed ledger entry
- Microsoft Visual Studio should now display the MyRDLReport.rdl report.
Drag a table control into the list control from the Toolbox. Place the table below the one holding the client information.
Note
The report body and the list controls might need to be resized in order to be bigger.
- Right-click the darkened border of the selected table and choose Tablix Properties. Make that the Dataset name box on the General tab is set to DataSet_Result, and then click the OK button.
- Insert Row, Outside Group - Below, and the table data row should be selected. This increases the table's data row count. One header row and two data rows are now present.
- Remove the table's first row (the header row), then add additional columns to make a total of eleven columns.
- Right-click the left shaded boundary of the first data row, select Add Group, and then select Parent Group.
- When choosing Group by in the Tablix group box, choose EntryNo_CustLedgerEntry from the drop-down menu. After choosing Add group heading, select the OK button.
- Insert Row from the context menu by clicking the first row, followed by Inside Group - Above. The captions for the Customer Ledger entries will be contained in this heading.
- To open the Expression window, right-click the cell in row 1, column 2, and then select Expression.
- Double-click EntryNo_CustLedgerEntryCaption in the Values column after selecting Parameters in the Category column. Take note that the following value is included in the Set expression for: Value box: =Parameters!EntryNo_CustLedgerEntryCaption.Value
- Change the formula to =First(Parameters!EntryNo_CustLedgerEntryCaption.Value).
- To add captions for the remaining table cells in the first row, repeat steps 9 through 11 as seen in the accompanying table.
| Column | Caption expression |
|---|---|
| 3 | CustomerNo_CustLedgerEntryCaption |
| 4 | PostingDate_CustLedgerEntryCaption |
| 5 | DocumentType_CustLedgerEntryCaption |
| 6 | DocumentNo_CustLedgerEntryCaption |
| 7 | Description_CustLedgerEntryCaption |
| 8 | Skip this cell. You will use this cell later. |
| 9 | CurrencyCode_CustLedgerEntryCaption |
| 10 | Amount_CustLedgerEntryCaption |
| 11 | OriginalAmtLCY_CustLedgerEntryCaption |
| 12 | RemainingAmtLCY_CustLedgerEntryCaption |
- In the table, right-click the grouping cell on the left that includes the EntryNo_CustLedgerEntry field, choose Text Box Properties, choose the Visibility tab, and then click the Hide option under the Change display choices section.
- Set the BackgroundColor property for the first row in the table to Dim Grey in the Properties pane's Fill section.
- To open the Expression window, right-click the cell in row 2, column 2, and then select Expression.
- Choose Fields (DataSet_Result) from the Category column, double-click EntryNo_CustLedgerEntry in the Values column, and then press the OK button. Take note that the value =Fields is included in the Set expression for: Value box!EntryNo_CustLedgerEntry.Value
- In order to add fields from the ledger entry dataset, repeat steps 15 and 16 for row 3. Place the fields beneath the relevant captions.
- Set the BackgroundColor attribute to Silver for the just-filled row.
- Check the Output pane after building the project to make sure there are no build problems.
- Right-click the left-most shaded border of the second table row, select Insert Row, and then select Below. Three group rows, a group data row, and a table footer row should now be included in the table. The captions for the Detailed Cust. Ledg. will be kept in this row. Data entry item.
- As indicated in the accompanying table, add the captions and fields for the Detailed Cust. Ledger Entry table.
| Third row (Caption) | Forth row (Fields) |
|---|---|
| EntryNo_DetailedCustLedgEntryCaption | EntryNo_DetailedCustLedgEntry.Value |
| EntryType_DetailedCustLedgEntryCaption | EntryType_DetailedCustLedgEntry.Value |
| PostingDate_DetailedCustLedgEntryCaption. | PostingDate_DetailedCustLedgEntr.Value |
| DocumentType_DetailedCustLedgEntryCaption | DocumentType_DetailedCustLedgEntry.Value |
| DocumentNo_DetailedCustLedgEntryCaption | DocumentNo_DetailedCustLedgEnt.Value |
| TransactionNo_DetailedCustLedgEntryCaption | TransactionNo_DetailedCustLedgEntry.Value |
| JournalBatchName_DetailedCustLedgEntryCaption | JournalBatchName_DetailedCustLedgEntry.Value |
| AmountLCY_DetailedCustLedgEntryCaption | AmountLCY_DetailedCustLedgEntr.Value |
| DebitAmountLCY_DetailedCustLedgEntryCaption | DebitAmountLCY_DetailedCustLedgEntry.Value |
| CreditAmountLCY_DetailedCustLedgEntryCaption | CreditAmountLCY_DetailedCustLedgEntry.Value |
- Shrink the column that contains the Customer No. field of the Cust. Entry Ledger to around half of its size.
- Select Insert Column from the context menu by right-clicking the column header that includes the Customer No. field, and then select Right.
- The Customer No. caption cell and the newly produced empty cell should both be selected before choosing Merge Cells to combine them.
- To combine the cell that holds the value of the Customer No. field with the newly formed empty cell, repeat step 24.
- Assign the expression from the Detailed Cust. Ledg.'s EntryType caption and field cells. You may now enter the blank cell you just made on the right. The expressions might need to be taken out and pasted into the empty cells.
- Repeat step 26 to reposition the field and EntryNo caption one cell to the right. By doing this, it is ensured that the EntryNo and EntryType data are placed immediately beneath the CustomerNo column.
Under the CustomerNo box in the accompanying image are EntryNo and EntryType cells.
- To add the Transaction No. and Journal Batch Name captions and fields under the Description data, repeat steps 23 through 27. Under the CurrencyCode field, a blank cell is produced as a result.
- Set the BackgroundColor property of the third row to Yellow after which you should set the BackgroundColor of the fourth row to Khaki.
Gomathi will then conceal any unfilled cells and shift the totals to the bottom row. To hide the empty cells, she will construct a filter that selects rows with [EntryNo] values greater than zero..
To hide empty cells and add totals
- Right-click the shaded border to the left of the first row, select Row Group, and then select Group Properties to display group properties.
- Select the Filters tab in the Group Properties box, then click the Add button.
- Expression should be [EntryNo_CustLedgerEntry], Text should be changed to Integer, Operator should be >, Value should be 0, and then the OK button should be selected.
- The other rows in the table are affected by the set filter.
- Make sure [EntryNo_CustLedgerEntry] is present in the Expression box in the Group Properties window's Filters tab.
- If a customer ledger record is not available, Gomathi will now format the cells, add the total of the amount field to the footer row of the table, and hide the total cell.
- Click the empty cell beneath the Amount (LCY) field in the table's last row using the right mouse button, and then choose Expression.
- Double-click Amount_CustLedgerEntry in the Values column of the Category column, choose Fields (DataSet_Result), and then modify the expression in the Set expression to read: Value box set to this value: =Sum(Fields!Amount_CustLedgerEntry.Value). selecting the OK button.
- Find the Format property in the Properties box, click the drop-down arrow, and then pick Expression.
Note
Amount_CustLedgerEntryFormat may also be modified by double-clicking it in the Values field of the Fields(DataSet) category.
- Choose Merge Cells from the context menu after selecting the two empty cells to the left of the total cell.
- Set the caption to Total_Caption by selecting Total_Expression from the context menu when you right-click the combined cell, which is now shown.
- The total and total caption cells' BackgroundColor attribute should be set to Red.
- Even if there are no ledger entries when you run the report right now, the total amount field will still be shown. When there are no ledger entries, Gomathi will add an expression to conceal the footer row.
- In the Properties box, select the final row, navigate to the Hidden property, click the drop-down arrow, and then choose Expression.
- Enter the following expression to conceal the row in the Set expression for: Hidden box in the Expression window: =Fields!EntryNo_CustLedgerEntry.Value = 0. selecting the OK button. When there are no entry values, the row is hidden.
- The last table row's leftmost cell may be selected by right-clicking it, choosing Text Box Properties, the Visibility tab, the Hide option under Change display choices, and finally clicking the OK button.
The information from the Sales Header table will now be included.
To add the sales header data
- Drag a Table control from the Toolbox to the List control, then place it beneath the table that holds the Cust. Ledger Entry table
- To add more columns to the table, simply right-click a column and select "Add Columns."
- Remove the table's initial header row.
- Verify that the DataSet name is set to DataSet_Result by performing a right-click on the data row, selecting Tablix Properties, and then clicking the OK button.
- To open the Tablix group window, right-click the data row, select Add Group, and then select Parent Group.
- To access the Expression window, select the Group by: option and then the fx button.
- Select Parameters in the Category column, then click twice on Sales_Document_Caption in the Values column. Make sure the following value is included in the Set expression for: Value box: =Parameters!Sales_Document_Caption.Value. selecting the OK button.
- Select Add group header in the Tablix group panel, then press the OK button.
- Select Insert Row from the context menu by right-clicking the table's top row, and then select Inside Group - Above.
- Reduce the first column's size before setting the Hidden property in the Properties window's Visibility section to True. This conceals the top column.
- Merge all the cells in the first table row, excluding the first grouping cell.
- Expression can be selected by performing a right-click on the combined cell.
- Select Parameters in the Category column, then click twice on Sales_Document_Caption in the Values column. Make sure the following value is included in the Set expression for: Value box: =Parameters!Sales_Document_Caption.Value. selecting the OK button.
- Put =First(Parameters!Sales_Document_Caption.Value) in the expression instead.
- To open the Expression window, right-click the cell in row 2, column 2, and then select Expression.
- Double-click DocumentType_SalesHeader in the Values column after selecting Parameters in the Category column. Make sure the following value is included in the Set expression for: Value box: Parameters!DocumentType_SalesHeaderCaption.Value.
- Click the OK button after changing the equation to =First(Parameters!DocumentType_SalesHeaderCaption.Value).
- Select Expression from the context menu by right-clicking the cell that is beneath the newly formed caption. Select Fields (DataSet_Result) in the Category column, and then double-click DocumentType_SalesHeader in the Values column. selecting the OK button. Make sure the following value is included in the Set expression for: Value box: =Fields!DocumentType_SalesHeader.Value
| Caption | Corresponding field |
|---|---|
| No_SalesHeaderCaption | No_SalesHeader |
| PostingDate_SalesHeaderCaption | PostingDate_SalesHeader |
| PricesIncludingVAT_SalesHeaderCaption | PricesIncludingVAT_SalesHeader |
| Amount_SalesHeaderCaption | Amount_SalesHeader |
- Set the BackgroundColor property to Lime in the Properties box after selecting the first two rows.
- Set the BackgroundColor property to Turquoise in the Properties window by selecting the data row (last row).
- At this point, Gomathi will create a filter to conceal empty rows.
To set a filter hide empty row
- Right-click the shaded border to the left of the table, choose Tablix Properties, and then select any row from the table.
- Select the Filters tab before selecting the Add button.
- Select No_SalesHeader, set Operator to >, set Value to 0, and then click OK in the Expression list box.
- Save the report.
Building and Running the Report
Gomathi will run the report to view what it looks like. For this, do the following:
- Ensure that the launch.json file's "startupObjectId" and "startupObjectType" are both set to the Id for the report object.
- To create and execute the report in Dynamics 365 Business Central, use the F5 key.
- The Web Client will display a request page if the UseRequestPage Property has not been deactivated.
The request page that appears when the report is executed is demonstrated in the following illustration.
The request page's Preview button will display the report with the newly constructed RLD layout when you select it.
Gomathi may now upgrade the report with new features. She can add features like having the report's firm name and emblem appear on each page. Additionally, she might want to include tools that let users adjust filters on the request page.
Conclusion
This blog has shown us how to
- Create a report from multiple tables
- To create a calculated field
- To format the RDL report
- To layout the RDL report
Reference
Microsoft Learn
5 Comments
Fantastic job madam I like it
ReplyDeleteGlad Deepak. Thank you.
DeleteThank you for all the videos and topics, it has really helped me. Please how can I do this in C/AL Nav dev environment: Checksum = Base64(Bcyrpt(ConcatString)); I will really appreciate it if you can help me
ReplyDeleteif i have created filed in sales line and want to insert in sales invoice line then how to write code in business central, please suggest
ReplyDeleteand where can write code in BC?
ReplyDelete