Should I be using SQL Reporting Services 2008 for my reporting engine?
I would like to use SQL Reporting Services 2008 to create my reports, but I want to use my own interface to specify the report type, columns, parameters and everything. I want to accept these criteria and then run an asynchronous request in SSRS and email the report to me. Is it possible? I don't want to go full steam through SQL Reporting Services 2008 just to realize that it doesn't do what I need it to do. In addition, I will have a ton of DB partitions from which to retrieve data. Some reports will need to fetch data from only one of them, but others may actually need to distribute different databases. Is it possible when submitting a report request to SSRS to specify which server / database name to pull the data with? Is it possible to transfer data from several databases to it and combine them? Thank.
source to share
Like Crystal Reports, ActiveReports, and other report generators, SSRS has two main elements for each report: SQL query and report layout. Regardless of which tool you use for SQL - it can be embedded SQL in a report or a call to a stored procedure - it will be the same query. Multiple databases are ok if you can specify them in the front.
You can have parameterized queries, so the user is prompted to enter the appropriate filters (customer ID, product group, date range, etc.).
Layout a report is similar to other tools — you drag controls, such as labels, into the report and set their formatting.
SSRS provides many 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 view, run in the early hours of the morning, and cache it so that each user doesn't have to wait for a long request to run.
This is a great tool. I think it will be worth your effort to experiment with it. I would wait to create a custom user interface until you've exhausted the inherent capabilities of the tool.
source to share
SSRS is not designed with this scenario in mind, as I'm not sure any of the out-of-the-box solutions will have a graceful solution to this. While SSRS can do what you ask (as others can), it is by no means fast or easy. You seem to be looking for an advanced ad-hoc dynamic dataset solution. I would first ask a question about the requirements and determine if the business scenario really justifies such an implementation. I would weigh my own building the solution versus your learning curve with a BI reporting solution. You may find it easier to just create something yourself.
source to share
I think you can build a pivot for your back to a certain extent, as RS sends multiple interfaces for reporting.
Remember that the final product is an rdl file, which is nothing more than xml, so you can write them by hand if you really like.
Multiple data sources are supported, but there is no merging on one control / chart / etc, so you need to configure the cross-database capability from one of your data sources prior to querying the report if you want to do so.