Tutorial 2 - Creating the order report

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 tutorial you will learn how to :-

Add a date range criteria item

Beginning the Tutorial 2 Report

Select Tutorial 2 - Orders 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=tut2_orders.xml). You are now ready to start this tutorial.

On entry to this tutorial, the SQL data query has already been set up. Run the report to see the output. You should see a list of orders borrowed. Press the Back button to return and then enter report design mode so that we can set up reporting by date.

Before configuring this report, it is a good idea to inspect the SQL statement we are using to generate report data. Press the Query Details to view the SQL statement. The SQL used in this report looks as follows :-

SELECT 
northwind_customers.CustomerID id,
CompanyName company,
ContactName contact, 
ContactTitle title, 
northwind_customers.Address,
northwind_customers.City,
northwind_customers.Region,
northwind_customers.PostalCode postal_code,
northwind_customers.Country country,
northwind_orders.orderid order_id,
date_format(orderdate,"%d/%m/%Y") order_date,
northwind_order_details.unitprice price,
quantity,
discount,
productname

FROM  
northwind_customers
join northwind_orders on northwind_customers.customerid = northwind_orders.customerid
join northwind_employees on northwind_orders.employeeid = northwind_employees.employeeid
join northwind_order_details on northwind_orders.orderid = northwind_order_details.orderid
join northwind_products on northwind_order_details.productid = northwind_products.productid
WHERE 1 = 1  
[ AND northwind_customers.customerid in ( {customer} ) ]
[ AND northwind_customers.country in ( {country} ) ]
order by northwind_customers.customerid, northwind_orders.orderid

This query pulls out a list of all orders and order items. The ordering allows the report to group by orderid so that order items can be shown together ( we can use this later to print an invoice ). Notice the use of the standard MySQL DATE_FORMAT function to format the dates into dd/mm/yyyy format before reporting.

Set up Date Range Criteria Entry

Click on the Criteria tab, and then press the Add button and press the resulting Criteria CriteriaName button. You should be presented with a set of criteria parameters.

Set these as follows :-

Parameter Value Comment
Name daterange The name of the criteria so it can be referred to elsewhere in the report
Title Date Range The title of the criteria item as it appears during criteria entry.
Main Query Column set to blank
Criteria Type Date Range Will ask the report user for a date range when entering crtieria for the report.
Criteria Display No Entry With a date range criteria this field is not relevant
Expand Display No Entry Indicates that no Expand option is applicable to this criteria item

To link this criteria item in with the main query you need to edit the main query. To do this select the Query Details option and add the following line after within the WHERE clause - i.e. after the line beginning WHERE but before the line beginnning ORDER :-

[ AND order_date between {daterange,RANGE1} and {daterange,RANGE2} ] This causes the report to replace the values between curly brackets ({}) with the lower and upper dates of your or criteria range.

Now test this out by returning to criteria entry mode and by running the report with different radio buttons selected.

Now that your criteria is set up, return to report execution by selecting Execute from the drop down list box. You should notice that you criteria is visible and you can change the date criteria. Note that if you change the from date then the to date will automatically be set to the same.

Setting a date range default

Now we are going to set a default period for the date range. Enter design mode and enter Criteria Tab and choose the daterange crtieria item and set the following

Parameter Value Comment
Defaults FIRSTOFLASTMONTH-TODAY The default report period be from the first of last month until today

This tells the report to default the date range from the first of the last month to the current day. It is possible to use other special code to default to yesterday, first of this month, last of this month etc. Press the help button against the defaults entry box to find the full list.

Finally, run your report and check that the criteria shows a date range of the the first of the previous month to current day.

  • tutorial_2_stage_1_-_stock_report_adding_a_date_criteria.txt
  • Last modified: 2018/05/04 21:40
  • (external edit)