The first article of the series about the new generation of database reporting tool mentions most important additions: preview, macros, parameterization, etc. This article describes and provides a few examples of report components customization: title, header, and footer.
Let's review the structure of the report. Any report has only one mandatory part. It is a table with data rows based on result of the query execution. Depends on the query or script, the report can contain one or more tables with the result set.
Unlike the first generation of the reporting tool, the new version allows users to assign not only report title but also header and footer. There are two modes. In the first case, the report has only one header and one footer. In the second mode, each table with result set has header and footer. he second mode is a default and can be changed at the settings window. As mentioned above, all these parts (title, header, and footer) are optional.
In addition to text changing, there are four ways to customize title, header, and footer:
- Paragraph size or type selection
- Macros
- Embedded query
- Parameters
The assigning of size is the simplest way to modify object's component presentation. It has different mean for HTML/CHM and other formats. For HTML output the size is a size of <Hn> tag but it is a font size only for other formats. Of course, this option does not compatible with XML and text output.
The macro is a very powerful way to tune component of the report at the run-time. The reporting tool will replace macro name to actual value. For example, $DATE$ and $TIME$ macros allow customers to include current date and time to the report. Also, these macros have special versions to access date and time components: $DATE.YYYY$, $TIME.MM$, etc.
$QUERY$ macro corresponds to whole SQL script used for the report. $QUERYn$ is a current SQL statement. For example, if the script consists of four SELECT statements the user can specify each statement text in the header of the record set with this macro. $STATEMENT$ macro can be user to access to total record set number of the script or procedure call. Another useful macro $ROWS$ will be replaced to a number of data rows retrieved for current SQL statement of the script. It is supported for footer object only and very useful for some kind of "total" description.
Each object (title, header, footer) can contain one embedded SQL query defined between {{ and }}. The query should return one scalar value, the program will ignore any other results. Please be noticed, that the reporting tool uses the same connection for mentioned queries.
This sample shows how to add maximum value and a total number of fetched rows to the footer of the report. It uses both macro and embedded SQL query:
The parameter is a way to replace any part of the report or whole report component (title, header, footer) with the user-defined value from an external file. Moreover, the parameters can be used in output file name and text of SQL query/script.
The value file can be assigned as command line parameter to customize the unattended (user interface free) report execution. The file with parameters is a text file with one parameter definition per line:
?<number>=<value>
This example shows how to use customer code from value file and use the parameter (?1) in the query and report header:
The file with parameters value content:
The SQL query with ?1 parameter:
The report header with parameter:
The report preview for the example:
The next article in the series will describe markers and column properties features of the reporting tool.