Home > Tutorials > Report Manager Tutorial > Advanced Report Settings > Advanced Settings: Aggregate Functions

Advanced Options - Aggregate Function Exercise

Aggregate Functions

You have been asked to summarize the sales for the company by Customer per Period for the current year. This will require the use of an Aggregate function. Complete the following steps to create a report that enables you to summarize the data prior to it being rendered to Microsoft Excel.

Step 1: Create a new report

Use the Help File Edit Column Properties. Create a new report with an aggregate function using the following details:

• Folder: Training
• Report Name: RKL Sales Summary by Customer
• Data Container: Customer Documents
• Place the following fields in the report
  • Customer Code
  • Customer Name
  • Period
  • TotalExcl

Step by Step Solution:

1. Select the Training folder and click the Add Report button on the toolbar.
2. Select the type of report to create: in this case Standard Report.
3. Enter the name of the report in the Report Name dialog box.
4. Select the Customer Document container from the Select Container dialog box.
5. Select the fields listed above and click OK. The report is displayed under the Training folder.
6. Click the Filter tab.
7. Click Add. The Choose Field dialog box is displayed.
8. Select Period and click OK.
9. Select Greater than or equal to and click OK.
10. Enter 1 in the Comparison value dialog box and click OK.

Step 2: Set the Aggregate Filter

Set the following aggregate function to the report:

• TotalExcl aggregate function is set as Sum

Step by Step Solution:

1. Ensure that the report is selected.
2. Click the Columns tab.
3. Right-click on the TotalExcl column and select Properties.
4. In the Properties dialog box, click the Aggregate Function drop-down list and select Sum.
5. Click OK. The Sum aggregate function is displayed to the right of the TotalExcl field in the Column tab.

Step 3: Run the report

Run the report

1. Run the report. The summarised data is rendered to Microsoft Excel.

Step by Step Solution

1. Click the Run button. The data is exported to Microsoft Excel.

2. Compare the output to the figure to ensure that it is correct.

   

Step 4: Link the Microsoft Excel report back to Sage Intelligence Reporting

Link the Microsoft Excel report back to Sage Intelligence Reporting .

Step by Step Solution

1. Select the RKL Sales Summary by Customer report in the Report Manager.
2. Click the Create and Link button.
3. Select the Microsoft Excel workbook that contains the report and click OK. The Specify a Template name dialog box is displayed.
4. Leave the default name unchanged and click OK. The Microsoft Excel workbook is converted to a template and linked to the report.
5. Ensure the Report Manager is active for the next exercise.

This concludes the section on Advanced Settings. The next section takes you through the process of creating advanced reports.

Next-->