The Query Details Menu

Query Details Menu Overview

Query Details => SQL Tab

SQL Query

The SQL query text that retrieves the main report data. There is nothing to stop you entering statements that comprise outer joins, groups, complex expressions ( if supported by the appropriate SQL engine ).

The entered query must comprise at minimum a SELECT clause and a WHERE clause. An ORDER BY clause is also necessary.

The SELECT clause must not contain the star(*) notation. This is due to the way Reportico parses the SQL. Therefore explicit column names must be provided. Each column specified is stored in the report definition identified with a name. By default this is the column name itself, however a more appropriate name can be provided as a column alias. Also, if expression columns are specified then these should be provided with column aliases otherwise Reportico will automatically name them in the form columnx where x is an incremental number. It is sensible to provide meaningful names in this instance. The column names are important as they can be referred to elsewhere in the report when using the columns within for example groups, graphs and assignments.

For example, the query

    SELECT customer_id,
    customer_name, count(*) order_count FROM order WHERE 1 = 1 ORDER BY
    customer_id GROUP BY 1, 2

will produce report output with column titles "Id", "Customer Name" and "Order Count".

Now we have 3 report columns customer_id, name and order_count which can be referred to elsewhere in the report. Note also that the ORDER BY CLAUSE gets expanded to the appropriate order type.

If you are providing a user from outside of reportico using the external_user then you can use this in your queries to filter by user by writing something like :-

SELECT col1, col2
    FROM table1 
    WHERE user = {FRAMEWORK_USER}

when you try to do this you will get a syntax error, but ignore the error and it will be replaced by the user id at report run time.

Limit/First

Causes the report to only output the specified number of rows

Skip/Offset

Causes report output to start from the record number specified here

Linking in Criteria Values

Additions can be made to the entered SQL Query that will cause entered user criteria to be linked in at report generation time. Normally this linking in is configured within the criteria item itself by filing in the Query Table Name and Query Column Name fields in the relevant criteria section. However, it is useful here where the way the criteria links in is not so simple.

To link a criteria item in, place the where clause between square brackets. If the user did not specify any values for the criteria item then the clause is not included in the main report query since in specifying no values for a criteria this indicates not to filter on this criteria item at all.

An example SQL statement may look as follows :-

SELECT customer_name, order_no, order_date 
FROM customer, orders 
WHERE 1 = 1 
AND customer.customer_id = order.customer_id 
[AND customer.country IN ( {country} )] 
[AND order_date BETWEEN {date,RANGE1} AND {date,RANGE2} ]
ORDER BY order_date

In the above example we are assuming a criteria item country exists that presumably allows selection of a set of country codes. Also a criteria item of date exists which is defined as a Date Range criteria.

At run time, if the user has selected some countries then the {country} clause is replaced with the comma delimited values which relate to the Return Column parameter as entered against the {country} criteria item. These will probably relate to a country code column within a country table. See the criteria section for more details.

The {date} criteria item has been defined as a date range criteria entry type since we are able to extract the user's entered start and end dates by using the RANGE1 and RANGE2 keywords. The format of the dates entered here relate to the Reportico configuration constant SW_DB_DATEFORMAT. See Appendix 1 - Reportico Project Configuration parameters for more details.

Note that when you use the {} notation to link in a criteria item, the value is always replaced by the criterai value with quotes added. Sometimes you do not want quotes added. In which case you need to add a final parameter of false to turn off automatic quoting. An example use of this is when you want to allow the user to enter a match string to include in a LIKE or MATCHES statement. If the user wants to match all people with Smith in their name then the user should be able to just enter Smith. To handle this the cluase would look something like :-

[AND name LIKE "%{matchcriteria,false}%" ]

Query Details => Query Columns Tab

The Query Columns tab presents a list of all the report columns that are fetched within the query and those that have been manually added that will contain calculations as set up under the Assignments tab. Selecting any of these column presents the column parameter screen where you can set up the column display parameters.

Add

This Button creates a new manual column. This will not form part of the main query but acts as a place holder for a calculation value set up in the Assignments tab. After adding a column it will appear named New Column in the column list. You must change its name by clicking on this button and altering the Name value.

Query Column Parameters

Column parameters can be changed by clicking the relevant column button. This presents the options below :-

Show or Hide?

Indicates whether this column should appear in the main report body. Since reports are tabular, you may wish a particular column not to appear because it will appear only in a group header or is used for a further calculation. Or maybe you want it selected but just not shown at this moment. To prevent a column showing select the hide option. By default, this attribute is set to show.

Content Type (PDF)

By default this is set to plain. However if the column represents a graphic selected from the database then this must be set to graphic for it to appear correctly in PDF reports.

Column Start (PDF)

Indicates the horizontal position of the column on PDF reports only. As with other measurements, the value may be specified in cm, mm or if no measurement is specified then points will be used.

If left blank then the column is automatically placed.

Column Width (PDF)

Indicates the width taken up by the column in PDF reports only. As with other measurements, the value may be specified in cm, mm or if no measurement is specified then points will be used.

If left blank the column will be automatically sized.

Column Width (HTML)

The width of the column as presented on HTML reports. This will be used within the "width" CSS style of the <TD> tag that contains the column value. To specify a value in pixels specify suffix of px, to specify a percentage value add a percent sign. The default is 100%

Automatic sizing is used if this is left blank.

Column Title

The title of this column. This provides the text describing the column and is displayed in the column header section on the report. If this is left blank then the column name is used (but auto-capitalised with underscores removed).

This also provides the default label for the column when it is displayed as a group header or a group trailer.

Group Header Label

The description label displayed next to the column value when displayed as a group header. By default this will revert to the Column Title value.

Group Header Label Start (PDF)

The horizontal start position of the group header label for this column. Defaults to the left margin.

Group Header Value Start (PDF)

The horizontal start position of the group header value for this column. Defaults to 250 points along from the label start.

Group Trailer Label

The group trailer label text for this column. Often a label is not required and can be removed by entering BLANK here. If required then it will default to the Column Title value if unspecified.

Query Details => Pre-SQLs

Pre-SQLs are SQL statements that are run prior to the main report query and are used mainly to populate temporary tables to provide data to the main report query. They may be used to set a database mode. It is also possible to enter DELETE, UPDATE and transaction control statements here to allow reports to actually performed modifications to your database.

Note that SQL entered may not be entered here when running in SAFE mode as the user could create SQL statements here that could delete and update data in the database. For instructions on how to turn on and off Safe Mode see the Reportico Security section.

Pre-SQLs are executed in the order that they appear in the list. You can use the delete button to remove a Pre-SQLs or the move up and down buttons to change the execution order.

Add

This Button creates a new Pre-SQL statement. To edit the query text click on the relevant Pre-SQL x button.

Pre-SQL Text Entry

The text should be a valid SQL statement acceptable by your database engine. It is not necessary to provide a closing semicolon.

Linking in Criteria

You can embed user criteria values into these statements by embedding references to criteria items in the same way as when embedding criteria references into the main report query or criteria queries. See linkcrit for more details.

So, for example, the following Pre-SQLs would create a temporary table new_customers, fill it with customers relevant to a country and then remove all customers who have not ordered anything . This would then allow the main report to easily report on customers from the selected country who have not ordered anything by joining in this table:-

INSERT INTO new_customers 
SELECT customer_id 
FROM customers 
WHERE 1 = 1 
[AND country IN ( {country,VALUE})]

DELETE FROM new_customers 
WHERE customer_id NOT IN 
       ( SELECT customer_id FROM orders)