Prev |  | Next
## 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 :- Create a string expression column Create a cumulative expression Create a line count expression 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).".&nbsp;&nbsp;".{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.
Prev | Up | Next
Tutorial 1 Stage 2 - Creating User Entry Criteria | Reportico Tutorial | Tutorial 1 Stage 4 - Working with groups
Documentation generated on Fri, 26 Jul 2013 20:58:43 +0100 by phpDocumentor 1.4.3