You need to restrict the amount of data that a report that queries a historic table can retrieve from the server as soon as you create the report. This will help to reduce any adverse affects on the communications on your system, caused by a report attempting to retrieve large amounts of historic data from the server (see Important Guidelines on Configuring and Generating Reports). ClearSCADA displays a diagnostic message should you attempt to retrieve historic data for a report that has no time constraint specified. However, you should still check that any time-period specified for a report is short enough to avoid causing any unnecessary adverse affects on the performance of your system.
We suggest that you filter a report that queries a historic table:
- To an individual point in the database (for reports that query the CDBHistoric table, CDBHistoricAll table, or a ‘custom’ Historic View)
and
- By Time (by limiting the maximum period for which a report can retrieve data, and also by using Parameters to allow users to further restrict the time-period for which historic data is retrieved).
When restricting such a report by time, use the RecordTime field in the CDBHistoric table or CDBHistoricAll table, rather than the Time field. (As ‘Time’ is a reserved word in SQL.)
To restrict a report by Point and Time, you use the Select Expert window. For examples of how you can use this window to limit the ClearSCADA data that is included in a report, see below, and see Limit the Data that is Included in a Report. For information on using the Select Expert window, see the documentation provided with the Crystal Reports application.
You can also use Formulas within your reports. For example, to restrict a report to retrieve only the data that occurred during particular a week or month. You write formulas using Crystal syntax. For information on formulas and Formula fields, see Formula Field, and see the documentation provided with the Crystal Reports application.
A report is used to retrieve the data for the DNP3 driver point, AIP4, for the previous week. The report references both the Historic table, CDBHistoric, and the Points table, CDBPoint. The tables are linked by Id field.
In order to retrieve historic data for only the point AIP4, the FullName field is used to restrict the CDBPoint table to entries with the name ‘DNP3 Group.DNP3 Direct.AIP4’. (The FullName field is used in preference to the Name field so that only that particular point is selected—the Name field would include any other points that have the same name (AIP4) but reside in different locations in the database.)
The Select Expert window is used to select the FullName field and specify the required restriction:
To restrict the report to retrieve historic data for the past week, the Select Expert window is used to select the RecordTime field from the CDBHistoric table and specify the required restriction (the restriction Last7Days is specified using the Formula Editor and selecting the entry from the list of Date Range functions):
The resulting report is restricted to retrieving data for the DNP3 point AIP4, for the previous week. For the week starting 3rd January 2005 and ending at midnight at the start of 10th January 2005, this is the resulting report:
(This information in this particular report is displayed using the Name field from the CDBPoint table and the StateDesc, Value, and RecordTime fields from the CDBHistoric table.)
The report used in Example 1, above, is further restricted by using a selection window. The window enables users to further limit the amount of data that the report is to generate, by specifying the start and end dates for that data.
To enable time selection in this way, a Parameter Field is added to the report.
The Parameter field, named RestrictTimePeriod, contains this configuration:
- Type—DateTime. (The RecordTime field contains date and time values.)
- Value Field—None. (The parameter does not reference a field in the database.)
- Prompt Text—Specify the period for which data is required. (This is the prompt that is displayed whenever a user attempts to generate the report.)
- Allow Range Values—True. (Enables users to specify a value range.)
Once the Parameter field has been added to the report, the Select Expert window is used to restrict the RecordTime field entries to those specified using the Parameter field:
Whenever a user attempts to generate the report from ViewX or a WebX client, a selection window is displayed, prompting them to specify the time period for which the report is to include historic data.
This allows the user to restrict the amount of data that needs to be retrieved from the server in order to generate the report, by selecting just the time-period that is of interest.
For another example of using a selection window to restrict the data that is included in a report, see Allow Users to Specify which Data is Included in a Report.
Further Information
Other historic tables that you might want your reports to reference: see Other Tables in the ClearSCADA Guide to the Database.