Conditional Parameters

Hi all
I cant get my head round conditional parameters. I have a report with two
parameters and I would like the user to be able to select either one or both
of the parameters.
IE.
Param 1 Firstname
Param 2 Lastname
The user should be able to enter into the first parameter, or the second
parameter or both. How do I acheive this and construct the SQL accordingly.
Thanks
Dave.David,
I used similar code in one of my reports:
where FirstName like (case when IsNull(@.FName, '') = '' then '%' else '%' +
@.FName end)
and LastName like (case when IsNull(@.LName, '') = '' then '%' else '%' +
@.LName end)
HTH,
Andrei.
"David Hines" <DavidHines@.discussions.microsoft.com> wrote in message
news:249315AC-DE87-4AF6-8013-025A9CA4EA81@.microsoft.com...
> Hi all
> I cant get my head round conditional parameters. I have a report with two
> parameters and I would like the user to be able to select either one or
both
> of the parameters.
> IE.
> Param 1 Firstname
> Param 2 Lastname
> The user should be able to enter into the first parameter, or the second
> parameter or both. How do I acheive this and construct the SQL
accordingly.
> Thanks
> Dave.|||Hi Andrei
You pointed me in just the right direction Thankyou.
Finished SQL was
WHERE (FirstName LIKE '%' + (CASE WHEN IsNull(@.Fname, '') = '' THEN '%'
ELSE @.Fname END) + '%') AND (LastName LIKE '%' + (CASE WHEN IsNull(@.LName,
'') = '' THEN '%' ELSE + @.Lname END) + '%') AND (LEN(@.Lname) +
LEN(@.OwnerCode) > 0)
Again Many Thanks for your speedy response.
Dave.
"andrei" wrote:
> David,
> I used similar code in one of my reports:
> where FirstName like (case when IsNull(@.FName, '') = '' then '%' else '%' +
> @.FName end)
> and LastName like (case when IsNull(@.LName, '') = '' then '%' else '%' +
> @.LName end)
> HTH,
> Andrei.
>
> "David Hines" <DavidHines@.discussions.microsoft.com> wrote in message
> news:249315AC-DE87-4AF6-8013-025A9CA4EA81@.microsoft.com...
> > Hi all
> > I cant get my head round conditional parameters. I have a report with two
> > parameters and I would like the user to be able to select either one or
> both
> > of the parameters.
> >
> > IE.
> >
> > Param 1 Firstname
> > Param 2 Lastname
> >
> > The user should be able to enter into the first parameter, or the second
> > parameter or both. How do I acheive this and construct the SQL
> accordingly.
> >
> > Thanks
> >
> > Dave.
>
>|||Another solution - I just don't like using LIKE...
--
WHERE (FirstName = @.Fname OR @.Fname IS NULL)
AND (LastName = @.Lname OR @.Lname IS NULL)
AND ...
--
This works very well when you have "<ALL>" as an option for a parameter.
Fred
"David Hines" wrote:
> Hi Andrei
> You pointed me in just the right direction Thankyou.
> Finished SQL was
> WHERE (FirstName LIKE '%' + (CASE WHEN IsNull(@.Fname, '') = '' THEN '%'
> ELSE @.Fname END) + '%') AND (LastName LIKE '%' + (CASE WHEN IsNull(@.LName,
> '') = '' THEN '%' ELSE + @.Lname END) + '%') AND (LEN(@.Lname) +
> LEN(@.OwnerCode) > 0)
> Again Many Thanks for your speedy response.
> Dave.
>
> "andrei" wrote:
> > David,
> >
> > I used similar code in one of my reports:
> >
> > where FirstName like (case when IsNull(@.FName, '') = '' then '%' else '%' +
> > @.FName end)
> > and LastName like (case when IsNull(@.LName, '') = '' then '%' else '%' +
> > @.LName end)
> >
> > HTH,
> > Andrei.
> >
> >
> > "David Hines" <DavidHines@.discussions.microsoft.com> wrote in message
> > news:249315AC-DE87-4AF6-8013-025A9CA4EA81@.microsoft.com...
> > > Hi all
> > > I cant get my head round conditional parameters. I have a report with two
> > > parameters and I would like the user to be able to select either one or
> > both
> > > of the parameters.
> > >
> > > IE.
> > >
> > > Param 1 Firstname
> > > Param 2 Lastname
> > >
> > > The user should be able to enter into the first parameter, or the second
> > > parameter or both. How do I acheive this and construct the SQL
> > accordingly.
> > >
> > > Thanks
> > >
> > > Dave.
> >
> >
> >