Solved

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

Posted on 2013-02-07
5
6,830 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 66

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

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

627 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