Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Drop down list/filter not working Expand / Collapse
Author
Message
Posted Wednesday, April 17, 2013 2:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 9:09 AM
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. :)

Post #1443147
Posted Wednesday, April 17, 2013 3:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 9:09 AM
Points: 3, Visits: 23
Attached screenshot.

  Post Attachments 
Parameter drop down filter.png (10 views, 161.64 KB)
Post #1443166
Posted Wednesday, April 17, 2013 8:23 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 11, 2014 9:09 AM
Points: 279, Visits: 175
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.
Post #1443295
Posted Wednesday, April 17, 2013 9:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 9:09 AM
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 :)

Thanks again for the help.

Post #1443332
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse