April 13, 2012 at 3:11 pm
I have requirements where I need to create optional parameters. When users run the report they can choose the parameter or they can leave it blank. I am new to SSRS any help would really appreciate it.
Main SQL:
Where
AgreementGroups.AgreementGroupName =@AgreementGroupName Or @AgreementGroupName is not null
Parameter SQL:
SELECT distinct AgreementGroupName
FROM AgreementGroups
WHERE AgreementGroupName IS NOT NULL
UNION
SELECT '(NULL)'
ORDER BY AgreementGroupName
April 16, 2012 at 3:29 am
It looks like you're most of the way there already. That query is going to return you a datset with all your AgreementGroups plus an empty record. In SSRS, create a parm, set it to Allow Null Value on the General Tab and set it to Get values from a query on the Available Values tab and select your dataset. Your user will now be presented with a dropdown list of all your Agreement Groups plus one blank line.
Be aware that, if they pick the blank line, the value they are actually picking is NULL. So you want to cater for that in your main report query.
April 16, 2012 at 8:38 am
Thanks for your reply, the multiple values parameter" is not working with NULL values. i have to give both options to the end users, they can pick multple vlaues or they can choose null value from the paramters and based on those parameters the report generate the data. Thanks.
April 16, 2012 at 9:10 am
Maybe I'm missunderstand the problem but I dn't think you need to do anything at all to support that. You just make it a multi valued parameter. The user will be present with a series of checkboxes and, if they don't want to pick anything, they don't pick anything.
What's the business background to this? It might help me understand the problem a bit better.
April 16, 2012 at 12:05 pm
Find the below link that shows the error msg while I am trying to create the optional parameter. The business requirements pretty straight forward. I have to create the optional parameter on Agreement Group name, so user can pick the values or they can skip it if they want to. Again thanks for taking your out.
April 16, 2012 at 1:27 pm
Yeah, I'm aware of the error. I'm just struggling to understand why you'd need a null if you've got a multi value parm. Null normally means nothing or everything, both of which the multi value parm can cater for without the need for a null option.
Assuming the user doesn't pick anything in the list, what would you expect them to see? If they don't want to see groups, what are you going to show them?
April 16, 2012 at 1:38 pm
Well we have business objects reports already in production, now clients asking for same reports in SSRS, In business objects we have optional prompts feature. So now in SSRS if the user doesn’t pick anything in the list, they should see everything in the report.
April 16, 2012 at 1:57 pm
Isn't that the same as the user picking everything in the list? If so the multi value parameter has an 'All' option as standard which makes picking everything nice and quick (they select 'A;;' and it selects all the options for them. You don't need to do anything to get it, it will be there automatically.
If it's not the same as the user selecting everything then what's the difference?
April 16, 2012 at 2:35 pm
I totally agree with you whatever you are saying, however the SSRS data are matching with the Business objects report when I pick something from Agreement Group parameter it is matching but when I leave it blank does not give me the correct data in the report.
April 16, 2012 at 3:53 pm
Oooh, I think I may have spotted your issue. Your query to populate the parameter has this Where clause:-
WHERE AgreementGroupName IS NOT NULL
Does that mean you've got some records where the AgreementGroupName is null? If so you're going to have a bit of an issue because you won't be able to query for them based on a multi value parameter. SSRS passes the selection from a multi value parm into the query as a comma separated list. TSQL then uses this as an "In" list. Since nothing is equal to Null, including Null itself, getting a null value into the list won't result in the records wih a null AgreementGroupName being returned.
Does that sound like it's the problem? If so I've got a couple of ideas. They're not pretty and involve pushing dummy values into your query but they'll work.
If that's the problem then I really can't see any reason why a multi value parm won't work for you. Sorry.
April 26, 2012 at 1:50 pm
I am sorry about the confusion, well in Business objects reports if users don’t pick any values from Agreement Group Name parameter, it won’t join to the fact table. So it will have more records in the report, where in SSRS the query is going to join the AgreementGroup table from the fact table, it does not matter if it is multiple values or single value parameter. I hope you understand my problem now. Is there any way I can achieve this in SSRS. Thanks for your help in advance.
April 30, 2012 at 1:21 am
I'm sorry but I still don't understand the problem. Can you provide some example of what the user would pick and what you'd expect the result to be?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply