Tutorial 1 Stage 2 - Creating User Entry Criteria

In this stage you will learn how to :-

1. Add simple text entry criteria

2. Create a film lookup criteria

3. Create a film category lookup criteria and make the film query dependent on it

4. Create a country lookup criteria and set a default

Beginning the Tutorial 1 Stage 2 Report

Select Film Listing - Tutorial 1 Stage 2 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_2_films.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 virtually the same report that you created in stage 1.

Add simple text entry criteria

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
1.	Name	film	An internal name to give to the criteria item. You would use this name to refer to this criteria elsewhere in the report tool
2.	Title	Film	The title of the criteria item as it appears on the report execute screen
4.	Main Query Column	film_id	Indicates that the film.film_id column will be used to match the entered users criteria when running the report. So when the report runs, the main report query will include into the where clause something like :- AND film.film_id IN (1,2,3,4) assuming that the user had selected films 1,2,3 and 4.

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 film entries but with no lookup/expand option.

Remember to click the Ok after entry.

Now return to the report entry screen and type in some comma separated film ids (numbers between 1 and 200) into the Film criteria entry text box and press Execute. The resulting report will show the films you have chosen.

Now return to the report entry screen.

Create a film lookup criteria

Enter Design mode, select the Criteria tab and select the film criteria button. You should see your criteria parameters. We are now going to turn the film criteria into a lookup or expand criteria item. Select the SQL sub-tab and enter the following SQL to fetch the films as follows :-

SELECT film_id, title
FROM reptut_film
WHERE 1 = 1 
ORDER BY  reptut_film.title

Now select the Details sub-tab. Set the following parameters :-

	Parameter	Value	Description
1.	Criteria Type	Database Lookup	The lookup should be generated from the SQL you just entered
1.	Criteria Display	Text Field	Indicates that by default criteria can be entered in a text field
1.	Expand Display	Multiple Selection List Box	Indicates that on pressin ghte Expand button the user can select criteria from a multiselect list box
1.	Return Column	film_id	The name of the lookup column containing the unique id that will be joined in to the query when runing the report
2.	Display Column	title	The name of the lookup column containing values that the user can select from
3.	Summary Column	title	The lookup column containing values that would match values entered by the user user in the text field. This is also the column used for summarises the user's current selection
4.	Match Column	title	This is the lookup column is used to match the users entered search string shown above the multiple selection list box

Remember to press the Ok afterwards. Return to the criteria entry screen and press the Expand >> button. You should now be able to select your required films.

After running this, you might also want to try setting the Debug option to Low to see what Query is being run when you execute the report.

Create a film category lookup criteria and make the film query dependent on it

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 Ok 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 cat_code cat_code, cat_desc cat_desc 
FROM reptut_category 
WHERE 1 = 1  
ORDER BY cat_desc 

In order to complete the category criteria we need to identufy 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
3.	Main Query Column	category.cat_code	Indicates that the category.cat_code column will be used to match the entered users criteria when running the report. So when the report runs, the main report query will include into the where clause something like :- AND category.cat_code IN ("H","C") assuming that the user had selected (H)orror and (C)omedy.
1.	Criteria Type	Database Lookup	Indicates that the criteria item can generate a lookup selection box from the database
2.	Criteria Display	Checkboxes	Displays a manual text entry box for entry of a criteria
3.	Expand Display	No entry	When the film category expand button (>> is pressed, the user can select from a list of categories stored in the database presented as checkboxes. The values presented are generated from the SQL select statement entered above.
4.	Return Column	cat_code	Indicates that the cat_code column will be used to pass to the main report query so that the report will fetch the data matching the user's criteria.
5.	Display Column	cat_desc	Indicates that the contents of the cat_desc column will appear in the selection box presented alongside the checkboxes when the user expands the category criteria.
6.	Overview Column	cat_code	Indicates that the contents of the cat_code 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.
7.	Match Column	cat_desc	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.

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

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 country lookup criteria, link it to the main query with a different method, and set a criteria default

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	country
2.	Title	Country

Now enter the following SQL in the same way as you did with the category criteria with the following SQL:-

SELECT country_code country_code, country_name country_name 
FROM reptut_country 
WHERE 1 = 1   
ORDER BY  country_name 

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
1.	Main Query Column		Leave this blank Rather than link the criteria in with the main query here, we will do it within the main report query later.
2.	Criteria Type	Database Lookup	Indicates that the criteria item can generate a lookup selection box from the database
3.	Criteria Display	Text Field	Displays a manual text entry box for entry of a criteria
4.	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.
5.	Return Column	country_code	Indicates that the country_code column will be used to pass to the main report query so that the report will fetch the data matching the user's criteria.
6.	Display Column	country_name	Indicates that the full country name will appear in the selection box presented alongside the checkboxes when the user expands the country criteria.
7.	Overview Column	country_name	Indicates that the full country name 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 ful country names into the summary text box as an alternative way of selecting countries.
8.	Match Column	country_name	Indicates that the full country name country_name column is used to match a value entered into the Search box in order to narrow down the list of choices.
9.	Criteria Defaults	ITA,UK	These are the codes for Italy and United Kingdom in the country table. These will be selected by default when the report runs.

Now link the film criteria to the country criteria. Add another clause to the film criteria SQL as you did with the category criteria but this time as follows (new line emphasised) :-

SELECT film.film_id, film.title
FROM reptut_film film
WHERE 1 = 1 
[ and cat_code IN ({category,VALUE}) ]
[ and country_code IN ({country,VALUE}) ]
ORDER BY  film.title

Link the country criteria into the main query

Now we need to tell the main query how to link with the country criteria because we did not specify a Main Query Column value for the country criteria item. We are going to do this in the preferred way by adding it to the main report query.

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 link two criteria items together. Chanfge the SQL to :-

SELECT film.film_id, film.title, category.cat_desc, country.country_name 
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  
[ AND film.country_code IN ( {country,VALUE} ) ]
ORDER BY  country.country_name, title

Now the report is ready to run. Return to Execute mode and you should see your default countries displayed. Now check the link to films by expanding the film criteria. You should see films shown only for those selected countries. You should also be able to select both countries and categories. The film expand box will then only display films belonging to both criteria.

Checking Your Report

After you have successfully completed the above steps, run the report :-

Notice that the country criteria is defaulted to the selected default countries. If you press the expand button on the film criteria only films from the default countries are shown.

Now return to the report entry screen. Clear the country text box to allow reporting on films from all countries. Now click on the film criteria >> button to perform a look up.

Experiment with the Search. Enter a string that appears in one or more films, for example dog. And then press the Search button. Your required entries should appear. To go back to all films, clear the search text box and press the Search button again.

Experiment with the Select All and Clear buttons. Now select a set of films. To select a range of films select two films 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 ids of the films appear in the box.

Now press the Execute button to produce the HTML report. Your selected films should be reported.

Return to the report entry screen and press the Reset button next to the Execute button. All your selections will disappear.

Now select one or two film categories. Now click on the film expand (>>) button. You will see that your selected categories appear in the category field entry box and a list box of films which belong to those categories only. This is because we have defined the film criteria as being dependent on the category criteria. In this report we have also made the film criteria dependent on the country criteria item as well. So if you select a country, then when you expand the film criteria you will only see films realting to that country. If you have both countries and categories selected then only the films will be displayed that belong to both country and category criteria.