Tutorial 1 Stage 5 - Including database graphics into the report and generating drilldowns

In this stage you will learn how to :-

1. Create an image assignment

Beginning the Tutorial 1 Stage 5 Report

Select Film Listing - Tutorial 1 Stage 5 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_5_films.xml). You are now ready to start the final stage of this tutorial.

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

Create an image assignment

To include the image in the report, you must create a new report column and then create assignment to it that will fetch the database image based on the country code.

Select the Assignments tab and add a new assignment. Find the Database Graphic Wizard and open this up. Then set the parameters as follows:-

	Parameter	Value	Description
1.	Assign To New Column	flag	Creates a new flag column
2.	Column Containing Graphic	flag	The column containing the graphic image
3.	Table Containing Graphic	reptut_country	The table containing the graphic image
4.	Column To Match Report Graphic	country_code	The column in the image table that is queried
5.	Report Graphic Width	50	The width in pixels to display the image in the report
6.	Graphic Report Column	country_code	The report column which used to look up the row in the image table

Press Ok and notice that the expression is set to an image_query function call which shows the select statement that will be run to fetch the image.

Checking Your Report

Run the report. Notice that the report output includes a flag for each country. The flag image for each country is held in the country table in column flag

Create a drilldown link to another report

To turn one of the columns into a clickable link which will display another report based on the clicked column. In this case we are going to convert the film title into a clickable link that will open up the loan history report and show all dates that hte film was hired and the members who hired it.

Enter report design mode and select the Assignments tab and add a new assignment by clicking the Add tab. Wr

Now we have the column, select the Assignments tab and add a new assignemnt by pressing Add and click on the new Assignment button that appears at the bottom of the assignment buttons. Set the Assign To field to be title as that is the column we are going to turn into a clickable link. Now find the Drilldown Wizard and open that up, set the drilldown report name to be tut2_loanhistory.xml as that is the report we are going to drill into. Click the Ok button next to the Drilldown Report line and a list of the criteria items in the Loan History report will appear. Now select the list box labeled Drilldown Column for Criteria film and choose film_id. So what we are doing is saying that the film_id column in this report shoudl be passed to the film criteria item in the Loan History report when the link is clicked. Press the Ok button on the right of the drill down options. Now you have an assignment expression of

    embed_hyperlink('Drill', '{URL_TO_SITE}/run.php?/xmlin=tut2_loanhistory.xml&execute_mode=EXECUTE&target_format=HTML&target_show_body=1&project=tutorials&MANUAL_film='.{film_id}.'')

We are not finished yet. There are 2 things to do to this assignment.

Firstly the link this function will create will be labelled Drill. We want to the link to appear as the film title. So change the assisngment to (leaving the appropriate value for URL_TO_SITE}

    embed_hyperlink({title}, '{URL_TO_SITE}/run.php?xmlin=tut2_loanhistory.xml&execute_mode=EXECUTE&target_format=HTML&target_show_body=1&project=tutorials&MANUAL_film='.{film_id}.'')

You should try running the report now, to check your link appears in the output. Also the link should work in PDF output!! However the Loan History report by default shows loans taken out between the first of this month and today. It is quite likely that the films you click on may not have been loaned out. Therefore we can change the link to get details of films taken out for a longer period. So lets change the link to report on the period between the first of the last year and today.

To do this edit the same assignment and change the assignment to (leaving the appropriate value for URL_TO_SITE}

    embed_hyperlink({title}, '{URL_TO+SITE}/run.php?xmlin=tut2_loanhistory.xml&execute_mode=EXECUTE&target_format=HTML&target_show_body=1&project=tutorials&MANUAL_loanDate_FROMDATE=FIRSTOFLASTYEAR&MANUAL_loanDate_TODAY&MANUAL_film='.{film_id}.'')

Checking Your Report

Run the report. See that the link opens up a Loan History report and shows loans since the first of last month for the chosen film.