April 17, 2013 at 2:35 am
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. 🙂
April 17, 2013 at 3:53 am
Attached screenshot.
April 17, 2013 at 8:23 am
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
or simply do a search here or out on the web for "SSRS Multi value parameter". Good luck.
April 17, 2013 at 9:14 am
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply