Differences

This shows you the differences between two versions of the page.

Link to this comparison view

tutorial_1_stage_3_-_working_with_expressions_using_assignments [2016/10/26 16:22]
admin created
tutorial_1_stage_3_-_working_with_expressions_using_assignments [2018/05/04 21:40]
Line 1: Line 1:
-===== Tutorial 1 Stage 3 - Working with expressions using assignments ===== 
  
-Note that this tutorial requires you to enter assignments using PHP code. By default, the tutorial projects have SAFE mode turned on. Some of the assignments here may not be entered when running in SAFE mode as the user could create statements here that could delete and update data in the database or perform disk operations. ​ To continue with this tutorial you need to turn off SAFE mode. To do this return to the Northwind project menu, click on the "​Configure Project"​ button to enter project configuration. Find the Safe Mode option and uncheck it. See more about safe mode in the [[reportico_security|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 Tutorial 1 Stage 3 - Stock Report 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_3_stock.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. 
- 
-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. 
  • tutorial_1_stage_3_-_working_with_expressions_using_assignments.txt
  • Last modified: 2018/05/04 21:40
  • (external edit)