SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Parameters Users Utilization


Parameters Users Utilization

Author
Message
davdam8
davdam8
SSC Veteran
SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)

Group: General Forum Members
Points: 296 Visits: 668
Hi geniuses!

I have a report with 3 parameters.
PARAM1
PARAM2 (multivalue and depending on PARAM1 selection)
PARAM3 (multivalue and depending on PARAM2 selection)

Right now the user is forced to use them all.
I want the user to be free to choose if he only want sto use only 1, 2 or all of them.

Suggestions?

Thanks
Regards!
davdam8
davdam8
SSC Veteran
SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)

Group: General Forum Members
Points: 296 Visits: 668
Basically:
How to pass NULL value to a Multi value parameter in SSRS?
Thanks

Regards!
H4K
H4K
SSC Veteran
SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)

Group: General Forum Members
Points: 241 Visits: 567
You can set the parameter properties to 'Allow NULL' and also handle it accordingly on the query side. For example:

Where (customer = @Customer or @Customer is NULL)
OR
Where (customer IN (@Customer) or @Customer is NULL)

BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI
sanjeewan99
sanjeewan99
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 399
Hello,
Go to this link will Help you to pass multiple or All parameter value
http://www.bi-rootdata.com/2012/09/efficient-way-of-using-all-as-parameter.html
davdam8
davdam8
SSC Veteran
SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)SSC Veteran (296 reputation)

Group: General Forum Members
Points: 296 Visits: 668
H4K, your solution does not work because SSRS 2008 will not allow to have multivalue parameter and allow null value checked.

What I got is this:

CapPARAM Parameter Query:
SELECT DISTINCT ISNULL(cap.title, '') AS Captitle, ISNULL(cap.title, 'NULL') AS CaptitleDesc, cha.title AS Expr1
FROM ABC
WHERE (chap.title = @ChapPARAM)



CapPARAM Parameter Properties:
Allow Multivalue
Available Values: Value field: Captitle , Label field: CaptitleDesc.

Dataset Query
SELECT DISTINCT chap.title, cap.title AS Expr1, cap.ed_pecent
FROM ABC
WHERE (chap.title = @ChapPARAM) AND (ISNULL(cap.title, '') IN (@CapPARAM))
ORDER BY chap.title



What I need is to enable the report user to not interact imperatively in all of the parameters, but he does have to select values from the first one.

Need solutions here, Thanks
Best Regards!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search