Post reply

Parameters

  • July 8, 2015 at 2:25 pm

    #309792

    I am trying to come up with report where in I can pass 3 Optional parameters.

    Either Parameter1 or

    Parameter1 and Parameter2 or

    Parameter1 , Parameter2 and Parameter3 and get data in the same report.

    Even though I add Allow null under parameters , The report still asks me to enter the other parameter .

    In the report Query I am passing the parameters through a where clause .

    Kindly advise as I don't want to end up creating three seperate reports.

  • July 8, 2015 at 3:00 pm

    #1811139

    That seems to be because the NULL checkbox isn't selected. You could allow blanks to prevent this problem.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • July 8, 2015 at 3:27 pm

    #1811143

    Looks like something in the where clause that needs to be tweaked.This is what I am using ....

    WHERE

    ((parameter1 = @parameter1) OR

    (parameter1 = @parameter1 AND parameter2 = @parameter2 )OR

    (parameter1 = @parameter1 AND parameter2 = @parameter2 AND parameter3 = @parameter3) )

  • July 8, 2015 at 3:35 pm

    #1811147

    Maybe you could use dynamic sql for better results as shown in this article: http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

    Or you could simply go like this:

    WHERE parameter1 = @parameter1

    AND (parameter2 = @parameter2 OR ISNULL(@parameter2,'') = '')

    AND (parameter3 = @parameter3 OR ISNULL(@parameter3,'') = '')

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • July 9, 2015 at 9:22 am

    #1811354

    That helped.

    Thanks Luis

  • July 9, 2015 at 9:47 am

    #1811361

    Hi Luis do you mind explaining what exactly this part does ? Will it treat the nulls as blank?

    ISNULL(@parameter2,'') = ''

  • July 9, 2015 at 10:05 am

    #1811372

    That's exactly what it will do to prevent sending blanks when you intend sending nulls. You can change that if you're certain that you'll only receive nulls to compare as @parameter2 IS NULL.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • July 9, 2015 at 10:56 am

    #1811389

    Oh Ok. Thanks again

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply