This walkthrough includes examples of reports that are more complicated than those described in earlier walkthroughs. These examples are not intended to be complete or ready to run, but are meant to give you examples that might be useful when designing your own reports.
Prerequisites
To complete this walkthrough, you will need:
-
Microsoft Dynamics NAV installed with a developer license.
-
The CRONUS International Ltd. demo data company installed.
Using Virtual Tables as the Data
Model
C/SIDE includes a number of virtual tables, such as the Integer table and the Date table. The Date table consists of five fields: Period Type, Period Start and Period End, Period No. and Period Name. Period Type can be Date, Week, Month, and so on. Period Start is the starting date of each period and Period End is the last date in the period. Period End dates are closing dates.
In this example, the Date table is used to create a report that prints information from the Cust. Ledger Entry table. For each day in a range of dates that can be chosen by the user when they want to run the report, the report summarizes the entries made on that date.
The report lists the different types of documents that were entered, the number of documents of each type, and the total number of documents entered on each date. The report also displays the total amount entered on each date. Finally, the total number of entries and the total amount entered for the selected date range is printed at the bottom of the report.
You could create the report by grouping the entries according to the Cust. Ledger Entry table alone, but the field that contains the posting date in that table is not part of any key. Creating a special key just for this report is not advisable, because it would slow down all the other transactions that involve this table. Every sales entry would be affected.
This data model contains two data items—one based on the Date table and one based on the Cust. Ledger Entry table.
To use virtual tables as the data model
-
Open Report Designer and create a new blank report with two data items with the Date and the Cust. Ledger Entry tables as the underlying tables.
-
Indent the Cust. Ledger Entry data item.
NoteIndenting the Cust. Ledger Entry data item ensures that the system only searches through the Cust. Ledger Entry table when a date is found in the Date table, that lies within the range specified by the user
-
Select the Date data item and open the Properties window.
-
In the Value field of the DataItemTableView property, click the AssistButton to open the Table View window.
-
In the Table Filter field, click the AssistButton to open the Table Filter window.
-
In the Table Filter window, set a filter that selects records with the Period Type of Date.
NoteThis is an important step, as the iteration of the Date data item would otherwise run through all the records, including those for Weeks, Quarters, Months, and Years.
-
Click OK in the Table Filter window. Click OK in the Table View window.
-
In the Properties window of the Date data item, in the Value field of the ReqFilterFields property, enter Period Start. This lets the user enter a range of dates when they fill out the request form before running the report. This date range is then used to filter the information displayed in the report.
-
Open the Properties window of the Cust. Ledger Entry data item. In the Value field of the DataItemTableView property, click the AssistButton to open the Table View window.
-
In the Key field, use the AssistButton to open the Key List window.
NoteIn this example, an appropriate key is a key that contains the Document Type field, as the definition of a group in this report is based on these fields. Here, a key is selected that has this field as its first component, the other fields that are included in the key are not important. No individual entries will be printed, so they do not need to be sorted in any specific way.
-
Be sure that the DataItemLinkReference property points to the Date data item, this is the default value.
-
In the DataItemLink property, you must specify the field that establishes the link between the two data items. In the Value field of the DataItemLink property, click the AssistButton to open the DataItem Link window.
-
In the Field field, use the AssistButton to select the Posting Date field from the Cust. Ledger Entry data item. In the Reference Field field, select the Period Start field from the Date data item.
-
In the Value field of the GroupTotalFields property, use the AssistButton to select the Document Type field.
NoteThis groups the entries from the Cust. Ledger Entry table according to their document type: Payment, Invoice, Credit Memo, Finance Charge Memo, Reminder, or Refund.
-
In the Value field of the TotalFields property, use the AssistButton to select the Amount field. This adds up the entries in the Amount field for each document type for the date in question.
Designing the Sections
So far, this report allows you select a range of dates from the request form of the report. The report runs through the Date table, with a constant filter on the Period Type field that selects only records whose type is Date. If you select a date range, only dates within this range are selected; otherwise all the dates are used. For each date that you select, the records in the Cust. Ledger Entry data item that are posted on that date are selected. The records for the Cust. Ledger Entry data item are grouped according to the value of the Document Type field, and the totals are maintained for the Amount field.
To design the sections
-
Click View, and then click Sections to open Section Designer.
-
Click Edit, click New, and then insert a Header section for the Date data item. This header will be used to contain the labels for the columns of data in the report.
-
Insert two GroupFooter sections for the Cust. Ledger Entry data item. You create two GroupFooter sections for the Cust. Ledger Entry data item so that you can control their output separately. Both sections contain summarized information about the groups created by this data item.
-
Insert a Footer section for the Cust. Ledger Entry data item. This section is used to display the subtotals.
-
Insert a Footer section for the Date data item. This section is used to display the grand totals at the end of the report.
-
Neither data item requires a Body section so you can delete them.
Adding Fields to the Report
The next step is to add the fields that you want to include in the various sections of the report.
To add fields to the report
-
In Section Designer, select the Cust. Ledger Entry, GroupFooter (2) section, click View, and then click Field Menu to open the Field Menu window.
-
In the Field Menu window, select the Posting Date, Document Type, and Amount fields.
-
In Section Designer, click twice in the Cust. Ledger Entry, GroupFooter (2) section to add these fields.
-
Move the labels into the Header section and align the fields.
-
In Section Designer, select the Cust. Ledger Entry, GroupFooter (1) section, and then add the Document Type and Amount fields.
-
Delete the labels for the fields that you have just added to the Cust. Ledger Entry, GroupFooter (1) section. These extra labels serve no purpose.
-
Align the fields.
This report is not complete, you still need to add the fields that:
-
Contain the number of documents of each type that were posted on each date.
-
Calculate and display the total number of documents posted on each date.
-
Calculate and display the grand total number of documents posted in the period covered by the report.
-
Calculate and display the total amount posted on each date.
-
Calculate and display the grand total of the amount posted in the period that the report covers.
-
Display the date range that the user used to filter the information in the report.
To accomplish this, you need to write C/AL code in the triggers, define variables, and add the appropriate fields.
Creating a Global Variable with
Code
The records in the Cust. Ledger Entry data item contain an amount and the total amount is calculated by using the properties of the data item in the report. You accomplished this in step 12 in the Using Virtual Tables as Data Model section, when you set the TotalFields property of the Cust. Ledger Entry data item to calculate a total based on the Amount field in the Cust. Ledger Entry table. This total is calculated separately for each date and is the basis for the grand total that you will calculate later. Unfortunately, the number of entries cannot be calculated in the same way because there is no field in the data item that contains this information. However, each record in the Cust. Ledger Entry table corresponds to exactly one entry, which means that the number of entries can be calculated by simply counting the records. To calculate the total number of entries made on each date.
To create a global variable with code
-
Select the Cust. Ledger Entry data item in Report Designer, click View, and then click C/AL Code to open the C/AL Editor.
-
Create a global variable called Qty and define its type as Decimal.
-
Add the following C/AL code to these triggers in the Cust. Ledger Entry data item. To the Cust. Ledger Entry - OnPreDataItem() trigger, add the following line of code.
CurrReport.CREATETOTALS(QTY); -
To the Cust. Ledger Entry - OnAfterGetRecord() trigger, add the following line of code.
Qty :- 1;
The statement in the OnPreDataItem trigger
maintains totals for the Qty variable in the same way
that the TotalFields property specifies that totals
are maintained for a field in a record. The statement in the
OnAfterGetRecord trigger assigns a value of one to the
Qty variable each time a record is retrieved.
You will use the CREATETOTALS function to maintain
totals for each group and a grand total for the iteration of the
data item loop. As data items are grouped according to the Document
Type field, Qty contains the sum of all the entries of
the same document type each time the Cust. Ledger Entry GroupFooter
section is printed. When the Footer section is printed,
Qty contains the sum of all the entries that were
selected, that is, all the records that were entered on the same
date.
The variable in the argument of the CREATETOTALS
function must be of the Decimal data type because the function is
usually used to add up amounts. This is why the Qty
variable was declared as a Decimal rather than an Integer.
Adding Fields to the Report
You must now add some fields to the report that will display the totals calculated by the code in the previous section.
To add fields to the report
-
Open Section Designer and open the Toolbox.
-
Add a text box to the Cust. Ledger Entry, GroupFooter (1) section and open the Properties window of the text box.
-
In the Value field of the SourceExpr property, click the AssistButton to open the C/AL Symbol Menu window.
-
Select the Qty variable that you just defined.
-
Add a label to the Header section, open the Properties window of the label, and in the Caption property, enter Qty. Be sure that the HorzAlign property is set to Right.
The Qty variable that you have just defined is a decimal. You must therefore change the formatting of the Qty text boxes in the sections so that they do not show any decimal places. The format of the Qty text boxes are defined as <2:2> by default when the SourceExpr is of the Decimal data type.
To change the formatting of the Qty text box
-
Select the text box in Section Designer and open the Properties window.
-
In the Value field of the DecimalPlaces property change the value to 0:0. The field will no longer display any decimals.
The next step is to add the field that will contain the total number of documents of each type that are entered on each date that is printed in the report.
To add the Qty field to the report
-
Copy the Qty text box that you just added to the Cust. Ledger Entry, GroupFooter (1) section.
-
Paste the text box into the Cust. Ledger Entry, GroupFooter (2) section. This text box will now display the total number of documents of each type entered on each date.
The report now contains fields that list the entries in the Cust. Ledger Entry table according to the date when they were entered and shows how many documents of each type were entered on that date.
Calculating the Total Number of
Entries and Total Amount per Date
The next step in designing this report is to ensure that it displays some subtotals that show how many entries were made on each date, as well as the total amount entered on each date. This is why you created the Cust. Ledger Entry, Footer (3) section.
To calculate the subtotals
-
Copy the Qty and Amount fields from the Cust. Ledger Entry, GroupFooter (2) section into the Cust. Ledger Entry, Footer (3) section.
NoteThese fields will now display the total amount entered per date and the total number of documents entered on the date in question.
-
Add a label to the Cust. Ledger Entry, Footer (3) section, open the Properties window of the label, and then in the Value field of the Caption property enter Total.
Print the Date in the First
Iteration
In each iteration of the Date data item loop, you want to print the Date when the information selected from the Cust. Ledger Entry data item is entered. You could just use the body section of the Date data item and print the date there; the Period Start field of the data item, but this would mean that the date would print on a line by itself. Instead, the date should be printed along with the other information on the first line that comes from the Cust. Ledger Entry data item.
One solution is to use the Date field from the Cust. Ledger Entry data item. Unfortunately, if it is added to the GroupFooter section, the date will be printed on every line. This means that the finished report will not be very attractive and will be cluttered with redundant information.
A better solution is to define two GroupFooter sections for the Cust. Ledger Entry data item. One that includes the Date field and one that does not, and then control when they are output. This is why you created two GroupFooter sections earlier.
To add a date and control
-
Create a global variable called IsDatePrinted and define its data type as Boolean.
-
Add the following C/AL code to the OnPreDataItem trigger of the Cust. Ledger Entry data item:
IsDatePrinted := FALSE;NoteThis code initializes the IsDatePrinted variable, with the value FALSE, before each iteration of the data item loop.
-
Add the following C/AL code to the OnPreSection trigger of the Cust. Ledger Entry, GroupFooter (1) section of the Cust. Ledger Entry data item.
Copy CodeIF IsDatePrinted THEN CurrReport.SHOWOUTPUT(TRUE) ELSE CurrReport.SHOWOUTPUT(FALSE);
-
Add the following C/AL code to the OnPreSection trigger of the Cust. Ledger Entry, GroupFooter (2) section.
Copy CodeIF IsDatePrinted THEN CurrReport.SHOWOUTPUT(FALSE) ELSE BEGIN CurrReport.SHOWOUTPUT(TRUE); IsDatePrinted := TRUE; END
These two pieces of code ensure that:
-
When a new iteration of the Cust. Ledger Entry data item begins, a date is not printed.
-
If the loop generates any output, only the second GroupFooter section containing the Date text box is included as output in the first iteration.
-
If additional output is generated, only the first GroupFooter section without the Date text box is printed.
Calculating the Grand Total Amount
and the Grand Total Quantity
To complete this report you must ensure that it displays the total number of documents posted as well as the grand total of the amount posted in the period covered by the report. It should display this information at the end of the report in the Date Footer (2) section.
The next task is to calculate the grand total of all the amounts that are printed in the report. However, these amounts come from the Cust. Ledger Entry data item and not the Date data item. This means that you cannot use the TotalFields property of the Date data item to do the totaling.
To calculate grand totals
-
Declare two global variables: GrandTotalAmount and GrandTotalQty of data type Decimal and Integer respectively.
-
Add the following lines of code to the OnAfterGetRecord trigger of the Cust. Ledger Entry data item.
Copy CodeGrandTotalQty := GrandTotalQty + 1; GrandTotalAmount := GrandTotalAmount + Amount;
NoteThe first line adds one to the GrandTotalQty variable when a record is retrieved, while the second line adds the retrieved Amount to the GrandTotalAmount.
-
Open Section Designer and add two text boxes to the Date, Footer (2) section.
-
The first text box will be used to calculate the total number of documents in the report. Place it under the other Qty fields and open the Properties window of the text box.
-
In the Value field of the SourceExpr property, click the AssistButton to open the C/AL Symbol Menu window.
-
Select the GrandTotalQty variable that you just defined.
-
The second text box will be used to calculate the grand total of the amounts displayed in the report. Place it under the other Amount fields and open the Properties window of the text box.
-
In the Value field of the SourceExpr property, click the AssistButton to open the C/AL Symbol Menu window.
-
Select the GrandTotalAmount variable that you just defined. This text box will now display the grand total of all the amounts in the report.
-
Add a label to the Date, Footer (2) section and place it under the Total field in the Cust. Ledger Entry, Footer (3) section.
-
Change the caption of this label to Total. The Date, Footer (2) section now contains the grand totals for the report.
Printing the Selected Range of
Dates
The posting dates are used as a header in the left margin of the report, so the report would be complete if the final line could display the range of dates that the user selected before they ran the report.
To print the selected range of dates
-
Create a variable called DateFilter and define its data type as Text, with a length of 100.
-
Add the following C/AL code to the OnPreReport trigger of the report.
Copy CodeDateFilter := Date.GETFILTER("Period Start"); -
Add a text box to the footer section of the Date data item, open the Properties window of the text box and enter DateFilter as its source expression.
NoteWhen the
OnPreReporttrigger is executed, the RequestForm has already been run. TheGETFILTERfunction returns any filters on the field, which are passed as an argument, as a text string.
See Also
© 2010 Microsoft Corporation. All rights reserved.