Check All Available Combinations of Parameter in DataSet

I have 4 parameters for a report. Is there a quick way to tell the dataset to check for all available combinations of the 4 parameters?

Who is Participating?
DcpKingConnect With a Mentor Commented:
If you're using SSRS, for example, use a stored procedure instead of just raw SQL for the dataset of the report. i.e. instead of using "select * from table" put that code into a procedure, check the option to use a procedure, and give the SSRS dialog the name of your procedure.

Next step is passing the parameters you want to the procedure. Firstly, alter the procedure so that it wants the four parameters:
create procedure myProc
    @intVar1 int,
    @intVar2 int,
    @intVar3 int,
    @intVar4 int
   select {fields}
   from {table}
   where field1 = @intVar1
       and field2 = @intVar2
       and field3 = @intVar3
       and field4 = @intVar4

Open in new window

Now use this procedure as your dataset and you'll see 4 entries in the Parameters section of the Dataset Properties dialog.


LCNWAuthor Commented:
So this will handle the filtering of all possible selected combinations of the 4 parameters?
Not as such ... I was just offering a template. The stored procedure will be rather more complicated and you'll have to build in parameter to the report. Do you know how to do these things, ?

This is a YouTube intro to adding parameters to a report. As for the code in the query, you'll need to be somewhat more specific.

What exactly do you mean by "all possible combinations"? If your parameters are a, b, c, & d then is it a simple combination (return values where field_a = a and field_b = b and field_c = c and field_d = d)  ?    Should there be "or" possibilities included in some way?    Should one take into account the possibility of the user not knowing a value for one or more of the parameters ?

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

LCNWAuthor Commented:
I'm good with building parameters and SPs. I built it up and am testing.  Each of my parameters has a set of values from a query, plus I added a <Select All> value via UNION.

FROM         Table
SELECT     '<Select All>' AS Expr1
FROM         Table AS Field1_DD

Then I choose '<Select All>' as the default value for each parameter which returns a null and loads all records.
Brian CroweConnect With a Mentor Database AdministratorCommented:
If you want to handle NULL parameter values a better way within a stored procedure is:

WHERE ISNULL(@Field1Param, Field1) = Field1
   AND ISNULL(@Field2Param, Field2) = Field2
   AND ISNULL(@Field3Param, Field3) = Field3
   AND ISNULL(@Field4Param, Field4) = Field4
LCNWAuthor Commented:

I've modified my SP to include the where clause and implemented it into my report, but upon preview I get the following:

Error converting data type nvarchar to int

I switched the parameter definitions to nvarchar and that seemed to fix the datatype issue. However, my the parameters do not seem to be applying to my report as it returns nothing.
LCNWAuthor Commented:

I tried the ISNULL technique but my report is still pulling in no results.
LCNWAuthor Commented:
I can now get data to load into the report (appears it was a datatype issue), but this has killed my <Select All> trick. My users like to have a <Select All> option in the drop down list.
LCNWAuthor Commented:
I think I have it all worked out. I'll report back Monday. Thank you all.
(Sorry I vanished - I had to feed the wife :)  Hope all is going well!)

PortletPaulConnect With a Mentor freelancerCommented:

so far all of the suggestions I see are based on AND's

That won't cover "all possible combinations"

e.g. in the following, if all parameters are present

WHERE ISNULL(@Field1Param, Field1) = Field1 -- a.
   AND ISNULL(@Field2Param, Field2) = Field2  -- b.
   AND ISNULL(@Field3Param, Field3) = Field3  -- c.
   AND ISNULL(@Field4Param, Field4) = Field4  -- d.

Then this would ignore matches on (a. and b. and c.) if d. is false

alternative syntax by the way is:

WHERE ( Field1 = @Field1Param or @Field1Param IS NULL ) -- a.
   AND  ( Field2 = @Field2Param or @Field2Param IS NULL ) -- b.
   AND  ( Field3 = @Field3Param or @Field3Param IS NULL ) -- c.
   AND  ( Field4 = @Field4Param or @Field4Param IS NULL ) -- d.

(it should be noted that using only AND's is probably the most common approach)
So the question is, what do you mean by "all available combinations"?
LCNWAuthor Commented:
All combinations of the four parameters as either a value or null. As referencing combinations and permutations in mathematics.

I came up with 16 using math.
LCNWAuthor Commented:
You are correct about the the and/or PortletPaul.
LCNWAuthor Commented:
I've got it all working. I used a combination of your answers. Thank you.
Great work. Cheers, Paul
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.