Table of Contents
Tutorial 1 Stage 2 - Creating User Entry Criteria
In this stage you will learn how to :-
Add simple text entry criteria
Create a country lookup criteria
Create a supplier lookup criteria and make it dependent on the country lookup
Create a category lookup criteria and make the film query dependent on it
Beginning the Tutorial 1 Stage 2 Report
Select Tutorial 1 Stage 2 - Stock 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_2_stock.xml).
You are now ready to start the second stage of this tutorial.
Note that the starting point for this stage follows directly from stage 1 and is the same report that you created in stage 1.
Add simple text entry criteria
Here we will allow the to report on a particular product by id.
Enter the report design mode and select the Criteria tab. Click Add to enter a new criteria and you will notice that a button appears named Criteria Name. Click on this and set the following parameters :-
| Parameter | Value | Description |
|---|---|---|
| Name | id | An internal name to give to the criteria item. You would use this name to refer to this criteria elsewhere in the report tool |
| Title | Product Id. | The title of the criteria item as it appears on the report execute screen |
| Main Query Column | Leave blank | |
| Criteria Type | Text Field | This should already be set |
Leave all other values at their default. Note the Criteria Display of Text Field and Expand Display of NOINPUT means at the moment we are allowing the user to type in id but with no lookup/expand option.
Remember to click the Apply button after entry.
Now click Run Report and you should see the Product Criteria Item as so
Now, we need to link the main report query to this criteria so it will take into account our criteria item. So go back into design mode and choose Query Details tab and SQL subtab (alternatively the report screen gives you quick SQL button to enter the ain report SQL ).
We need to add into the query where clause an entry as follows:-
[ AND ProductID in ( {product_id} ) ]
The square brackets at the start and end of the line, means that the clause will only be included if user enters a criteria, otherwise all products will be reported. The “in” clause allows user to specify more than one product id separated by commas, alternatively the = could have been used to atch one. The {product_id} entry means that the values at run time will come from the criteria item named “product_id” which is what you used when entering the criteria and what the user is providing values for here.
Your SQL should now look something like ..
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 ProductID in ( {product_id} ) ]
ORDER BY categoryname
Now return to the report entry screen and type in some comma separated ids (numbers between 1 and 200) into the product id entry text box and press one report execution buttons. The resulting report will show the products for the ids you have chosen.
Create a country lookup criteria which can be search by an Expand Search button
Enter Design mode, select the Criteria tab and add a new criteria with the Add button. Then set the following parameters:-
| Parameter | Value | Description |
|---|---|---|
| Name | country | |
| Title | Country | |
Now hit the Apply button to store the details. We will fill in the remainder of the parameters one we have entered the lookup query SQL. To do this click on the SQL tab under the Criteria Tab and key the following SQL statement into the Query SQL Box and press the Ok button:-
Now enter the following SQL in the same way as you did with the category criteria with the following SQL:-
SELECT DISTINCT Country FROM northwind_suppliers WHERE 1 = 1
In order to complete the country criteria we need to identify which of the columns from this select will be used for display in the lookup/expand selection box, which column will be used in the main report where clause, which column will be used for searching on and which column will be displayed to the summary section if the report screen when the user has made a selection. Since there is only one column, this is used for all of them.
Return to the Details sub-tab and complete the country criteria entry by filling in the remaining criteria entry with the following parameters:
| Parameter | Value | Description |
|---|---|---|
| Main Query Column | Leave this blank | |
| Criteria Type | Database Lookup | Indicates that the criteria item can generate a lookup selection box from the database |
| Criteria Display | Text Field | Displays a manual text entry box for entry of a criteria |
| Expand Display | Multiple Selection List Box | When the country expand button is pressed, the user can select from a list of countries stored in the database presented as a multiple selection dropdown list box. The values presented are generated from the SQL select statement entered above. |
| Return Column | Country | Indicates that the Country column will be used to pass to the main report query so that the report will fetch the data matching the user's criteria. |
| Display Column | Country | Indicates that the country will appear in the selection box presented alongside the checkboxes when the user expands the country criteria. |
| Overview Column | Country | Indicates that the country will appear on the summary side of the report entry screen indicating the current countries selected. This also means that the user may type in country names into the summary text box as an alternative way of selecting countries. |
| Match Column | Country | Indicates that the country column is used to match a value entered into the Search box in order to narrow down the list of choices when in expand mode. |
Link the country criteria into the main query
Now we need to tell the main query how to link with the country criteria.
Edit the main report query SQL,Choose the main Query Details Tab and modify the SQL to include an extra line in the WHERE clause to link to the country criteria in the same way as we linked in the product id above. We need to add this in :-
[ AND northwind_suppliers.country in ( {country} ) ]
You should now have something that looks like this
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 ProductID in ( {product_id} ) ]
[ AND northwind_suppliers.country in ( {country} ) ]
ORDER BY categoryname
Now the report is ready to run. Return to the report and you should see your country criteria displayed and on the side is the Expand button(a magnifying glass). Click on this, and you should see a list of countries. Select one or multiples (using the Ctrl/Shift key). Press the Ok button under the coutnry box and the selected countries should appear in the text box. (Althuogh, you dont have to select that final Ok in order to run the report for the selected countries).
Create a category lookup criteria to show checkboxes
Enter Design mode, select the Criteria tab and add a new criteria with the Add button. Then set the following parameters:- Parameter Value Description 1. Name category 2. Title Category Now hit the Apply to store the details. We will fill in the remainder of the parameters one we have entered the lookup query SQL. To do this click on the SQL tab and key the following SQL statement into the Query SQL Box and press the Ok button:-
SELECT CategoryID, CategoryName, Description FROM northwind_categories WHERE 1 = 1 ORDER BY CategoryName
In order to complete the category criteria we need to identify which of the columns from this select will be used for display in the lookup/expand selection box, which column will be used in the main report where clause, which column will be used for searching on and which column will be displayed to the summary section if the report screen when the user has made a selection.
So return to the Details tab and set the following parameters :-
| Parameter | Value | Description |
|---|---|---|
| Main Query Column | Leave blank | |
| Criteria Type | Database Lookup | Indicates that the criteria item can generate a lookup selection box from the database |
| Criteria Display | Checkboxes | Displays a list of checkboxes which the user can select from generated from the SQL |
| Expand Display | No entry | No expand button is required, because we are poutting the criteria on the frontscreen |
| Return Column | CategoryID | Indicates that the CategoryID column will be used to pass to the main report query so that the report will fetch the data matching the user's criteria. |
| Display Column | CategoryName | Indicates that the contents of the CategoryName column will be used to label the checkboxes |
| Overview Column | CategoryName | Indicates that the contents of the CategoryName column will appear on the summary side of the report entry screen to indicate the current selection. This also means that the user may type in category codes into the summary text box as an alternative way of selecting categories. |
| Match Column | CategoryName | Indicates that the cat_desc column is used to match a value entered into the Search box in order to narrow down the list of choices. |
Press the Apply button and return to the report screen, you should see your categories available for selection as checkboxes boxes…
Now link the criteria into the main SQL as above .. the SQL should look something like
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 ProductID in ( {product_id} ) ]
[ AND northwind_suppliers.country in ( {country} ) ]
[ AND northwind_categories.categoryId in ( {category} ) ]
ORDER BY categoryname
Now we have entered both film and category criteria we are going to link the two together so that when we expand the film list criteria we only show films from the selected categories.
To do this we need to return to the film criteria entry and enter the SQL subtab. You will see the current SQL which selects from the films table. We now want to extend this by adding to the where clause a new line ( emphasised ) below :-
SELECT film.film_id, film.title FROM reptut_film film WHERE 1 = 1 [ and cat_code IN ({category,VALUE}) ] ORDER BY film.title The square brackets mean that the clause will only be included if the user has made a category selection. The {category,VALUE} will be replaced with the user's category selections when the film criteria is expanded. Now test what you have entered by returning to Execute Mode and selecting one or more categories from the category expand box. Then expand the film category and you will only see films under that category. Also, you should be able to enter category codes into the category text box as an alternative way of selecting a category.
Create a supplier lookup criteria using a dynamic searchable list box
Enter Design mode, select the Criteria tab and add a new criteria with the Add button. Then set the following parameters:- Parameter Value Description 1. Name supplier 2. Title Supplier Now hit the Apply to store the details. We will fill in the remainder of the parameters one we have entered the lookup query SQL. To do this click on the SQL tab and key the following SQL statement into the Query SQL Box and press the Ok button:-
SELECT supplierid, companyname
FROM northwind_suppliers
WHERE 1 = 1
[ AND country in ( {country} ) ]
Hit the Apply button
So return to the Criteria Details tab and set the following parameters :-
| Parameter | Value | Description |
|---|---|---|
| Main Query Column | Leave blank | |
| Criteria Type | Database Lookup | Indicates that the criteria item can generate a lookup selection box from the database |
| Criteria Display | Searchable Multiple List Box | Displays a searchable list box |
| Expand Display | No entry | No expand button is required, because we are putting the criteria up without need for search button |
| Return Column | supplierid | Indicates that the supplierid column will be used to pass to the main report query so that the report will fetch the data matching the user's criteria. |
| Display Column | companyname | Indicates that the contents of the companyname column will be used for display in the list box and used for searching on |
| Overview Column | companyname | Indicates that the contents of the companyname column will appear on the summary side of the report entry screen to indicate the current selection. This also means that the user may type in category codes into the summary text box as an alternative way of selecting categories. Not relevant to this type of criteria |
| Match Column | companyname | Indicates that the companyname column is used to match a value entered into the Search box in order to narrow down the list of choices. |
Press the Apply button and return to the report screen, you should see a supplier list box which you can type characters into and you are able to make selections that match your entry.
Now link the criteria into the main SQL as above .. the SQL should look something like
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 ProductID in ( {product_id} ) ]
[ AND northwind_suppliers.SupplierID in ( {supplier} ) ]
[ AND northwind_suppliers.country in ( {country} ) ]
[ AND northwind_categories.categoryId in ( {category} ) ]
ORDER BY categoryname
Now return to the report screen and you should be able to run the report for the selected suppliers.
Create a custom criteria
You can alter the report query behaviour by using custom criteria items which present the user with specified options to control whether to for example include or exclude vaues from the query.
Here we are going to see how to create a checkbox criteria item which when checked can alter the SQL to only show low stock items.
Enter Design mode, select the Criteria tab and add a new criteria with the Add button. Then set the following parameters:-
| Parameter | Value | Description |
|---|---|---|
| Name | lowstock | |
| Title | Show Only Low Stock | |
| Main Query Column | Leave blank | |
| Criteria Type | Custom List | Indicates user will provide list of select options |
| Criteria Display | Checkboxes | Presents selection as check boxes |
| Expand Display | No entry | No expand button is required |
| List Values | Yes=1 | This means that this criteria presents a single check box with a label of Yes and and a value of 1. When checked this value can be used in the main report query as shown below. You can provide more comma delimited pairs like this to provide more complex options |
Press the Apply button and return to the report screen, you should see a check box.
Now link the criteria into the main SQL as above .. to do this we use a trick.. you need to add in the following to the where clause :-
[ AND {lowstock} = '1' and UnitsInStock < reorderlevel ]
This means that if the value of lowstock is 1 ( i.e is checked ) then only rows are returned which have stock less than the reorder level.
So the final SQL looks as so :-
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 ProductID in ({product_id}) ]
[ 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 return to the report screen and you should be able to run the report for low stock items
Link Criteria Together
Now we are going to make the supplier criteria dependent on the country criteria. So if one or more countries are selected, then the supplier criteria only presents suppliers from the selected countries
To do this we need to return to the supplier criteria entry and enter the SQL subtab. You will see the current SQL which selects from the suppliers table. We now want to extend this by adding to the where clause a new line to link in the country :-
SELECT supplierid, companyname
FROM northwind_suppliers
WHERE 1 = 1
[ AND country in ( {country} ) ]
The square brackets mean that the clause will only be included if the user has made a country selection.
Now test what you have entered by returning to the report scree and selecting one or more countries from the country expand box. Then start typing characters into the supplier box and you will see only suppliers from that country
Checking Your Report
After you have successfully completed the above steps, run the report :-
If you press the expand button on the country then the list of countries appears. Experiment with the Search in the expand panel. Enter a string that appears in one or more countries, for example France. And then press the Search button. Your required entries should appear. To go back to all countries, clear the search text box and press the Search button again.
Experiment with the Select All and Clear buttons. Now select a set of countries. To select a range of countries select two countries with the Shift key held down. To select a set of individual films hold down the CTRL key while selecting. Once your selection is made, click the Ok key. Notice how the countries selected appear int the text box on the left.
Now press the Execute button to produce the HTML report. Your selected products should be reported.
Now clear your country selections.
Play with the other criteria items, hopefully things work as expected
