July 6, 2007 at 11:26 am
I have a Crystal Reports background from a previous job, and am being asked to implement a MS Reporting Servics (2005) solution at my current job. I've been playing arround with it, and I'm OK with simple little canned reports, and even simple report models to allow people to do their ad-hoc reports.
But here's where I hit a snag. We want to use it to replace an existing application that generates AdHoc reports. On the DB server it hits, there are about 25 databases, each with identical structure but with different data. There are a set of views in there that flatten out the data that the app uses to allow AdHoc reports. The thing I'm struggling with is how to allow them to put data from a variable nubmer of those databases in a single report. Currently, a dynamic SQL query is generated based on which databases they select.
So far the best Idea I have is to create one monster view that UNIONS all of the data together, then use a multi-select parameter and filter based on that. But the performance of such a query would be unacceptable as I think it would have to do all of the union queries, and then filter.
The only way I can think of to make it work efficiently is the dynamic-sql solution.
Is there a way to do that in Reporting Services? Any other ideas?
Thanks,
Jason
The Redneck DBA
July 6, 2007 at 1:41 pm
Create a Centralized Report database
Make that database your Data Source,
Encapsulate all your query logic within a stored procedure in that database
Use Partitioned Views and or Dynamic Sql to query your data.
Use report definition to call the stored procedure.
August 1, 2007 at 9:25 am
The Report database is really what we need...but at this point not feesable in the near future (long story).
I've figured out how to pass a list of sources into the SP and create a dynamix SQL query based on that. (A select for each source, unioned together).
Now the problem is if there is any filtering, the SP needs to return all of the data to the client, and the client does the filtering. Is there a way to pass along the filters into the SP so I can add WHERE clauses?
I'm not sure I like this solution even if it does work...seems awfully injection-prone.
I've also been given permission to look at other products besides reporting services. Does anyone know of any other products that can accomplish this? I can't imagine this being that unique of a task (other than not being able to have a central report repository)
The Redneck DBA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply