Dynamically Enabled Parameters

Matthew Fifield -

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.

The setup below is a basic scenario where the parameter could be used, for example, inside of a Finished Report or Detail level filter.  The filter statement sent to the database would not impacted.

  • First we created a Combobox parameter called "FilterBy" with the values: Payment Code, Pay Date, and Collector.

  • Next we created a Parameter for the Payment Code.
    • The "Datatype" property is set to integer
    • The "Type" property is set to "Value"
    • 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.
    • The "Parameter Value" property is set to one of the "FilterBy" values, "Payment Code".  This means that if the Parameter "FilterBy" selected value equals "Payment Code", this parameter will be enabled, otherwise it will not be enabled.
    • The "Prompt Page" property is set to 2.  The page will need to be at least 1 page AFTER it's driving parameter is prompted.

  • Next we created a Parameter for the PayDate.
    • The "Datatype" property is set to DateTime
    • The "Type" property is set to "Date Time Picker"
    • 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.
    • The "Parameter Value" property is set to one of the "FilterBy" values, "Pay Date".  This means that if the Parameter "FilterBy" selected value equals "Pay Date", this parameter will be enabled, otherwise it will not be enabled.
    • The "Prompt Page" property is set to 2.  The page will need to be at least 1 page AFTER it's driving parameter is prompted.

  • Next we created a Parameter for the Collector.
    • The "Datatype" property is set to String
    • The "Type" property is set to "IN"
    • 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.
    • The "Parameter Value" property is set to one of the "FilterBy" values, "Collector".  This means that if the Parameter "FilterBy" selected value equals "Collector", this parameter will be enabled, otherwise it will not be enabled.
    • The "Prompt Page" property is set to 2.  The page will need to be at least 1 page AFTER it's driving parameter is prompted.
    • The "Dynamic" property is set to true so that we can get a list of Collectors.
    • The "Dynamic Query" property is set to "SELECT DISTINCT SQLUser_Payment_History.COLLECTOR FROM SQLUser_Payment_History" to give us our list.

 

  • Example of the parameter prompts with "Payment Code" selected

 

  • Example of the parameter prompts with "Pay Date" selected

 

  • Example of the parameter prompts with "Collector" selected

 

The setup below is a more complex scenario where the parameters will impact the filter statement sent to the database using Variable Filters.

  • Using the Parameters from the previous example, we'll need to add three Variable Filters, Payment Code, Pay Date, and Collector.
  • For the "Payment Code" Variable filter set the statement to (SQLUser.Payment_History.PAYMENT_CODE = ?PaymentCode?)

 

  • For the "Pay Date" Variable filter set the statement to (SQLUser.Payment_History.PAY_DATE = ?PayDate?)

 

  • For the "Collector" Variable filter set the statement to (SQLUser.Payment_History.COLLECTOR IN ?Collector?)

 

  • Now we need to enable property "Variable Filter" on the parameter "FilterBy".  This means that the "FilterBy" parameter will return the corresponding Filter Variable's "statement".  For example if "Payment Code" is selected, the value returned would be (SQLUser.Payment_History.PAYMENT_CODE = ?PaymentCode?)

 

  • Next we need to go back to our three parameters PaymentCode, PayDate, and Collector to adjust the "Parameter Value" equal to the corresponding variable filter statement.  This is because now that "FilterBy" has the property "Variable Filter" enabled, the value is no longer going to be what is selected, it's going to be the corresponding Variable Filter's "statement" property.

    • PaymentCode Parameter

    • PayDate Parameter

    • Collector Parameter

 

  • Next we'll need to enable "Advanced Filtering" inside of the FSGS tab

 

  • Once "Advanced Filtering is enabled, we'll add the "FilterBy" parameter to the statement

 

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

  • Example Parameter prompts and SQL statement using the Payment Code selection

 

  • Example Parameter prompts and SQL statement using the Pay Date selection

 

  • Example Parameter prompts and SQL statement using the Collector selection

 

 

 

Have more questions? Submit a request

Comments

Powered by Zendesk