Database Data Group - 1 -
Database Data Group
Overview
A Database Data Group is built with a Database connector, such as a SQL Server or Access, to
extract raw or summary values from a database.
The database group builder interface consists of several tabs which, collectively build an SQL
statement.
Quick Access
Data Groups can be accessed for modification by selecting a template from the main window of the
Project Explorer and clicking the group from the template items in the bottom of the Left View.
Another direct method is to use the Connector Group application for creating and modifying Data
Groups. From the Project Explorer select Tools tab, Diagnostic, Connector Groups to display all
the connectors defined for the project and the data groups that are using them.
If a data group references a connector that does not exist, it is displayed with a red image. In this case,
the group can be re-assigned to a different connector by selecting Assign and choosing the new
connector.
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.
Database Data Group - 2 -
For example: A table in a database contains columns DateAndTime, TagName and Value so
the output of a standard query produces:
• 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
Temp Sum
Temp
Avg
Pressure
Sum
Pressure
Avg
12
405.00
202.50
605.00
302.50
Setup Tab
The Setup tab is used to selecting 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.
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
Database Data Group - 3 -
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.
Standard 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.
Database Data Group - 4 -
Cross Tab Group
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.
Expression/Summary and Calculations
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.
Static Text
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).
Database Data Group - 5 -
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.
Database Data Group - 6 -
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.
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.
Calculation
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.
Database Data Group - 7 -
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.
Preview
Preview is opened from the Preview menu option. To preview the output of the group settings, click
the Refresh pushbutton.
Multiple instances of Preview can be opened to compare data.
If the group contains Variables, they are listed in the display so that values can be specified.
Variables
When variables are used in database data groups, their usage becomes more flexible since the variables
can be given a value when it is deployed.
The following database group settings can be specified as a variable:
• Static Text
• Filter Conditions
• Stored Procedures
Database Data Group - 8 -
Information in this document is subject to change without notice. SyTech assumes no responsibility for
any errors or omissions that may be in this document. No part of this document may be reproduced or
transmitted in any form or by any means, electronic or mechanical, for any purpose, without the prior
written permission of SyTech.
Copyright 2000 - 2023, SyTech. All rights reserved.
XLReporter
®
is a registered trademark of SyTech (dba of TheReportCompany, LLC).
Microsoft
®
and Microsoft Excel
®
are registered trademarks of Microsoft, Inc.
All registered names are the property of their respective owners.