TN 010: Using a Multi-Select Combo Box with a SQL Query - Dream Report 4.6 R3

Owned by Admin
Last updated: Dec 29, 2020
4 min read

In this example, we will use a Multi-Selection Combo Box to enable a dynamic set of fields to use in a SQL query, and to pass a list of possible values to use in a SQL “Where…” expression. This example uses the sample “Process” database (process.mdb) installed with Dream Report

Note: The following two lines must be added to the DreamReport.ini file,located in \ODS\Dream Report\System

[AutoAddQuotes]

Use=no

  1. In Dream Report Studio, select the DSN Manager from the “Tools” toolbar, and create an ODBC DSN (Data Source Name) named “dsnProcess”, which references the “Process” DSN:


  2. Add a Combo Box (from the Web Elements tab) to a new Web report, and name the Combo Box “cb_User"

  3. Set the “Display Mode” to “Display as Selection Box”, and ensure that the “Multi-Selection” option is checked

  4. Configure the “Display Data from:” option. In this example, we will execute a SQL Query to get a list of users from the Batch_Data table in the Process database. This list could also be populated with a manually-entered list of items, Batch ID’s, etc.

  5. In this example, we will execute a SQL Query to get a list of users from the Batch_Data table in the Process database:

  6. Add a second Combo Box to the report, named “cb_Tag”

  7. Set the “Display Mode” to “Display as List Box”, and ensure that the “Multi-Selection” option is checked

  8. Configure the “Display Data from:” option to be “Manual Values”, and enter Tank1, Tank2 and Tank3 as available options:

  9. From the right-side toolbar, select and add an Expression Data object to the report, and name the object “Expr1”

  10.  In the expression area, type in the following function, which references the first Combo Box (“cb_User”): OdsObjComboGetSelectedListQuoted('[on#cb_User]')

  11. Under the “Result Representation” tab, you may choose to un-check the “Visible” option, but during development, it is a good idea to keep this visible, to verify what is being passed into the subsequent SQL query

  12. From the right-side toolbar, select and add another Expression Data object to the report, and name the object “Expr2”

  13. In the expression area, type in the following function, which references the second Combo Box (“cb_Tag”): OdsObjComboGetSelectedList('[on#cb_Tag]')

  14.  From the right-side toolbar, select and add a SQL Query Table object to the report

  15.  Select “dsnProcess” from the “ODBC Database Source”

  16.  For this query, we will query a list of tank level tags (which will be passed in from the “cb_Tag” combo box) and a counter value from the Batch_Data table, where the Counter=500 – i.e., this query has a dynamic “Select…” statement, and a fixed “Where…” expression. Enter the following SQL Query:Select [f#Expr2], Counter from Batch_Data where Counter=500



  17. From the right-side toolbar, select and add another SQL Query Table object to the report

  18.  Select “dsnProcess” from the “ODBC Database Source”

  19.  For this query, we will query a list of Motor tag fields (which will be passed in from the “cb_Tag” combo box) from the Batch_Data table, where the Counter=500 AND the User is one of the Users selected in the “cb_Users” combo box – i.e., this query has a dynamic “Select…” statement, and a dynamic “Where…” expression. Enter the following SQL Query: Select [f#Expr1], User from Batch_Data Where Counter=500 And User IN ([f#Expr2])

  20. Add an Action Button (from the Web Elements tab) to the report, with the Button Caption “Refresh”, and the action being “Refresh Report”

  21. The resulting report should look similar to this in Studio:



  22. Running the report in a web browser, with user-selections in the Combo boxes, results in the following: