tutorial_1_stage_4_-_working_with_groups [Reportico User Manual Version 6.0.0]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
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//

<code>
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
</code>

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

<code>sum({in_stock},{category})</code>

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.