Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-02-07
5
Medium Priority
?
7,259 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 1332 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 668 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 1332 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

721 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