Next Tutorial 1 Stage 4 - Working with groups In this stage you will learn how to :- Create a group with headers and trailers

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=northwind&execute_mode=PREPARE&xmlin=tut1_4_stock.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 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 (or use the SQL button). Change the report query to folliwing and press Apply

SELECT  ProductID id, ProductName product, northwind_categories.CategoryID, UnitsInStock in_stock, UnitsOnOrder on_order, ReorderLevel,  companyname Company, country, categoryname category
FROM northwind_products 
join northwind_suppliers on northwind_products.supplierid = northwind_suppliers.supplierid
join northwind_categories on northwind_products.categoryid = northwind_categories.categoryid
WHERE 1 = 1  
[ AND northwind_suppliers.SupplierID in ( {supplier} ) ] 
[ AND northwind_suppliers.country in ( {country} ) ] 
[ AND northwind_categories.categoryId in ( {category} ) ] 
[ AND {lowstock} = '1' and UnitsInStock < reorderlevel ]
ORDER BY categoryname

Now select the Output tab followed by the Groups sub-tab. Click on the Add button which will create a new Group Button. Click on this button and set the Group on column value to category. Press Apply.

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 category. 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 in stock total below the in stock column on the report. To set the in stock trailer, press the Add button and set the Group Trailer Display Column column to in_stock and the Group Trailer Value Column column to in_stock_total. This says that after each category set, the report should show the in stock total below the in_stock column.

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” and “in stock total”. 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 the columns category and in_stock_total 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 in_stock_total assignment to a group sum, select the Assignments tab, select the in_stock_total assignment and change the expression to include the group. That is change it to

sum({in_stock},{category})

This adds categry as an extra parameter to the sum function.

Rerun the report and check that the in stock total relates to the category group.

Checking Your Report

Run the rep ort. This report groups the product stock by category. You should see group headers showing the category name. Also at the end of each category you will see the in stock 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.

  • tutorial_1_stage_4_-_working_with_groups.txt
  • Last modified: 2018/05/04 21:40
  • (external edit)