Dashboard buttons can be setup to run SQL Statements and display results. This can be used to quickly display the last 30 days of data for a variable, count the number of records written by a user/interface, etc...
1. Use Dashboard>Button in Spread Design
2. Choose Execute SQL for the Menu Item
3. Enter the button text in the Caption Text field.
4. Click ... to expand the SQL statement input box.
5. Enter a valid SQL Statement - For information on useful SQL statements, see the following articles:
WIMS Data Structures
(MS SQL) SQL Statement/Query Examples
(ORACLE) SQL Statement Examples
NOTE: Use the #SD#, #ED#, and #ED+1# buttons to insert a date range into the query. #SD# button will insert #SD# into the query. When the button is executed, the #SD# will be replaced by the Start Date as specified by the Dates Setting of the month.
#SD# - Replaced with the Start Date of the button's Dates Setting.
#ED# - Replaced with the End Date of the button's Dates Setting.
#ED+1# - Replaced with the End Date + 1 of the button's Dates Setting. This is useful when querying daily detail data and want to get data thru the end date. Example, if the End Date is set to March 31st and you query the hourly data table (DATADDH), you would not see data for March 31st at 1AM, 2AM... as that is after March 31st midnight. Therefore, use < #ED+1# and you will get all records before April 1st.
Cell() - Replaced with the value in the referenced cell. Example, ORDER BY CELL(B2) will become ORDER BY CURVALUE if cell B2 = CURVALUE.
Example 1: Show all records for variable 1 in the specified date range
1. Choose Execute SQL
2. Click ... to expand the SQL Statement input box.
3. Enter the title for the Display Results Form.
4. Enter the SQL Statement. SELECT DATESTAMP, TEXTVALUE FROM DATATBL WHERE VARID = 1 AND DATESTAMP >=#SD# and DATESTAMP <#ED+1#
5. Click OK to collapse the SQL input box.
6. Set the button caption
7. Set the Date Range for the query
8. Click OK to locate the button on the report.
On the dashboard, click the button and the results are displayed: