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_1_-_creating_a_basic_report [2016/10/21 11:58]
127.0.0.1 external edit
tutorial_1_stage_1_-_creating_a_basic_report [2016/10/25 21:05]
admin [Enter the report data access query]
Line 6: Line 6:
   - Enter report design mode   - Enter report design mode
   - Set the report title and description   - Set the report title and description
-  - Enter the report data access query+  - Enter the report data access ​SQL query
   - Set report column labels and formatting   - Set report column labels and formatting
  
-===== Beginning the Tutorial 1 Stage 1 Report =====+===== Create A New Report =====
  
-Select Film Listing - Tutorial 1 Stage 1 from the tutorials menu (Alternatively you can point your browser at the URL  +Assuming you have configured the northwind project, enter Design Mode from the Admin page and enter the northwind project from the admin page and select the "​Create Report"​ option ​from the tutorials menu (Alternatively you can point your browser at the URL  
-<​code>​http://​{SERVER_ADDRESS}/​{REPORTICO_INSTALL_DIRECTORY}/​run.php?​project=tutorials&​execute_mode=PREPARE&​xmlin=tut1_1_films.xml)</​code>​+<​code>​http://​{SERVER_ADDRESS}/​{REPORTICO_INSTALL_DIRECTORY}/​run.php?​project=northwind&​execute_mode=MENU)</​code>​
  
- You should now enter a pretty empty report front end screen with a title of Set Report Title. You are now ready to start the first stage of building a basic report.+You should now enter a pretty empty report front end screen with a title of Set Report Title. You are now ready to start the first stage of building a basic report.
  
-===== Enter report ​design mode =====+===== Report ​design mode =====
  
-In order to access ​the report ​configuration screens, click on the Design Report button. You should then see the design screen with selection tabs Format, Query details, Assignments,​ Criteria and Output.+You are in design mode for the new report ​and you should then see the design screen with selection tabs Format, Query details, Assignments,​ Criteria and Output.
  
 Press the New Report button. Nothing really seems to happen, but if you were editing a completed report then this button would start from scratch again. Press the New Report button. Nothing really seems to happen, but if you were editing a completed report then this button would start from scratch again.
Line 24: Line 24:
 ===== Set the report title ===== ===== Set the report title =====
  
-The first configuration task to do is to set the report title by selecting the Format tab and locating the Report Title parameter. Enter the Report Title, say Film List. Then click in the Report Description text box and enter a free text description. Now save your entry by pressing the small Ok button on the right hand side of the format parameters. Every time one or more entries are made on a single screen, the appropriate ​Ok button must be pressed for them to be saved.+The first configuration task to do is to set the report title by selecting the Format tab and locating the Report Title parameter. Enter the Report Title, say "My Stock Report"​. Then click in the Report Description text box and enter a free text description. Now save your entry by pressing the green Apply button on the right hand side of the format parameters. Every time one or more entries are made on a single screen, the appropriate ​Apply button must be pressed for them to be saved (it is very easy to forget this and have to repeat the process each time.
  
-You will now see the correct report title displayed at the top of the screen. ​Now return to the report entry screen by selecting ​the Execute option from the Mode drop down list box. You should also see your text description on the right hand side. A report description can tell users what the report shows and how best to run it.+You will now see the correct report title displayed at the top of the screen. ​Press the Run Report button at the top. You should also see your title text description on the right hand side. A report description can tell users what the report shows and how best to run it. This is the screen you will run your report from, but we havent entered an SQL statement yet
  
 ===== Enter the report data access query ===== ===== Enter the report data access query =====
  
-Now return to Design mode to enter the query. Select the Query Details tab. You are presented with the SQL submenu tab which will contain a dummy query. Enter into this box the following query replacing what is already there and press then press the Ok button:+Now press return to Design mode to enter the query. Select the Query Details tab. You are presented with the SQL submenu tab which will contain a dummy query. Enter into this box the following query replacing what is already there and press then press the Ok button:
  
-<​code>​SELECT ​film.film_idfilm.titlecategory.cat_desccountry.country_name  +<​code>​SELECT ​ ​ProductID idProductName productnorthwind_categories.CategoryIDUnitsInStock in_stock, UnitsOnOrder on_order, ReorderLevel, ​ companyname Company, country, ​categoryname ​category 
-FROM reptut_film filmreptut_country ​country, ​reptut_category ​category  +FROM northwind_products  
-WHERE 1 = 1 AND film.cat_code ​category.cat_code +join northwind_suppliers on northwind_products.supplierid ​northwind_suppliers.supplierid 
-AND film.country_code ​country.country_code ​  +join northwind_categories on northwind_products.categoryid ​northwind_categories.categoryid 
-ORDER BY  ​country.country_name,​ title</​code>​ +WHERE 1 = 1   
-If no error is reported then the query has been accepted. ​Note that afterwards ​the query has been slightly modifiedEach column is now qualified with name ( for example ​you entered film.film_id and the query is now showing film.film_id film_id). Also the ORDER BY section has been modified ​to include ​the ASC operator ​to imply ascending order.+ORDER BY categoryname</​code>​ 
 + 
 +If no error is reported then the query has been accepted. ​ 
 + 
 +Now try running this. Press the "Run Report"​ button to return the report execution screenTo run the report you can use 4 buttons as follows :- 
 + 
 +{{:​runbuttons.png?​nolink&​200|}} 
 + 
 +The first button run the report as HTML output in new window. ** Note to use this option ​you will need to enable popups from your site **When you press the button for the first time therefore you may see a warning in the top of your browser which you can click to enable popups. 
 + 
 + 
 +Then try the second button which shows the HTML in the same window ( press the Back arrow button in the top left (not your browser back button) ​).  
 + 
 +Then the third and fourth buttons prints ​to PDF and CSV format. 
 + 
 + 
 +Now before we move on, just notice the following :- 
 + 
 +{{:​designbuttons.png?​nolink&​400|}} 
 + 
 +The SQL button is a short cut to entering the SQL without having to go to the Design Page. Similarly ​the others offer quick access ​to change report settings.
  
-Before continuing to the next section you may wish to try the report as it is. Select Execute mode and when you return to the Execute screen press the Execute button. You should see the report data. You will notice that the column headings are the names of the database columns. We will change these in the next stage. Return to Design mode before continuing. 
  
 ===== Set report column labels and formatting ===== ===== Set report column labels and formatting =====
  
-Enter Design ​mode and select the Query Details tab. Then select ​the Query Columns sub-tab. You will see the list of data columns relating to the query you have entered. You will now set the column label of the cat_desc column. Click on the cat_desc ​column and you will see the column parameters. Locate the Column Title field and enter the text Category ​and then press the second ​Ok button. You can do this if you wish for each column, capitalizing and altering the column label text. We will now make the film_id ​column right-justified. Select the film_id ​column, locate the Justification parameter and change it to right.+So lets change some column settings. You can access these options by clicking the Columns button or by pressing the  "Design ​Report"​ button ​and select the Query Details tab followed by the Query Columns sub-tab. You will see the list of data columns relating to the query you have entered. You will now set the column label of the cat_desc column. Click on the ReorderLevel ​column and you will see the column parameters. Locate the Column Title field and enter the text "​Reorder Level" ​and then press the second ​Apply button. You can do this if you wish for each column, capitalizing and altering the column label text. We will now make the ReorderLevel ​column right-justified. Select the ReorderLevel ​column, locate the Justification parameter and change it to Right.
  
 ===== Checking Your Report ===== ===== Checking Your Report =====
  
-Now return to execute mode and run the report. You will notice your column labels have improved and the Film Id column is right justified.+Now return to execute mode and run the report. You will notice your column labels have improved and the Reorder Level column is right justified.
  • tutorial_1_stage_1_-_creating_a_basic_report.txt
  • Last modified: 2018/05/04 21:40
  • (external edit)