Solved

SSRS report with filterable (like Excel filter by form) column headers:  Any workaround?

Posted on 2013-02-07
5
5,376 Views
Last Modified: 2013-02-13
Hi All

I have a client that is asking me if SSRS 2008 can accommodate column headers where the user can click and filter by form, like Excel.

Looks like the answer is you can't do that.

My question:  Has anyone been in this situation, and proposed any graceful work-arounds?

At the moment I'm about to pitch parameters for all column headers, where the parameters call various views that contain all values, and then the user re-runs the report with every change in parameter.

Thanks.
Jim
0
Comment
Question by:Jim Horn
  • 2
  • 2
5 Comments
 
LVL 6

Assisted Solution

by:liija
liija earned 333 total points
ID: 38865417
I agree, can't do that.
I don't have any good work arounds...

You could utilize interactive sorts - it might help a bit.

Having a parameter for every field can cause performance issues, especially if you use multi value parameters. Then SSRS and SQL places all the possible parameter values to your SQL, that's bad (WHERE ProductID IN (0,1,2,3,4,5,6.....n)). So other option - thinking of performance would be to split the report.

The best option which I have used, is to go through the real requirements of the report with the customer. On some occasion I have built a main report with a few drill through reports. Or split this one multi purpose report into three 'special' reports. That has actually served the customer needs better than the report with a huge parameter set.
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 167 total points
ID: 38867035
Hey Jim,

Unfortunately, the method you're proposing is the one I'd implement as well when asked for such requirements.

Or, as liija mentioned, is there any possibility to simplify the situation from the user perspective?  Maybe the following question can help: do all users need to be able to filter on all columns, or does user A always want filter X while user B wants filter Y?

The above is of course simplified as well, but let's say a user is only interested in certain specific filter combinations.  In that case it could be more interested to build a couple of tables that store the user credentials and the filter combinations in which their interested.  Each filter combo would have a label.  Your report would then have one parameter that displays the configured filter labels for the user that's requesting the report.

This will of course require some stored proc creativity, probably with dynamic SQL, but it should work.  I've used a similar technique when requested to build a report with 5 drill-down levels, 8 required filters and 14 optional filters.  On top of all those filters, the client needed some complex filter combinations that needed to be pre-configured through a custom application.  As you can imagine, I had some fun building that :)

Let me know if something wasn't clear.  Sometimes what's in my head doesn't come out as expected.

Valentino.
0
 
LVL 6

Assisted Solution

by:liija
liija earned 333 total points
ID: 38867066
I have also used dynamic SQL, must admit... You can get a nice report with that on this purpose. On one column you can choose the filtered column (e.g. customer/product/cost center etc). And the next ones are used for filtering the data. Of course, you can fill in the next parameters with the chosen values (cust/prod etc. data).

Looks nice but of course, the dynamic sql is not compiled. With large data there may be some performance issues. But probably less than when having all the parameters and filters in place.

Jani
0
 
LVL 65

Author Comment

by:Jim Horn
ID: 38884593
Thanks for the design help.

On this project I have a BA that is not very good, and a PM that is enforcing that client contact be solely through the BA, and the project has had a number of internal and external client business SME's, so my contacting the client directly with a 'what do you really want' is not permitted.  Because of all of this, I'm trying to minimize my time on the project so I can spend it on others where I can be a rock star.

I decided to kick out an Excel 2010 Spreadsheet with links to the SQL SP's, and the ability to manipulate the parameters via cells and refresh with a Go button.  This allows the tinkerers to tinker, with minimal development time.

>The above is of course simplified as well, but let's say a user is only interested in certain specific filter combinations.  In that case it could be more interested to build a couple of tables that store the user credentials and the filter combinations in which their interested.
Excellent idea, I'll have to work with this.

>Looks nice but of course, the dynamic sql is not compiled.
At that moment that's not an issue, but you are correct in that I don't want to open the door to performance issues down the road.

Thanks guys.
Jim
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 38884756
Good to hear you found a solution that works for the client!  If they want Excel behavior, give them Excel: I like that!
(and sorry to hear they're such a pita, but at least there's some BA being done :)

Good luck,
Valentino.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now