Home > Tutorials > Report Manager Tutorial > Creating Advanced Reports > Creating Sub-Query Reports > Creating Advanced Reports: Sub Query Part 2

Part 2: Using the Sub-Query Report as a Filter

Use the sub-query Report as the basis of a filter in a report.

Step 1: Set the sub-query filter

To use the sub-query created in the exercise above, set it as a filter in a report. Create a new report called Customer Invoice List using the following details.

• Report Name: Customer Invoice List
• Container: CustomerDocuments
• Columns: CustomerCode, CustomerName, Date, DocNo, DueDate, OrderNo, TotalExcl
• Filter:
  • DocType Equal to INV and
  • DocNo Is Not In Sub-Query: Sub Query - Credit Notes
• Parameters:
  • Date Greater than or Equal to 01 July 2003
  • Date Less than or Equal to 31 December 2003

Step-by-Step Solution

1. Right-click on the Training folder and click Add Report.
2. Select Standard Report.
3. Enter the name: Customer Invoice List and click OK.
4. Select the data container: CustomerDocuments.
5. Select the columns listed above and click OK. The Report is displayed below the Training Folder.
6. Select the Filter tab.
7. Click the Add button and add the DocType filter to the report, using the filter details listed above.
8. Click Add. The Filter field dialog box is displayed.
9. Select the DocNo field as the filter field.
10. Select Is Not In Sub Query from the Comparison Operator dialog box.A list of sub query reports are displayed.
11. Select Sub Query - Credit Notes from the list displayed. Then click Ok.
12. Click the Parameter tab.

13. Click Add and complete the parameters using the details listed above then click OK.

    Note: Ensure both parameters are included.

Step 2: Run the report

Run the report.

Step-by-Step Solution:

1. Select the Customer Invoice List and click the Run button. The report is run and the results are displayed in Microsoft Excel as shown in the figure below:

Step 3: 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 Customer Invoice List 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 completes the section on sub-query reports. The next section deals with union reports.

Next-->