Dynamically Enabled Parameters

Trouble seeing the images? Right click on images and open in new tab to enlarge or zoom in on the page (Ctrl + mousewheel).

Parameters can be enabled or disabled based on another parameter's selected Value.  This allows a driving parameter to dictate multiple sets of parameters while not prompting for unused parameters.

1. Inside parameters, set the Number Of Parameter Pages to 2

2. Create a Combobox parameter called "FilterBy" with the following properties:

**Note: Text in Orange use your own names/values**

• • Type: Combobox
  • Datatype: None
  • Name: FilterBy
  • Type: Combobox
  • Values List: EmployeeID, OrderDate, City
    • These values will be used for each variable filter Name (Must match exactly)
      
      
      
      
  • Variable Filter: True

3. Add A Variable Filter for each Value added to the FilterBy Values List (Names must match exactly)

**Note: Text in Orange use your own names/values**

The statement section of the Variable Filters will use whatever your filter will be based on this field. If you need help building a filter statement use the FSGS tab.

Variable Filter 1: EmployeeID

Name: EmployeeID

Statement: dbo.Orders.EmployeeID IN ?EmployeeID?

Variable Filter 2: OrderDate

Name: OrderDate

Statement: dbo.Orders.OrderDate >= ?OrderDate?

Variable Filter 3: City

Name: OrderDate

Statement: dbo.Customers.City IN ?City?

4. Create a Parameter for the first value you added. This needs to match the Variable Filter as well as the Value added to the FilterBy Value List (EmployeeID Value > EmployeeID Variable Filter > EmployeeID Parameter)

**Note: Text in Orange use your own names/values**

Parameter 1: EmployeeID

• • Dynamic: True
  • Dynamic Query: SELECT DISTINCT Orders.EmployeeID FROM Orders
  • Parameter: FilterBy
    • The "Parameter" property is set to the Parameter "FilterBy".  This means that this parameter will be enabled based on the selected value of the "FilterBy" parameter.
  • Parameter Value: dbo.Orders.EmployeeID IN ?EmployeeID?
    • This means that if the Parameter "FilterBy" selected value for "EmployeeID" is dbo.Orders.EmployeeID IN ?EmployeeID?, this parameter will be enabled, otherwise it will not be enabled.
  • Type: IN
  • Datatype: Integer
  • Prompt Page: 2. 
    • The page will need to be at least 1 page AFTER it's driving parameter(FilterBy) is prompted.
      
      

5. Create a Parameter for the second value you added to the FilterBy Values List.

**Note: Text in Orange use your own names/values**

Parameter 2: OrderDate

• • Parameter: FilterBy
    • The "Parameter" property is set to the Parameter "FilterBy".  This means that this parameter will be enabled based on the selected value of the "FilterBy" parameter.
  • Parameter Value: dbo.Orders.OrderDate >= ?OrderDate?
    • This means that if the Parameter "FilterBy" selected value for "OrderDate" is dbo.Orders.OrderDate >= ?OrderDate?, this parameter will be enabled, otherwise it will not be enabled.
  • Type: Date Time Picker
  • Datatype: Integer
  • Prompt Page: 2. 
    • The page will need to be at least 1 page AFTER it's driving parameter(FilterBy) is prompted.
      
      

6. Create a Parameter for the third value you added to the FilterBy Values List.

**Note: Text in Orange use your own names/values**

Parameter 3: City

• Dynamic: True
• Dynamic Query: SELECT DISTINCT Customers.City FROM Customers
• Parameter: FilterBy
  • The "Parameter" property is set to the Parameter "FilterBy".  This means that this parameter will be enabled based on the selected value of the "FilterBy" parameter.
• Parameter Value: dbo.Customers.City IN ?City?
  • This means that if the Parameter "FilterBy" selected value for "City" is dbo.Customers.City IN ?City?, this parameter will be enabled, otherwise it will not be enabled.
• Type: IN
• Datatype: String
• Prompt Page: 2. 
  • The page will need to be at least 1 page AFTER it's driving parameter(FilterBy) is prompted.
    
    

7. Enable "Advanced Filtering" inside of the FSGS tab.

8. Add the ?FilterBy? parameter to the Filter Statement.

The view's filter statement is now created Dynamically using both Parameters and Variable Filters.