February 2, 2012 at 1:08 am
Dear All,
I am an extreme fresh and in-experienced SSRS's user but now having a chance to work on report development for an organisation. I am now encountering a big issue when setting up a parameter. The situation is I want to create 3 options as a parameter in the report from a list of stuff. For example, there is a field called "Fruit" under the table called "Food" and within there are 5 different fruits: apple(a), orange(o), lemon(l), pear(p) and banana(b).
If I just want to create 3 options for a parameter from this field:
1) All fruits (a, o, l, p, b)
2) Only apple (a)
3) All fruits except apple (<>a)
How can I do it?
I tried to setup a non-queried parameter but it only works for apple. (Please see pic)
Actually can I write ONE sql query to list out this 3 options, since I just know simple query and have no idea on how to do complex query or double "Select".
I appreciate if any SSRS's expert could provide me the solution.
And if I want to develop myself into this area (SSRS), how and where should I start?
Thanks and regards,
Bruce
February 3, 2012 at 6:10 am
It would be better for you to use the multi-value parameter option. Then the report will present a check box list from which to select individual items (in your case fruits). The multi-value parameter also has a check box at the top of the list which will select all the parameters. Next, you will need to filter either in your query, dataset, or object based on the parameter selected.... using the "in" option for the filter. If you send the parameter to a stored procedure, you will need to parse it out because SSRS sends the parameter as a single comma delimited list.
February 5, 2012 at 6:32 pm
Hi Journeyman,
Even though I am not so sure all the jargons that you have mentioned, really appreciate your help and I will try to figure it out.
Thanks a lot.
Regards,
Bruce
February 10, 2012 at 7:42 am
Agreed. Set up a new dataset (query) that only produces the fruit selections, then set up a QUERIED parameter in your original dataset that references your new fruit parameter. Select multi-value. If set up correctly, you can chose whatever combination of fruits you like to see data for! Woooo woooo!
If you need more help, send more pics and I (we) can walk you thru it!
Mike
February 11, 2012 at 1:35 am
Hi Mike,
Thank you for your suggestion, I got a friend suggested me to use conditional statement and I found it works properly.
I firstly create a non-queried parameters called FruitQuery with the information below:
Lable: Value:
ALL A
Apple AP
Non-apple NAP
IF @FruitQuery = 'A'
BEGIN
[SQL query]
END
ELSE IF @FruitQuery = 'AP'
BEGIN
[SQL query]
END
ELSE IF @FruitQuery = 'NAP'
BEGIN
[SQL query]
END
Do you think this can be used in this situation as well?
Thanks,
Bruce
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply