x

How can the dynamic filtering functionality of Access/Excel be implemented in SSRS?

We have an SSRS report with a dataset that calls a stored procedure. This stored procedure cannot be changed, and the report parameters cannot be changed.

We would like the user to be able to filter the report dynamically in the same sort of way that filtering is done when viewing an Access table or an Excel spreadsheet.

For example, to see only the data for a particular client, we would like the user to be able to click on the ClientNumber column header, see a list of possible values that are contained in the data, and select one or more values, after which the report will only display those rows with the selected ClientNumber(s).

Just to re-iterate the limitations at the beginning of this post: the stored procedure that this report calls cannot be changed, and no new report parameters can be added to the report.

This is a very basic reporting requirement, and I am sure most SSRS developers out there have come across this problem. How can this be implemented?

more ▼

asked Jan 18, 2011 at 07:00 AM in Default

avatar image

xnl28
905 60 64 68

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

If you want dynamic filtering like Excel, why not provide the data to an excel sheet? You can do this with SSRS and wouldn't need to make any changes to the report or sproc.

more ▼

answered Jan 18, 2011 at 07:36 AM

avatar image

WilliamD
26.2k 18 35 48

  • : read my mind.....

Jan 18, 2011 at 11:31 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

Use a parameter in the report and reference that value from a filter on the table object that you want filtered. The expression will be something like '=Parameters!paramName.value' but you can select it through the expression GUI.

This [http://www.mssqltips.com/tip.asp?tip=1897][1] shows you how to add the filter, etc.

Its a shame you cant alter the procedure as every time someone runs the report with a different filter value it will still be calling all the data from the database. This means the data server and the network will be working harder than is necessary.

[Edit]
@WilliamD points our no report parameters can be added.
Can you create a linked report that could have parameters? [1]: http://www.mssqltips.com/tip.asp?tip=1897

more ▼

answered Jan 18, 2011 at 07:24 AM

avatar image

Fatherjack ♦♦
43.8k 79 99 118

@Fatherjack - xnl28 cannot add a new parameter. Your arguments are totally valid, but the limitations don't allow them to be implemented.

I would say that the requirements and limitations stop any sensible solution being achieved.

Jan 18, 2011 at 07:35 AM WilliamD

Ah, I read that the sp parameters couldnt be changed. Figured a report param could be added...

Jan 18, 2011 at 07:46 AM Fatherjack ♦♦

I think it is a perfectly sensible requirement for SSRS to provide filtering functionality in a similar way to Excel and Access - they are all Microsoft data/reporting products.

If a new report parameter was required to filter on a column, then in my case the report would need 12 new parameters (the report has 12 columns which need to be filtered). This would make the report quite unweildy. In addition to this, all the filter parameters would begin by being free text, along with all the pains that would cause the user. Ideally, all 12 parameters would display a list of possible values from the data, and I don't know how this can be achieved seeing as the report dataset is populated from one stored procedure.

Jan 18, 2011 at 07:54 AM xnl28

@xnl28, Keep in mind that a SSRS report can use more than one SP (or table) at at time. So for filtering, you can get distinct values from a different SP or table - that is, if you're allowed. :)

Jan 18, 2011 at 08:01 AM Mark

@xnl28 - I think your needs are mixed between SSRS and using something based on a datawarehouse - like PowerPivot. SSRS is very much a web-based method of distributing data, not necessarily for the heavy duty analysis that you are alluding to. You may be best off looking into a different tool that will match your needs more closely

Jan 18, 2011 at 08:06 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x698
x24

asked: Jan 18, 2011 at 07:00 AM

Seen: 4306 times

Last Updated: Jan 18, 2011 at 07:06 AM

Copyright 2017 Redgate Software. Privacy Policy