Tutorial 2 - The order report - adding Date Criteria

Note that this tutorial requires SAFE mode to be off. This is described back in tutorial 1 stage3

In this tutorial you will learn how to :-

Add a date range criteria item, and set a default criteria value.

Beginning the Tutorial 2 Report

Select Tutorial 2 - Orders Working with Date Criteria 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. Also notice in the criteria screen that the country criteria is defaulted to UK. This is done in the criteria defaults option. 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 :-

northwind_customers.CustomerID id,
CompanyName company,
ContactName contact, 
ContactTitle title, 
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,

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 loanDate The name of the ecriteria 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
Defaults FIRSTOFLASTMONTH-TODAY The default report period be from the first of last month until today

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 loan_date between {loanDate,RANGE1} and {loanDate,RANGE2} ] This causes the report to replace the values between curly brackets ({}) with the lower and upper dates of your loanDate 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 the default dates to run for are from the start of this month until today. Now experiment with the dates by choosing an earlier period and execute the report by pressing the Execute button.

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