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

Link to this comparison view

tutorial_2_stage_1_-_stock_report_adding_a_date_criteria [2018/05/04 21:40] (current)
Line 1: Line 1:
 +==== 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 :-
 +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,
 +FROM  ​
 +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)