Home : Dashboard Buttons - Execute SQL Examples
Q14072 - INFO: Dashboard Buttons - Execute SQL Examples
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:

 

Related Articles
No Related Articles Available.

Article Attachments
No Attachments Available.

Related External Links
No Related Links Available.
Help us improve this article...
What did you think of this article?

poor 
1
2
3
4
5
6
7
8
9
10

 excellent
Tell us why you rated the content this way. (optional)
 
Approved Comments...
No user comments available for this article.
Created on 3/22/2013 8:45 AM.
Last Modified on 4/2/2020 12:07 PM.
Last Modified by Scott Dorner.
Article has been viewed 10105 times.
Rated 6 out of 10 based on 6 votes.
Print Article
Email Article