Group Builder
 |
When creating a new database data group, the following group types are available:
Standard
The output is arranged in columns by the selected table columns.
For example: A table in a database contains columns DateAndTime, TagName and Value so the output of a standard query produces:
DateAndTime
TagName
Value
1/1/05 12:00
Flow
100
1/1/05 12:00
Temp
200
1/1/05 12:00
Pressure
300
1/1/05 12:01
Flow
105
1/1/05 12:01
Temp
205
1/1/05 12:01
Pressure
305
Cross Tab
The output is arranged in rows/columns by the values of the selected table columns.
For example: With the TagName as columns and DateAndTime as the rows, the data produces:
DateAndTime
Flow
Temp
Pressure
1/1/05 12:00
100
200
300
1/1/05 12:01
105
205
305
Switching the columns and rows produces:
DateAndTime
1/1/05 12:00
1/1/05 12:01
Flow
100
105
Temp
200
205
Pressure
300
305
Additional sophistication can be added to the output by adding summary calculations. Instead of raw values, calculate summary values over the hour to produce:
Hour of DateAndTime
Flow Sum
Flow Avg
Temp Sum
Temp Avg
Pressure Sum
Pressure Avg
12
205.00
102.50
405.00
202.50
605.00
302.50
Setup Tab
The Setup tab is used to select the table(s) and/or views from the database.
 |
The Reconnect button can be used to retrieve the latest list of tables and/or views from the connector.
The View button displays the connection string used to connect to the database as created in the connector.
The available list can be filtered to show Tables, Views, System Tables or a combination thereof. In addition, a textual filter can be applied to help narrow down the list to the Tables and/or Views needed which can be helpful if the database has many to choose from.
The Filter supports the * wildcard which can be used to narrow down the list. For example, if set to ABC*, only Tables/Views that start with ABC are listed. If set to *ABC*, any Table/View that contains ABC are listed. If set to *ABC, only Tables/Views that end with ABC are listed.
The number of records in the output can be limited by the Records to Fetch setting.
When more than one table/view is selected, the Joins indicates the relationship between them. For complex joins between many tables the user is advised to create a View in the database and use that directly. The following join conditions are supported:
Inner Join
This produces as many records as there are records where there is a match between the joined column values.
Left Join
This produces as many records as there are in the left table, producing null values for the column values from the right table on the records where there is no match on the join columns.
Right Join
This produces as many records as are in the right table, producing null values for the column values from the left table on the records where there is no match on the join columns.
Full Outer Join
This produces as many records as there are when there is a match in the left OR right (table2) table columns.
Columns Tab
The Columns tab is used to select the columns used in the output of the group.
 |
Use the arrow buttons to move columns from the Available Columns list to the Selected Columns list. To move the position of a column in the selected list, use the up/down arrow pushbutton.
Enable Show Headings to have the headings included in the output.
 |
From the Available Columns, make a selection for Columns and then using the browse […] pushbutton, indicate the values that will determine the columns of the output.
From the Available Columns, make a selection for Rows to indicate the rows of the output and make a selection for Values to indicate the content of the output.
Enable Show Headings to have the headings included in the output.
These items in the Available Columns list provide the capability to derive new columns from the existing by applying functions and calculations. To add an item, highlight one of Expression, Summary or Function then right click and select Add Item to open the appropriate dialog.
This option is only available with Standard Groups.
This item in the Available Columns list provides the capability to display a static text expression alongside the fields returned from the database. To add an item, highlight Static Text, right click, and select Add Item.
 |
This feature can be used, for example, to display the units GPM adjacent to a field representing gallons-per-minute. It can also be used to display Variables, e.g. {RG000} or {Units} or can be used to just return an empty column from the group (to account for a formula in the worksheet).
Filters Tab
The Filters tab is used to define a filter for the output.
 |
The filter can be defined either by manually entering it under Filter Condition or by selecting items under Columns, Conditions and Values Once the Value is specified, the settings appear in Filter Condition. To add multiple conditions, use And/Or.
The filtering conditions are: =, <>, >, >=, <, <=, LIKE, NOT LIKE, BETWEEN, NOT BETWEEN, DURATION, IN, NOT IN, IS and IS NOT.
The filter value can be fixed or be a variable enclosed in {}.
Note that if the Condition IS or IS NOT is selected, the Value list just contains NULL. This allows for the IS NULL and IS NOT NULL filters to be configured.
Order Tab
The Order tab is used to define the ordering of the output.
Group Tab
The Group tab is used to define the grouping of the output. Note that this is used when there are summary columns configured on the Columns tab.
Calculations Tab
The Calculation tab is used to define the client-side calculations which will appear as sub totals and grand totals in the output given by the Scope.
Grand
Grand provides a calculation for the entire set of data returned. For example, if the group returns temperature records over the day, this can be used to calculate the minimum and maximum temperature over the day.
Sub
Sub provides subtotal calculations based on the column selected. The columns listed are all of those set up under the Order tab. This will provide a calculation after each distinct value for the selected column.
If a datetime column is selected, additional date options become available (XLRyear, XLRmonth, XLRday, XLRhour, XLRminute, XLRsecond).
For example, if the group returns the timestamp and temperature records over the day, Sub can be used along with the XLRhour of the timestamp to calculate the minimum and maximum of the temperature over each hour of the day.
Type
The calculation to perform. The following are available:
total
minimum
maximum
average
count
On
The column to perform the calculation on.
SQL Tab
SQL Statement
If the query is constructed using on the previous tabs, the SQL statement parameter displays the SQL statement based on the settings.
Custom Query
An alternate approach to designing the query is to use the SQL tab exclusively. If the query has not been formed by the previous tabs, the SQL Statement is editable so that a query may be manually entered using any SQL syntax acceptable by the database engine underlying the Connector.
Stored Procedure
A stored procedure is a set of SQL statements with an assigned named that can be run by issuing an EXEC or EXECUTE command.
Stored procedures are processed by XLReporter in two ways, depending upon the configuration of the procedure.
If the stored procedure is used to populate a table in the database, the procedure must be executed in the Stored Procedure parameter so that the table can be queried by the statement in the SQL Statement parameter. At the runtime of the data group, the stored procedure is processed first, followed by the SQL statement.
If the stored procedure returns a record set directly, then the stored procedure must be executed in the SQL Statement parameter. With this approach, the results of the stored procedure are returned to the Data Group, and then to an underlying report in the same way as a standard SQL statement/query.
To run a stored procedure, the syntax is:
EXEC stored procedure name [parameter=value],[parameter=value]
Where:
stored procedure name is the name of stored procedure configured in the database.
parameter=value is the parameter/value pair for any parameter defined for the stored procedure. The value can be hard coded text, an XLReporter variable or Name Type keyword.
References
When a data group to a database is used it may appear with the .sql extension. This indicates that the connector for the group is a history or analytic connector and not a general database connector.