Parameters...HELP!

I am using 6 parameters in a report. If I set the Where clauses to OR,
the report will return everything (understood) but it doesnt work when
I set it to AND (how it should be). By themselves, each parameter
works. But when you combine all of them or even 2, nothing returns. Am
I missing a simple step?
Also, I wanted some parameters to be "optional"-dont have to have a
value in them (is this possible with report designer?). I've tried
setting the "optional" ones to null.
I even tried to use SQL profiler to try and view the sql being executed
but to no avail.
Any suggestions would be greatly appreciated!
Thanks!gte401:
Does the query work correct in Query Designer? Typically, I like to design
my SQL code there and then paste it into the report query after it's been
debugged. If it's working correctly then you should get the same results.
As for six parameters, I have a few with up to four with no problems.
Regarding your question about leaving parameters NULL. If you do, then
you'll definitely want to make sure you've accommodated the NULL in your
WHERE statement. You could use an ISNULL function to convert it to '%' and
do a LIKE. For example:
WHERE myField LIKE '%' + RTRIM(LTRIM(ISNULL(@.TEST,''))) + '%'
Hope this helps! I'm sure someone may have an easier way to do this, but
for now it works for me.
Patrick|||Open the report up in Visual Studio.
Go to Report => parameters.
You can specify if the value of a parameter may be NULL/Empty.
hope this helps
Regards,
Stas K.|||When you select the Null Function for say Last Name, does the query
skips this parameter or does it look for Last Names with Null?
Thanks
Kevin|||Patrick,
My SQL runs. Its just when I add the Where clause, it does not work.
This is what Business Intelligence Studio generated:
WHERE
(Person.Status LIKE @.Status) AND (PersonType.PersonType_Desc =PersonType.PersonType_Desc) AND (Methodology.Methodology_Desc IN
(@.Methodology_Desc)) AND (Certification.Certification_Desc IN
(@.Certification_Desc)) AND (Person.Last_Name = @.Last_Name) AND
(Skill.Skill_Desc IN (@.Skill_Desc)) AND (Person.First_Name =@.First_Name)
When you run it, it will prompt "Parameters for Query" I set everything
to null.
I then go to the preview tab. Enter the desired values in my search
page, and nothing appears.|||This also works:
and myfield = ISNULL(@.myparameter, myfield)
... if @.myparameter isnull then statement is myfield = myfield, which is
always true
"gte401e" wrote:
> I am using 6 parameters in a report. If I set the Where clauses to OR,
> the report will return everything (understood) but it doesnt work when
> I set it to AND (how it should be). By themselves, each parameter
> works. But when you combine all of them or even 2, nothing returns. Am
> I missing a simple step?
> Also, I wanted some parameters to be "optional"-dont have to have a
> value in them (is this possible with report designer?). I've tried
> setting the "optional" ones to null.
> I even tried to use SQL profiler to try and view the sql being executed
> but to no avail.
> Any suggestions would be greatly appreciated!
> Thanks!
>|||Dterrie,
Thanks for the help!
But one more question....I have several multi value parameters
=IN(@.Example) . I know you cant set them to Null. Is there a way to
make them optional though? Or can I incorporate =IN(@.Example) into the
statement you gave me? If so, how?
Thanks in advance!
Kevin