I would like to use SQL Reporting Services 2008 to generate my reports, but I want to use my own UI for specifying the report type, columns, parameters and everything. I want to be able to take these criteria, and then kick off an asynchronous request to SSRS and have the report emailed to me. Is this possible? I don't want to go all the way down the road of researching SQL Reporting Services 2008 only to find that it doesn't do what I need it to do. Also, I will have a ton of DB partitions that the data will need to be pulled from. Some reports will need to pull data from only one of these, but other ones may actually need to span different databases. Is it possible when sending a report request to SSRS to specify what servername/database to pull the data from? Is it possible to tell it to take the data from multiple databases and combine it? Thanks.
R – Should I use SQL Reporting Services 2008 for the reporting engine
reporting-services
Best Answer
Like Crystal Reports, ActiveReports and other report generators, SSRS has two basic elements behind each report: the SQL query and the report layout. No matter what tool you use for the SQL -- it can be inline SQL in the report or a call to a stored procedure -- it's going to be the same query. Multiple databases are fine as long as you can specify them up front.
You can have parameterized queries, so the user is prompted to input the relevant filters (customer ID, product group, date range, whatever).
Doing the report layout is similar to other tools -- you drag and drop controls like labels onto the report, and set their formatting.
SSRS does provide a lot of options for distributing the report, including email. You can embed the report in an ASP.Net web page, leave it on the report server site for users to browse to, run it in the wee hours of the morning and cache it so every user doesn't have to wait for the lengthy query to run.
It's a great tool. I think it will be worth your effort to experiment with it. I would wait on creating the customized UI until you've exhausted the possibilities inherent in the tool.