Differences

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

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
tutorial_1_stage_3_-_working_with_expressions_using_assignments [2016/10/26 16:25]
admin
tutorial_1_stage_3_-_working_with_expressions_using_assignments [2018/05/04 21:40] (current)
Line 1: Line 1:
-===== Tutorial 1 Stage 3 - Working with expressions using assignments ​=====+==== 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. 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.
Line 10: Line 10:
  
 Create a line count expression Create a line count expression
 +
 +Create a conditional assignment to style a cell depending on its value
  
 Use an assignment within a criteria item Use an assignment within a criteria item
  
-==== Beginning the Tutorial 1 Stage 3 Report ====+===== 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. 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.+Note that the starting point for this stage follows directly from stage 2 and is the same report that you created in stage 2.
  
 ===== Create a string expression column ===== ===== 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.+In this stage you will make the Id field 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 report design mode and select the Assignments tab (or click the Assignments button). 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:​- Enter the following parameters:​-
-Parameter Value Description +^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 +|1. |Assign To |id |We are applying ​the assignment ​to the existing id column| 
-2. Expression  +|2. |Expression |str_pad({id},​4,"​0",​STR_PAD_LEFT)|Call the PHP str_pad function to format the column with zeroes. Note the use of {id} entry to indicate a report column that will be reformatted within this functon. At run time the value of the id column for each row will be passed to this function and then assigned back to itself.| 
-str_pad({film_id},​4,"​0",​STR_PAD_LEFT) +|3. |Condition  | |Leave blank.|
-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.+Run the report and check that the id column is zero padded. 
 + 
 +Now for extra interest, you might want to a second assignment after the one you just added which will add a period/dot at the end of the id. This demonstrates that assignments are performed sequentially in the order entered. In this case the id value is padded then the padded value is concatenated with the dot symbol. 
 + 
 +^Parameter ^Value ^Description^ 
 +|1. |Assign To |id |We are applying the assignment to the existing id column| 
 +|2. |Expression |{id} . "​."​|Use the PHP . operator to add a dot to the id value| 
 +|3. |Condition | |Leave blank.|
  
 ===== Create a cumulative expression ===== ===== Create a cumulative expression =====
-In this stage you will create ​new columns ​to appear on the report that represent ​a rolling ​minimum, maximum, sum and average.+In this stage you will create ​new column ​to appear on the report that is a rolling ​totals of the existing columns ( this is the way we can get totals ​and aggregates ).
  
-This time you need to manually add 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..+This time you need to manually add new assignment but to a new column ​that doesn't yet exist. So enter the report design mode and select the //Assignments// tab (Or use the //​Assignments//​ button). Now click on the //Add// button, then click on the resulting ​//Assignment Column// button and set the //Assign to New Column// field value to be **in_stock_total**. Set this to **sum({in_stock})** and press //Apply//. Note that you can also use the //Aggregates Wizard//, below to arrive at the same sum function ​function. Now add another ​assignment with Assign To New Column set to {on_order_total}
  
-Assign To Expression Description +You end up with some thing like this:-
-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.+{{:​tutorial1_3_1.png?​nolink|}} 
 + 
 + 
 +Run the report and check that your rolling/​cumulative ​value is set. 
  
 ===== Create a line count expression ===== ===== Create a line count expression =====
Line 60: Line 58:
  
 Enter the following parameters:​- Enter the following parameters:​-
-Parameter Value Description +^Parameter ^Value Description 
-1. Assign To New Column count To set the count column +|Assign To New Column|count|We are settingnew column "count" | 
-2. Expression  +|Expression ​|count()|Function to set the line count| 
-lineno() +|Condition| |Leave blank.
-Function to set the line count + 
-3. Condition   Leave blank.+It should look like this .. 
 +{{:​tut_1_2_2.png?​nolink|}} 
 + 
 +Running the report shuld now show the line count column. 
 + 
 +===== Create a conditional assignment ===== 
 + 
 +Here we are going to turn the in stock value red if it is less than the reorder quantity. So now go into the //​Assignments//​ panel and add a new assignment. Click on the new assignment and set the //Assign// field to **in_stock** then open the Output Styles Wizard. Leave the //Apply Style to// field as **CELL** and set the Text Colour to **#ff0000** which is red. Click on the //Apply// button and you should see the text  
 +<​code>​apply_style('​CELL',​ '​color',​ '#​ff0000'​);​ </​code>​ 
 + 
 +Now enter into the Expression field.. 
 + 
 +<​code>​{in_stock} < {ReorderLevel}</​code>​ 
 + 
 +And press apply. It should look something like :- 
 + 
 +{{:​tutorial1_3_1.png?​nolink|}} 
 + 
 +Now run your report and it show the "in stock" column in red when the value is less than the reorder level. 
 + 
  
 ===== Use an assignment within a criteria item ===== ===== Use an assignment within a criteria item =====
-In this stage you will use an expression within the film criteria item.+In this stage you will use an expression within the supplier ​criteria item.
  
-Enter report design mode and select the Criteria tab, then select the Criteria ​film button.+Enter report design mode and select the Criteria tab, then select the Criteria ​//​supplier// ​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 :-+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 supplier ​id and the supplier name. Add a new assignment and set the following parameters :-
  
 Enter the following parameters:​- Enter the following parameters:​-
-Parameter Value Description +^Parameter ^Value ^Description^ 
-1. Assign To New Column full To set the full description +|Assign To New Column|full|Names this assignment "full"| 
-2. Expression  +|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| 
-str_pad({film_id},​4,"​0",​STR_PAD_LEFT)."​.&​nbsp;&​nbsp;"​.{title} +|Condition| |Leave blank.|
-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.+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.+Run the report and click the film criteria expand(>>​) button. You should see the film list comprises both id and name.
  
 Checking Your Report Checking Your Report
 After you have successfully completed the above steps, check the whole 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.+Execute the report to get a list of films. On the report output you will see the first column of Id with values left-padded with zeroes. This is the result of the assignment that reformats the 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.+Also notice the **in stock** total column. They represent the cumulative ​total of the in_stock ​column. ​On the last row will be the grand total. (You will use to this to create a subtotal in the next stage).
  
-Notice the Line column is an expression assignment which is the report line number+Notice the **count** ​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.+Finally, return to the report entry screen and type some characters into the supplier box. You will see that the displayed list contains values that comprise the id concatenated with the supplier name. 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)