May 6, 2011 at 8:14 pm
Is it possible to create a completely dynamic report?
What I'm trying to do is to have the report have a parameter that selects from a list of available tables and/or views. The report then dynamically adapts to the columns in that data set, and displays the data.
Is this even possible?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 7, 2011 at 10:05 am
Out of curiousity, what would be the compelling reason to have a single report instead of separate reports?
My first thought is to have each data region you want (tablixes, charts, etc) exist within its own Rectangle that defaults to being hidden. Then whichever one they choose from the drop-down list gets unhidden. But i believe this would result in executing all of them each time, resulting in a lot of wasted resources.
Could you instead have the main report essentially be a list of hyperlinks to each of the optional reports? Then, each data region could be its own report that only gets executed when the user clicks that hyperlink. That would be far better from a resource utilization standpoint.
May 7, 2011 at 10:38 am
What I've done in the past (and created an entire customer-facing product from) is to make a set of generic templates: 2, 4, or 8 columns. Abstract the data source the same as you would a business/data layer, and have the report pull from "GenericDataset" "Field1, Field2, etc" and have the query return those fields back, handling the data complexity within the query.
The drawback is that you're either limited to a fixed-width column set and have challenges formatting each column, or you end up making 20-50 templates, thereby negating the value of a generic report in many cases. It's an OK solution for small-scale, but if you have 2,000 business customers, you're almost guaranteed to have one that demands more.
The solution I came up with impressed management, and was a hit for some customers, but almost half wanted more robust layout and formatting. It simply is unrealistic to create an end-user friendly report that allows them to select anything from everything, and then get white-glove service at the same time. Everybody wants this, but I've never seen it done - not well, at least. The SSRS Report Builder goes part way there, but you need data models, training, etc, and then someone is still doing manual layout.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply