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?
Thanks.
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 ?
Mike
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 ?
Mike
ASKER
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.
SELECT DISTINCT Field1
FROM Table
UNION
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.
SELECT DISTINCT Field1
FROM Table
UNION
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
DcpKing,
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.
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.
ASKER
BriCrowe,
I tried the ISNULL technique but my report is still pulling in no results.
I tried the ISNULL technique but my report is still pulling in no results.
ASKER
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.
ASKER
This is the technique I follow:
http://dataqueen.unlimitedviz.com/2011/06/how-to-default-to-all-in-an-ssrs-multi-select-parameter/
http://dataqueen.unlimitedviz.com/2011/06/how-to-default-to-all-in-an-ssrs-multi-select-parameter/
ASKER
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!)
Mike
Mike
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
I came up with 16 using math.
ASKER
You are correct about the the and/or PortletPaul.
ASKER
I've got it all working. I used a combination of your answers. Thank you.
Great work. Cheers, Paul
ASKER