Tutorial 1 Stage 4 - Working with groups

1. Create a group with headers and trailers

2. Create group sums, averages, minums, maximums

Beginning the Tutorial 1 Stage 4 Report

Select Film Listing - Tutorial 1 Stage 4 from the tutorials menu (Alternatively you can point your browser at the URL http://{SERVER_ADDRESS}/{REPORTICO_INSTALL_DIRECTORY}/run.php?project=tutorials&execute_mode=PREPARE&xmlin=tut1_4_films.xml). You are now ready to start the fourth stage of this tutorial.

Note that the starting point for this stage follows directly from stage 3 and is virtually the same report that you created in stage 3.

Create a group

In this stage you will group the report by category

Before adding the category group we will change our main query select statement to order the report output by category. The grouping will only happen correctly if the the data is sorted by our group. This is because group headers will be printed whenever the group field changes.

So enter report design mode and select the Query Details tab. Change the report query to

SELECT film.film_id film_id, title title, cat_desc cat_desc, country.country_name country_name, year year 
FROM reptut_film film, reptut_country country, reptut_category category 
WHERE 1 = 1         AND film.cat_code = category.cat_code
AND film.country_code = country.country_code  
ORDER BY  cat_desc, title ASC

Now select the Output tab followed by the Groups subtab. Click on the Add button which will create a new Group Button. Click on this button and set the Group on column value to cat_desc.

To enter the group header values, select the Headers sub tab and click the Add button. Select the Header 0 button and set the Group Header Column to cat_desc. This says that before each category group data, the report should print the category description.

Now enter the group trailer values by selecting the Trailers option. In this section we will indicate that we want to show the group minimum, maximum, sum and average year values below the year column on the report. To set the minimum year trailer, press the Add button and set the Group Trailer Value Column column to minyear and the Group Trailer Display Column column to year. This says that after each category set, the report should show the minimum year below the year column. Do the same for maxyear, sum year and avgyear setting these to display under the year column as well.

Now run the report to check that your group headers and trailers are displayed. Notice however that the report detail section still shows the category description and the cumulative year minimum, maximum, sum and average values. Since these are really for the group headers and trailers we want to hide them from the report detail. So go back to design mode, select the Query Details=>Query Columns sub tab and for each of the columns cat_desc, minyear, maxyear, sumyear and avgyear set the Show or Hide parameter to hide. Rerun the report to check that these columns are removed from the report body.

Create group sums, averages, minums, maximums

The current report will not show group minimums, maximums etc but rolling cumulative values that do not reset on each category change. To turn the existing cumulative year values to be group related, we need to modify each related assignment. So to change the minyear assignment to a group minimum, select the Assignments tab, select the minyear assignment and change the expression to include the group. That is change it to

min({year},{cat_desc})

Rerun the report and check that the cumulative year values relate to the category group.

Checking Your Report

Run the report. This report groups the films by category. You should see group headers showing the category name. Also at the end of each category you will see the minimum, maximum year as well as the year average and total. Notice that the category and year summary values are not shown in the detail section because we have hidden them as they are now summary values only.