SQLTeam.com | Weblogs | Forums

Datasets/multiple tables with parameters as filters

#1

Hello everyone!

So I have 4 datasets and 4 parameters. All of the parameters can be used in the 1st Dataset called wo and are able to be toggled on and off.. So you can use 1,3,4 parameter, 1 parameter or all 4 parameter. So I got the 1st dataset to work.

Now the second dataset has labor data. What I want and need to happen is when those parameters are set they pull a field that is called workorder-numbers. So any workorder-numbers in the 1st dataset should pull all the workorder-numbers from the second dataset based off the parameters filtered from wo dataset.

Dataset1
Select
workorder-number, statuscall, divcode, tech, salesman
from wo
where (@Statuscall IS NULL OR
[Status-Call] = @Statuscall) AND (@Divcode IS NULL OR
[div-code] = @Divcode) AND (@Salesman IS NULL OR
[Slspn-code1] = @Salesman) AND (@Tech IS NULL OR
[Slspn-code] = @Tech)

Dataset2
Select workorder-number, laborhr, labor-rate, tech
from wolabor
WHERE ??????????

#2

How about get the PKeys (or all the data columns you need perhaps**) into a #TEMP table at your "Dataset1" stage and then use that for display/output (if required) and then also use it to JOIN to other tables at the "Dataset2" stage.

You could use dynamic SQL for Dataset1 stage (to avoid having OR tests for "@MyParameter IS NULL") by only including tests in the (dynamic) WHERE clause that are actually in use. Pros and Cons of both routes of course, but we tend to use dynamic SQL to gather PKeys into a #TEMP table [when there are multiple, optional, filter criteria @parameters] and then JOIN that #TEMP table in on all the other steps (i.e. where there are multiple steps to be taken, after the first Dataset is acquired)

** If we need multiple, additional, JOINed tables for display we tend to get PKeys only into #TEMP. If the Filter happens to include all the JOIN Tables that are required in the Display phase we tend to store all the columns, required in the display phase, into the #TEMP table so we only make one pass. But "it depends" :slight_smile: and only as a result of performance testing do we actually decide which method performs best, although "time cost of development" is also a consideration, particularly if the report is seldom used and/or runs very quickly anyway, in which case we will choose the route that has least development cost; I don't need to spend a day optimising a report that is run once a month on a table containing 100 rows :slightly_smiling: