Filter data in report by using dropbox

Question

Filter data in report by using dropbox

fasttrack2

SSC-Addicted

Points: 477
More actions
January 17, 2011 at 3:52 am

#245370

Hello guys,
I am new in SSRS 2005 and I need some help from you.
I am developing my first reports and my issue is the following.
After having executed the report I should be able to filter, for instance by using a drop box. in a few words, selecting the parameter, the data is shown in the report depending on the parameter selected in the dropbox.
for example, the dataset produce the report:
col1, col2, col3, colfilter
Just as example, the column filter allow only 2 values; a or b for each row.
So, I from the dropdown box I select "a":
col1, col2, col3, colfilter
valCol_11a, valcol_12a, valcol_13a, a
valCol_21a, valcol_22a, valcol_23a, a
valCol_n1a, valcol_n2a, valcol_n3a, a
Then, from the drop box I select "b", and I get the following data selection:
col1, col2, col3, colfilter
valCol_11b, valcol_12b, valcol_13b, b
valCol_21b, valcol_22b, valcol_23b, b
valCol_n1b, valcol_n2b, valcol_n3b, b
and so on.....
I know that building a query in the dataset with @paramter as a filter I have the dropbox; but this drop box is used before running the report just to set the parameters.
What I need, it is a filter tool like a dropbox to be used when the report is already shown.
Otherwise if I have lots of parametr I need to launch again the report and select all the dropbox available to run the report.
Could you help me
Many thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steven.Howes SSCertifiable Points: 6210 More actions · Answer 1

If I get what you're asking then what you need is a dataset for each drop down box

In a new dataset get all your parameter values into two columns, Label and Value. Then on your parameter set the Available values to "get value from a Query" use the data set you just made and set your label and Values fields.

Then on the Parameter tab of your main dataset set "parameter Value" the parameter you just set up.

Hope this makes sense, It's very early...

fasttrack2 SSC-Addicted Points: 477 More actions · Answer 2

Hi, and thanks for your reply.

I need to apply filtering directly at report level and not at dataset level.

I think the way to proceed if different and I need some help from you.

Many thanks.

Steven.Howes SSCertifiable Points: 6210 More actions · Answer 3

My filtering is done at run time... I'll use an example of a report I've made.

I have a report that will tell me who has accessed reports. It has One parameter, called pFolder. pFolder has it's own dataset that returns all the folders in a branch of my report server catalog. The script would look something like:

Select [Name] as Label, [Path] as Value

from reportserver.catalog

where [Path] like '\ApplicationName\Accounting\' --Not the real script

The Parameter tab available values would have Folder as the dataset, and value = Value and label = Label.

Now when I hit the drop down I get the results of the script in the drop down box.

Next on the main dataset thet displays data in the report table I set the parameter Name @folder to equal the report Value @pFolder. @Folder would be a filter i the where clause of the Main dataset report script

Hopefully a little clearer. if I can find a spot to post images I'll reply with some screenshots.

Steven.Howes SSCertifiable Points: 6210 More actions · Answer 4

I found a place...

Paramter Properties: you can see how the available values are set.

ParameterDropDown: what the drop down would look like

how to make the

Dataset Properties: Here you can see how the main dataset would passthe selection made in your drop down to the main dataset