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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 ?

Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

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 CroweDatabase 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!)

PortletPaulEE Topic AdvisorCommented:

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.
PortletPaulEE Topic AdvisorCommented:
Great work. Cheers, Paul
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.