Create a SQL-Based Report

SQL-based reports are defined using explicit SQL. After creating and saving a report, it can be scheduled to run automatically in the Automation Rules module.

The following describes how to create a SQL-based Report to report on a device and variable parameters will be used to prompt the user to type in the name of a device when running the report.

1. Navigate to Management > Reports.

2. Select Add, then select Add SQL-based Report. Name the report and add a brief description.

3. Set Max. Result to a manageable maximum. A Max. result of -1 displays unlimited results.

4. For SQL, enter a SQL query to generate the desired report variables. For this example, enter the following query into the SQL field:
   select * from vx_dev where name=? (Select all from the vx_dev table where the device name matches the user selection)

   • Note: For every parameter defined using Variable Parameters, use a prepared statement (question mark placeholder) to bind variables to user input.

5. Select Variable Parameters.

6. In Type, select the parameter type. For this example, select Devices and press OK.

7. Enter a user prompt for the parameter, then specify the Device type.

   • Note: Because variable parameters will replace the question mark placeholders in the SQL query, the number of parameters must match the number of question marks in the query.

8. In Parameter, select the parameter and press OK.

9. Press Save and Close.

10. To run the report, go to Reports and press Run. The Report Parameters window opens and prompts the user to select a predefined device to report on. Select the device and press OK.