Non Mandatory Queried Report Parameters

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Non Mandatory Queried Report Parameters

Author

Topic

cirajc
Starting Member

24 Posts

Posted - 2011-10-11 : 02:29:08

May i know how to make Report Parameters Non Manadatory.
I am having 3 Params, in which if i am selecting only one and excludes other two ,Then i need to avoid getting that Prompting Message Box & then to Process the Report with the only given parameter.
I m using Queried-MultiValue parameter ,non Queried also......

Im new to SSRS ,So Please Reply me Tricks or Method beyond this.....

ciraj

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 02:58:29

you need to select either of allow null value or allow blank value

also you need write queries in such a way that it ignores filter when passed value is null or blank based on above setting

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cirajc
Starting Member

24 Posts

Posted - 2011-10-11 : 03:59:57

Dear

Thanks for your Immediate response .

I was having same crieteria what u had explained.
In Four Parameters
1.MultiValued Query Parameter(Allow Blank Value ,String ).
2.SingleValued Query Parameter(Allow Null Value ,String ).
3.Non Query Parameters (Allow Null Value ,Integer ).
4.Non Query Parameters (Allow Null Value ,DateTime ).

I am passing above FOur parameters to View consist of last Row with NULL Values.

Is that What u had explained Or Something Else??

quote:
Originally posted by visakh16

you need to select either of allow null value or allow blank value

also you need write queries in such a way that it ignores filter when passed value is null or blank based on above setting

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ciraj

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 04:12:41

yep...same thing. how have you used where clause in query for filtering?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cirajc
Starting Member

24 Posts

Posted - 2011-10-11 : 04:30:24

It is JOIN of 4 Tables.
In which INNER & LEFT OUTER JOIN exists.
ON based Each Tables Key Field.
and WHERE Condition with a.AMT <> b.AMT..Like wise.

If you want me to explain exact Tables & View Script,i will reply to u.

Thanks for ur usual support

quote:
Originally posted by visakh16

yep...same thing. how have you used where clause in query for filtering?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ciraj

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 04:45:42

nope. I'm asking place where you're filtering based on above parameters

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cirajc
Starting Member

24 Posts

Posted - 2011-10-11 : 05:12:21

Filtering Based on Parameters

= Fields!User_Description.Value=Parameters!SalesMan.Value(0)
OR Fields!Payment_Code.Value = Parameters!ReceiptNumber.Value
OR Fields!Payment_Date.Value = Parameters!PaymentDate.Value
OR Fields!Printing_Times.Value >= Parameters!PrintingTimes.Value
OR Fields!Printing_Times.Value > Parameters!PrintingTimes.Value
OR Fields!Printing_Times.Value <= Parameters!PrintingTimes.Value
OR Fields!Printing_Times.Value < Parameters!PrintingTimes.Value

= TRUE

quote:
Originally posted by visakh16

nope. I'm asking place where you're filtering based on above parameters

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ciraj

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 05:39:40

here you need to apply conditional logic to bypass the filter in case of null or blank value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cirajc
Starting Member

24 Posts

Posted - 2011-10-11 : 09:52:32

i has added same Conditional Logic for Other parameters too as below
OR Fields!User_Description.Value = " "
But if we are not selecting any parameter, still Prompting
"Parameter Error:Pleaset Select a Value For the parameter"....

quote:
Originally posted by visakh16

here you need to apply conditional logic to bypass the filter in case of null or blank value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ciraj

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-11 : 13:44:58

have you set allow blank value or allow null value for all?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cirajc
Starting Member

24 Posts

Posted - 2011-10-15 : 07:36:38

Yes....Sure
I did as below,
Report parameters as below
1.MultiValued Query Parameter(Allow Blank Value ,String ).
2.SingleValued Query Parameter(Allow Null Value ,String ).
3.Non Query Parameters (Allow Null Value ,Integer ).
4.Non Query Parameters (Allow Null Value ,DateTime ).
And Filter Condition

OR Parameters!SalesMan.Value(0) = " "
OR Parameters!ReceiptNumber.Value= " "
OR Parameters!PrintingTimes.Value = " "

quote:
Originally posted by visakh16

have you set allow blank value or allow null value for all?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ciraj

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-15 : 12:37:43

still its prompting you for parameter value?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cirajc
Starting Member

24 Posts

Posted - 2011-10-17 : 08:03:07

Yes,Still Prompting.

quote:
Originally posted by visakh16

still its prompting you for parameter value?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ciraj

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 08:27:50

thats not possible. that means you missing a step somewhere.

can you list out full steps you did for each parameter?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cirajc
Starting Member

24 Posts

Posted - 2011-10-17 : 10:51:52

Report parameters as below
1.MultiValued Query Parameter(Allow Blank Value ,String, From Query ).
2.SingleValued Query Parameter(Allow Null Value ,String, From Query).
3.Non Query Parameters (Allow Null Value ,DateTime,NonQueried ).

Filter Condition
= Fields!User_Description.Value=Parameters!Salesman.Value(0) OR
Fields!Payment_Code.Value = Parameters!ReceiptNumber.Value OR
Fields!Payment_Date.Value > Parameters!PaymentDate.Value OR
Parameters!Salesman.Value(0)=" " OR
Parameters!ReceiptNumber.Value = " " OR
Parameters!PaymentDate.Value = " "
= TRUE

quote:
Originally posted by visakh16

thats not possible. that means you missing a step somewhere.

can you list out full steps you did for each parameter?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ciraj

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 11:00:45

wat about this?

Filter Condition
= Fields!User_Description.Value=IIF(Parameters!Salesman.Value <> " ",Parameters!Salesman.Value,Fields!User_Description.Value) OR
=Fields!Payment_Code.Value = IIF(Parameters!ReceiptNumber.Value <> " ",Parameters!ReceiptNumber.Value,Fields!Payment_Code.Value) OR
=Fields!Payment_Date.Value > IIF(Parameters!PaymentDate.Value <> " ",Parameters!PaymentDate.Value,Fields!Payment_Date.Value )

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cirajc
Starting Member

24 Posts

Posted - 2011-10-18 : 02:47:24

I tried as below Still not working , i can discard Only Payment_Date while Selection Because NULL Check Box is there ,Others are still mandatory......

quote:
Originally posted by visakh16

wat about this?

Filter Condition
= Fields!User_Description.Value=IIF(Parameters!Salesman.Value <> " ",Parameters!Salesman.Value,Fields!User_Description.Value) OR
=Fields!Payment_Code.Value = IIF(Parameters!ReceiptNumber.Value <> " ",Parameters!ReceiptNumber.Value,Fields!Payment_Code.Value) OR
=Fields!Payment_Date.Value > IIF(Parameters!PaymentDate.Value <> " ",Parameters!PaymentDate.Value,Fields!Payment_Date.Value )

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ciraj

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-18 : 02:59:16

but you only told right you set allow null value, allow blank value for all of them

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cirajc
Starting Member

24 Posts

Posted - 2011-10-18 : 04:01:41

Yes.
Either One.... 3 Paramters with

1.Salesman: MultiValued Query Parameter(Allow Blank ,Allow Null Value )
2. Receipt Number: SingleValued Query Parameter(Allow Null Value )
3. Printing Times: Non Query Parameters with (Allow Null Value )
4. Payment Date: Non Query Parameters with (Allow Null Value )

quote:
Originally posted by visakh16

but you only told right you set allow null value, allow blank value for all of them

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ciraj

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-18 : 04:17:44

sorry i didnt understand then how it will prompt for the value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cirajc
Starting Member

24 Posts

Posted - 2011-10-18 : 04:44:39

i am Really sorry fro my Replies,if it is not precise and understanble.
Anyway i am other Projects now ,once i recheck about this i will come to you again.

Thanks a lot for ur usefull Help......

quote:
Originally posted by visakh16

sorry i didnt understand then how it will prompt for the value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ciraj

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources