SUBREPORTS

Subreports are an advanced feature that can solve some complex database reporting problems. These include:

• Data for the report are coming from more than one data source
• Table joins cannot be used to retrieve the required records
• Several views of the same data are required

If you have these problems, display some of the report as a main report and the remainder in one or more subreports.

Then you have an important design decision to make. Which part of the report do you do in the main report, and which parts do you want in subreports?

The main report must do the primary record selection and grouping. You cannot group or select data based on values in the subreport. The subreport is then used complete the missing values–essentially "fill in the blanks."

One extension to this approach is to use a subreport to retrieve a value, and then use conditional formatting in the main report that uses the subreport information. But there are limitations to this:

• The subreport has to take up some vertical space in the report.
• Subreports in suppressed sections do not get processed, so any shared variables expect from them will not be calculated.

The solution is to create the parameter in the subreport prior to creating the link. When you create a link to an existing parameter in the subreport, it is saved when you exit the subreport link form.

1.        Select INSERT

2.        SUBREPORT

3.        The subreport wizard appears.

4.        Name the subreport.

5.        Select REPORT WIZARD

6.        Create New Database Connection

7.        The database expert displays

8.        Select CREATE NEW CONNECTION by clicking the node to the left.

9.        The screen displays the database and the TABLES, VIEWS and STORED PROCEDURES

10.     Select the tables and move them to the select table’s area.

11.     If a stored procedure or view is being used, and they contain PARAMETERS built into the stored procedure or view, you will be prompted to enter value(s).

12.     Select NEXT

13.     The LINK screen displays. 

14.     Select NEXT

15.     The field selection screen displays

16.     Select the fields and drag them to the detail section of the report.

17.     Once a field has been used in the report it is designated with a green check to the left of the field name in the field explorer.To view the data that is displayed select the REFRESH DATA icon.

18.     Select FINISH

19.     The screen reverts back to the main screen.

20.     Place the sub report in the location where it should display.

21.     Right click on the sub report

22.     Select EDIT

23.     Format the subreport to your desired layout.

24.     Dependent on the purpose of the subreport, you may need to do one or all of the following;

a.        Create Several views of the same data are required where you would layout the subreport identical to the main report. (if you want to see costs in one area of the report and fees in another)

b.        Create a subreport to retrieve values to use in the main report.

Linking the Subreports

Link a field or formula from the main report to a field or formula in the subreport to retrieve records based on that link. The field must be in both reports to link the subreport to the main report.

25.     Right click on the subreport

26.     Select CHANGE SUBREPORT LINKS

27.     The following screen displays

28.     Select a field from the main report which is located under AVAILABLE FIELDS.

29.     Move it to the FIELDS TO LINK TO.

30.     If the field is available in the subreport to join to it will display in SELECT DATA IN SUBREPORT BASED ON FIELD.