Tutorial 1 Stage 3 - Working with expressions using assignments

Note that this tutorial requires you to enter assignments using PHP code. Assignment code may not be entered when running in SAFE mode as the user could create SQL statements here that could delete and update data in the database or perform disk operations. By default, the tutorial projects have SAFE mode turned on. To continue with this tutorial you need to turn off SAFE mode by folowing the instructions on how to turn on and off Safe Mode in the Reportico Security section.

In this stage you will learn how to :-

1. Create a string expression column

2. Create a cumulative expression

3. Create a line count expression

4. Use an assignment within a criteria item

Beginning the Tutorial 1 Stage 3 Report

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

Note that the starting point for this stage follows directly from stage 2 and is almost the same report that you created in stage 2 except we now have the year showing in the main report.

Create a string expression column

In this stage you will make the Film Id appear on the report as a 4-digit number padded to the left with zeroes.

Enter the report design mode and select the Assignments tab. Click Add to enter a new assignment and click on the button created marked Assignment Column. You should see a set of entry fields named Assign To,Expression etc. Here you select the column you wish to make the assignment to and expression in the form of a PHP expression.

Enter the following parameters:-

	Parameter	Value	Description
1.	Assign To	film_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
2.	Expression	str_pad({film_id},4,"0",STR_PAD_LEFT)	Call the PHP str_pad function to format the column with zeroes. Note the use of {film_id} entry to indicate a report column that will be reformatted within this functon. At run time the value of the film_id column for each row will be passed to this function and then assigned back to itself.
3.	Condition		Leave blank.

Run the report and check that the film Id column is zero padded.

Create a cumulative expression

In this stage you will create 4 new columns to appear on the report that represent a rolling minimum, maximum, sum and average.

This time you need to manually add 4 new assignments to columns that don't yet exist. So enter the report design mode and select the Assignments tab. Now click on the Add button, then click on the resulting Assignment Column button and set the Assign to New Column parameter value to be minyear. Set this to min({year}) and press Ok. Note that you can also use the Aggregates Wizard to implement the same minimum function. Now do this another 3 times adding columns named maxyear, sumyear and avgyear and setting the these to the values below..

	Assign To	Expression	Description
1.	minyear	min({year})	When the report runs the minyear column will show the minimum year so far reported
2.	maxyear	max({year})	When the report runs the maxyear column will show the maximum year so far reported line
3.	sumyear	sum({year})	When the report runs the sumyear column will show cumulative year sum
4.	avgyear	avg({year})	When the report runs the avgyear column will show cumulative year average

Run the report and check that your rolling/cumulative values are set.

Create a line count expression

In this stage you will create a new report column showing the line number

Select the Assignments tab. Click Add to enter a new assignment. Set the following parameters.

Enter the following parameters:-

	Parameter	Value	Description
1.	Assign To New Column	count	To set the count column
2.	Expression	lineno()	Function to set the line count
3.	Condition		Leave blank.

Use an assignment within a criteria item

In this stage you will use an expression within the film criteria item.

Enter report design mode and select the Criteria tab, then select the Criteria film button.

Select the criteria=>Assignments sub tab ( not the top Assignments which relates to the main query. We will assign a new criteria column with a string containing a formatted combination of the film id and the film title. Add a new assignment and set the following parameters :-

Enter the following parameters:-

	Parameter	Value	Description
1.	Assign To New Column	full	To set the full description
2.	Expression	str_pad({film_id},4,"0",STR_PAD_LEFT).".  ".{title}	Sets the full column to be a zero padded film id concatenated with the film title
3.	Condition		Leave blank.

To make use of the value within the criteria, you will need to select the Details subtab and set the Display Column to be full.

Run the report and click the film criteria expand(>>) button. You should see the film list comprises both film id and title.

Checking Your Report

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

Execute the report to get a list of films. On the report output you will see the first column of Film Id with values left-padded with zeroes. This is the result of the assignment that reformats the Film Id value.

Also notice the minyear and maxyear, avgyear and sumyear columns. They represent the cumulative minimum, maximum, average and sum of the year column. Obviously the sum and average of the year is pretty meaningless but it does demonstrate these functions.

Notice the Line column is an expression assignment which is the report line number

Finally, return to the report entry screen and expand the film criteria. You will see that the displayed film list contains values that comprise the film id concatenated with the film title. This is performed with an expression assignment within the criteria.