The Query Details Menu

Here you enter the main report query for your report. This can be a straight select but it could be final select which selects for data already prepared using Pre-SQLs or Custom Source Code

There are 3 tabs here :-

  1. Query Details where you enter your main report SQL query.
  2. Query Columns where you set display attributes of your result columns ( e.g. justification, width )
  3. Pre-SQLs where you can enter SQL to be executed prior to the main report query ( e.g. for preparing temporary tables for the final select ).

SQL Query

The SQL query text that retrieves the main report data. There is nothing to stop you entering statements that comprise left joins, groups, subselects 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 if you are grouping because you probably want to order by the column you are grouping on.

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 the column name.

By default the title of the column shown in the report will be this column name, but you can use a column alias to provide a more meaningful name. You must enter an alias for expressions. An alternative name for a column can also be set in the Query Columns tab.

It is sensible to provide meaningful names for columns. 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.

Filtering Report Output by User and other external parameters

You can provide a user id from outside of reportico by specifying the external_user in the runner ( see Reporting By User. Then you can include the user id in your queries to filter by user by writing something like :-

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

Note 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.

You can also specify other parameters using the USER_PARAM notation

SELECT col1, col2
    FROM table1
    WHERE column1 = {USER_PARAM,external_parameter}

See Using External Parameters

Linking in Criteria Values

One of the key features of Reportico is to allow criteria selected by the user to be included witin the query statement. The best way of achieving this is to use the curly bracket “{criterianame}” notation to link criteria in at report generation time. Alternatively 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 linking within the SQL using this method is always preferable because it allows for more complex ways of using the crtieria.

To link a criteria item in, just construct an where clause statement for the criteria, specifying the name of the criteria .. e.g.

    SELECT x 
    FROM y
    WHERE 1 = 1
    AND column1 = {criterianame}

Note when the report runs it adds quotes to the selection so you don't need to.

For criteria items which can result in multiple selection values you can use the SQL in notation

    SELECT x 
    FROM y
    WHERE 1 = 1
    AND column1 IN ( {criterianame} )

The criteria element will be replace by something like 'x', 'y', 'z'

Excluding criteria if user doesnt specify any

However if the user does not specify a criteria then you probably dont want to include this in the where clause ( because the criteria would be empty ). In this case put square brackets around the whole line e.g.

    SELECT x 
    FROM y
    WHERE 1 = 1
    [ AND column1 IN ( {criterianame} ) ]

If the user did not specify any values for the criteria item then the clause is not included in the main report so all rows are returned.

Date ranges

When you use a date range criteria you can do this in one of two ways.

Either by

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

In the above {daterange} will be replace by “BETWEEN 'yyyy-mm-dd' and 'yyyy-mm-dd'

Alternatively you can access both dates in the range separately using the RANGE1 and RANGE2 paramters

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

In the above examples we assume there is criteria item set up of daterage exists which is defined as a Date Range criteria.

Also we are assuming a criteria item country exists that presumably allows selection of a set of country codes.

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. Dates will automatically be fed to the database in the format defined in the project configuration page specified as “Database Date Format”.

Excluding the quotes / using criteria in LIKE clauses

Note that when you use the {} notation to link in a criteria item, the value is always replaced by the criteria 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}%“ ]


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.

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.

Justification

Indicates whether column should be justified right, left or centred.

If left blank the column will be automatically sized.

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.


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:-

PreSql0CREATE TEMPORARY TABLE new_customers ( customer_id int )
PreSql1INSERT INTO new_customers SELECT customer_id FROM customers WHERE 1 = 1 [AND country IN ( {country,VALUE})]
PreSql2DELETE FROM new_customers WHERE customer_id NOT IN ( SELECT customer_id FROM orders)
  • the_query_details_menu.txt
  • Last modified: 2018/05/04 21:40
  • (external edit)