Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Drop down list/filter not working


Drop down list/filter not working

Author
Message
addyb77
addyb77
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 23
Hi all, first post!

Ok i dont know if im just missing something obvious but i cant for the life of me get this to work. I basically have a report with a big query behind it. I have been trying to edit it in report builder so i can have drop down menus along the top so users can filter via manager name.

I have set a parameter up so it references the dataset and the particular column and i do indeed end up with a drop down list where i can select all or individual manager names.

The issue i have is no matter what i select once i refresh the report based on my selections it still returns all the data. Im not sure why its doing this and to be honest im a reporting services/report builder virgin so im learning this as i go along.

Things ive done/tried so far.

In the parameter settings ive pointed the available and default values to look at the dataset and column (for reference dataset is "Clearview" and Value field is "ProjMgr") Should the label field also be set as "ProjMgr" as thats what ive been doing?

Ive also tried creating an additional dataset with a simple query that retrieves all the data for that field.

select
s.firstname+' '+s.lastname ProjMgr

from
staff s,


and then pointing the parameter settings at this dataset instead. That still gives the same results.

If i add an additional where clause in my original query for a prompt

and s.firstname+' '+s.lastname = (@ProjMgr) i then just get a blank search field when i run the report and it does actually retrieve the correct data once i type in a manager name and refresh.

I just cannot get the report to show the appropriate results based on manager name when i select them from a drop down list. I must be missing something obvious but feel like ive tried all i can think of at present.

Sorry if i havent supplied enough detail. If anyone can help it would be much appreciated. I can then go on and add my drop down lists to the report then. Smile
addyb77
addyb77
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 23
Attached screenshot.
Attachments
Parameter drop down filter.png (29 views, 161.00 KB)
TaylorMade
TaylorMade
SSC-Addicted
SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)

Group: General Forum Members
Points: 498 Visits: 210
The most likely reason (details are lacking) you are having problems is that your parameter is a "multi-select", and in your SQL where clause, you are using "=" as the condition. But for multi-select, you should be using "IN (n,n1,....)", or another technique altogether (use filter).

The other thing that I suggest would be to use a key, not a text string to do the matching. In other words, use the value field, not the label field, i.e. use ID_NUM not lastname + ', " + firstname or something like that.

Here is a link to get you started on how to setup multi-select parameter reports:

http://www.sqlservercentral.com/articles/Reporting+Services+(SSRS)/66322/

or

http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/c02370b5-aeda-47ec-a3a8-43b2ec1e6c26/

or simply do a search here or out on the web for "SSRS Multi value parameter". Good luck.
addyb77
addyb77
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 23
Thanks for the reply. I was starting to think it was due it being text based when i was following a tutorial based on parameters. Thanks for the links i will take a look tomorrow. I will have a play and use an ID or num field that i also have in the query instead.

Watch this space Smile

Thanks again for the help.
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