Reporting Services 2005 and Business Logic

I'm working on a app with complex reporting requirement and would like to centralize the business logic for app and reporting. We are not planning to use SP for reporting. Having said that, i would ideally like the reports call the business layer (business.dll) assembly which in turn will call the data access layer (data.dll) assembly to run the required queries and get the results via business assembly as a custom business entity or data set. I'm not sure if this possible with Reporting Services 2005. I have looked at the custom data processing extensions but i dont think it will provide me the required business-data separation unless i'm missing something.

I was wondering if anyone has done this before and if yes, could you please provide me some direction or insight or sample. It will be of great help. I also saw the custom references tab where you can reference an .NET assembly and invoke a method within .NET assembly but i'm not sure if that method could return a custom entity that could be consumed by the reports. Please recommend the right approach to achieve this.

Thanks in advance

There are at least four approaches you could consider:

1. Your app can call down to the business logic layer to retrieve the entity or an ADO.NET dataset and bind it to a local report. This scenario assumes that you don't need to deploy the report to the report catalog (the report is available to the application only). For more information about local vs. remote processing, read this article.

2. If you need to share the report with other users (deploy to the report catalog), write a CDE that will take the serialized entity (e.g. as an ADO.NET dataset) and expose it to the report, as demonstrated here.

3. Assuming SQL Server 2005, implement a CLR stored procedure(s) to call down to the business layer and surface the results to the report, as shown here.

4. Write a web service to return the results in XML and use the RS 2005 XML data provider (see this post).

The first approach is probably the easiest to implement.

|||

Hello Teo,

Thanks for your response. Since this is a web app and would like to deploy to the report catalog i'm thinking of option# 2. I have read the customer data extensions and i see it more as a data layer assembly. We will have to retrieve the custom entity from the middle tier, update it (only if required) and then display using CDE. Now based on that i have these follow up questions

1. Our entity is not going to be a ADO.NET data set at the business layer level, its going to be a custom .NET class so will be possible for the CDE to retrieve this custom entity and then display it. I can serialize the custom .NET class into a XML and then have the reports consume the XML but i'm concerned about the app performance serializing/deserializing.

2. I do not require any open/close connection in the CDE since this will be handled by the app data layer. Is this OK?

3. What is your opinion about accessing .NET code via assembly references and embedded code within the report properties? Is this a good option to think about.

Thanks

|||

1. Performance should be an area of concern. Assuming 100 Mbts LAN, it will take a fraction of time to send the serialized payload across the wire. I didn't see any performance issues with my CDE which serializes ADO.NET datasets.

2. The CDE that I pointed you to doesn't establish a connection either. This is entirely optional.

3. You cannot use this approach to bind a dataset to a report region if this is what you are after. That's because the DataSet property is not expression-based. Other than that, I do use external .NET code extensively to call common functions, e.g. to handle number formatting. In general, I'd recommend you put any .NET function that you need in your reports in an external assembly so you can reuse it across reports.