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 section.

In this stage you will learn how to :-

Create a string expression column

Create a cumulative 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

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 the same report that you created in stage 2.

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 (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:-

Parameter Value Description
1. Assign To id We are applying the assignment to the existing id column
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.
3. Condition Leave blank.

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.

In this stage you will create a 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 a 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}

You end up with some thing like this:-

Run the report and check that your rolling/cumulative value is set.

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:-

Assign To New ColumncountWe are settingnew column “count”
Expression count()Function to set the line count
Condition Leave blank.

It should look like this ..

Running the report shuld now show the line count column.

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

apply_style('CELL', 'color', '#ff0000'); 

Now enter into the Expression field..

{in_stock} < {ReorderLevel}

And press apply. It should look something like :-

Now run your report and it show the “in stock” column in red when the value is less than the reorder level.

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 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 supplier id and the supplier name. Add a new assignment and set the following parameters :-

Enter the following parameters:-

Parameter Value Description
Assign To New ColumnfullNames this assignment “full”
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
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 id and name.

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 Id with values left-padded with zeroes. This is the result of the assignment that reformats the Id value.

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 count column is an expression assignment which is the report line number

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)