3 Replies Latest reply on Sep 20, 2017 3:50 PM by Sirish Bhatta

    Dynamic Parameter Ranges as an Input into CustomSQL

    Trevor Safko

      We have sql tables of varying lengths (the largest of which is > 100,000 rows). The majority of our tables are small (< 1000 rows) and many of them are very small mapping tables. For our analysis, we require a dataset which includes a series of full-outer joins across these tables. However, the sheer number of combinations makes this infeasible. Instead, we are using a custom sql query to filter down the dataset. We want this custom sql query to run off of user defined parameters. However, the range of values for our parameters are too large for a simple drop down selection. Can we create a series of parameters whose ranges depend on the previous parameter selected (similar to the "Only Relevant Values" option for a series of filters)? These parameters would then be the input into the sql query. In this way, we do not need to display all possible parameter options. Alternatively, is it possible to determine the range of a parameter based on a filter selection?

       

      Example:

       

      Country (full outer join) State (full outer join) County (full outer join) Zip Code

       

      In the above example, we want the available range of 'State' parameters determined by the 'Country' parameter. 'County' would be determined by 'State' selected, etc.

       

      Once determined, the selected parameters would be called in the sql query to return a limited subset of the total data. We do not want to simply (full outer) join all 4 tables as our actual data seems too large to create an extract.

       

      If you have any solutions or suggestions please let me know.

       

      Thanks!