As I started using SQL's reporting services, I am really happy about the ease and power of its designer BUT can't decide where a server-side report (based on SQL reporting services) fits in an n-tier application .
The problem is that the BL is the tier that handles all security and BL logic while an SQL report seems to totally ignore the BL and directly connect to the backend database. Is there a way to build SQL reports based on BusinessObjects? Anyone has used SQL reports with LLBL entityCollections or the likes??

Great question. It seems reporting is left out of n-tier apps many times. Especially when the reports are generated by some like SSRS.

I have not tackled this issue and would probably just end up writing SQL code to generate the reporting data, because that is probably the 'easiest road to a solution'.

However, I think the IDEAL method would be to create a custom data extension for SSRS. You can write assemblies to extend both the reporting and the data retrieval. That means we could create an assembly that used the SSRS API and LLBLGen - and that would be the best solution.

Another solution might be to use WebServices to generate the data for SSRS. With SSRS 2005, you can use the XML provider to consume data from web services for your report and of course with LLBLGen Pro you can use the webservices templates to generate the webservices.

http://msdn2.microsoft.com/en-us/library/ms345334.aspx

Unfortunately I am stuck in the world of SSRS 2000 for a while, so not sure what I want to do. Still think the custom data extension would be nice.

omar wrote:

As I started using SQL's reporting services, I am really happy about the ease and power of its designer BUT can't decide where a server-side report (based on SQL reporting services) fits in an n-tier application .
The problem is that the BL is the tier that handles all security and BL logic while an SQL report seems to totally ignore the BL and directly connect to the backend database. Is there a way to build SQL reports based on BusinessObjects? Anyone has used SQL reports with LLBL entityCollections or the likes??

I don't recommend you use entities/business objects/collections with reporting. These objects are about normalized data and "things". Reports are about aggregations and lists. They are, by definition, _de_normalized data.

That being said, you can use LLBLGen's excellent query system to create datatables that perhaps can be bound to your reports (not sure how reporting services works), and this would afford you the ability to creating a reporting services tier that allows you to reuse existing business logic codifed in your business layer. It also abstracts out the data source and allows you to compile the data however you see fit. This won't be as performant as a straight SQL solution, but you asked about introducing LLBLGen, so...

Jeff...

In reality, most businesses want to quickly author and build their own reports. Enter SRS. I like to create a dedicated read only user that only has access to a set of predefined views that users can build their reports from.

In addition, I also prefer to create a seperate reporting system / database that doesnt interfer with the normal IO for the production OLTP database.

If you simply need a few reports in your application, you might as well use a more lightweight mechanism. Installing, running, configuring, and maintaining an instance SRS for just a few reports is overkill (IMO).

The really big drawback of SRS is its limited support for forms authentication.

But to answer your question, I have never found a way to use a custom data source for a report.

Some other report engines support setting a data source dynamically at runtime, if you're not married to SSRS.

Thanks guys for the great insights. I am more inclined to treat my reporting as of two types: Type 1 reporting (UI reporting): these are light weight reports that are treated as an extension to a WinForm or a WebForm (example, invoice, Leave form, ..etc.). These reports are best build using non-server reporting engines (CrystalReports, XtraReports, ...etc). These reports can be used with LLBL style objects and collections. Type 2 reporting (Server reporting): I tend to agree with what jeffreygg said (specially after attending a week long course about SQL2005 Analysis services). The motivations of introducing SSRS reports are very similary to the motivations of building a DataWarehouse to support cubes (although it is an overkill to build a DataWarehouse just for SSRS reports). This meens that the role of (Data Analyst) should be introduced in the development team that goes through a devolpment cycle that is not concerned with DAL, BL but more concerned with builing a BI (Business Intelligence) interface the same way our DAL and BL are build to support our WinForm, WebForm, Webservices UIs.