How do I bind CSLA business objects to SQL Server 2005 RS Report?

I have what may seem like a stupid question:

I am using CSLA and have lots of objects created and I've succesfully used them on WinForms and ASP pages.
I am new to SSRS and cannot find how to use the BOs to a report.

I found on this forum that I can use my BOs as a data source for SSRS

check: http://forums.lhotka.net/forums/post/11839.aspx
3.Base your reports on your BO.  This is a new feature in SQL Server 2005 Reporting Services

How can I do this?

thanks!!

lazaropi

We have the same question. We have a smart-client app using remoting to get data across the internet, but client-side reporting is s-l-o-w for lots of records. Server-side reporting with SSRS looks great but we'd rather not recreate our whole business layer to access the database directly. Instead we'd like to use the CSLA business objects we already have, even if we have to use an ObjectAdaptor to trick SSRS into thinking they're real datasets. Lazaropi above references a Rocky post where he claims SSRS 2005 supports BO binding, but we can't find this anywhere in Microsoft documentation. Is this functionality actually client-side only? (In which case it would be the SQL Server Report Viewer that supports BO binding, not technically Sql Server Reporting Services).

Thanks,
Glenn, CarteGraph Systems

If you want to use server side reports, forget about .Net at all and simply build some views and bind your reports to the views. 

If you want to use your Csla BOs as datasources, you can only use client side reports.  SSRS doesn't support using objects as datasources when creating a server side report.

Its not a function of the ReportViewer control, its the underlying report framework.  If you look, you'll see there are actually seperate Report objects for server and local:

Microsoft.Reporting.WinForms.LocalReport
Microsoft.Reporting.WinForms.ServerReport

HTH
Andy

My team tried to use SSRS for a project and we ran into way too many limitations.

It does not seem designed to handle application specific reporting scenarios.

We opted to go with Active Reports .Net and find it to be an excellent product.

We built a reporting framework usng CSLA BOs as the datasource for unbound reports. The framework fetches the Header data and the Detail data and then maps the properties to the controls.

We built 50 reports in a couple of weeks using this framework. Designing the layout of the report was the hardest part.

The only catch is that the data has to be pre-sorted and grouped - the report tool does not do it for you. So your queries have to be just a little bit smarter.

Joe

 

We're using ActiveReports in some of our other products and it *is* very nice, but in this scenario we have tens of thousands of records, and remoting them all to the client for reporting is way too slow. AR doesn't appear to do server-side reports. By hard-coding our SQL we're getting SSRS to generate our reports server-side, and it's nice and fast, and since we were using SQL Report Viewer client-side before, we don't have to re-do our report layouts (much). But it's going to be a pain to re-do our business layer to add dynamic SQL just for reports. Using the BOs would be much nicer.

If everything is working fine server side, why do you need to redo your BOs?

It's only working fine server-side with our one test report where we hard-coded the SQL to do some performance tests. In the live application the user can add custom fields to the database, so the report SQL needs to be dynamically generated at run time. Our BOs do that for us.

Ahhh.. that's an important detail.

Assuming the users that can change the schema are administrators and don't do this very often, you may want some BOs that generate the approprate Sql (perhaps as views) and server reports as the users add and remove the custom fields. 

Even if you did not give the users the ability to customize the database it seems to me that you would still have to redo your Buisness Logic (writing everything in raw SQL statements) if you wanted to do SQL 2005 Reporting Services Server Side?

Your idea is an interesting idea though. It would be nice that you would not have to dupilcate that code. I wonder if there is a way to create a custom plugin of some sort into SQL to use CSLA Buisness Objects Server Side?

Because if you are doing remoting (not enterprise) over the Internet and 'real' reports (like 10,000) I would think that the performance would be painfully slow, if not, unuseable?

Server Side reports in your case sound like the only option. Because even if you did pagination any report that did grouping, sorting, or a chart would still need to get all the data for a Client Side (local) reports. I remember reading Rocky said something about being careful sending down no more than 1,000 in a list. Anyone have anyother thoughts on this?

Another suggestion; since you're using remoting, perhaps you should look at Xal's CompressedRemotingProxy?  He posted recently about that; you may be able to heavily compress the remoting stream to speed transmission of the data.

Andy

ajj3085 thanks for the information about the CompressedRemotingProxy. We will check that out!

We have an update. We have successfully found a way to use CSLA.NET and Server Side SQL 2005 Reporting Services without any duplication of code! What we did was create a "Custom Data Processing Extension" for SQL 2005 Reporting Server. Here is an article from Microsoft: http://msdn2.microsoft.com/en-us/library/ms154655.aspx

What this gives you is a way of essentially binding your Business Objects, just like you would in Local Reporting. Initial testing looks promising. Huge reports seem real snappy with Pagination turned on. This is because we don't remote all the data at once to the client (like local reporting). It processes everything server side and then just sends a page of data at a time. This seems like the only solution if you have thousands and thousands of records and you are using CSLA's remoting.

Microsoft has a skeleton code project to jump start off with in SQL 2005 Samples called: "FsiDataExtension"  (http://msdn2.microsoft.com/en-us/library/ms160918.aspx). You can get the sample using the SQL 2005 installer, or off of Microsoft's website. Here is another good example:
http://www.devx.com/dbzone/Article/31336/0/page/1

There are a few little tips we found out.

Server Side:
Copy DLLs to c:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin. This Includes CSLA.

In the ReportServer directory, in rssrvpolicy.config, you need to have a "code group" for both the your strong key and for CSLA's, which would be different than yours. We used secutil to generate "publickeyblobs" for both (secutil -hex -s dllname).

E.g: 
<CodeGroup
    class="UnionCodeGroup"
    version="1"
    PermissionSetName="FullTrust"
    Name="Kodiak_Strong_Name"
    Description="Key for Your DLLs.">
    <IMembershipCondition
   class="StrongNameMembershipCondition"
   version="1"                   PublicKeyBlob="0q238490821093480293845908230945802938450923840582390485really-long"
/>
</CodeGroup>

<CodeGroup
      class="UnionCodeGroup"
      version="1"
      PermissionSetName="FullTrust"
      Name="CSLA_Strong_Name"
      Description="Key for CSLA DLL.">
      <IMembershipCondition
            class="StrongNameMembershipCondition"
            version="1"                                                       PublicKeyBlob="0024000004800000940000000602000000240000525341310004000001000100AFE494440F03A57E64464D6D407F83E51F2C8A412F8E9144BDAF02DF16234C2074149908E37A67E060E61E23456E985515C1FAE98050E802CE6859E9929587C5BB66AC32D21DBCF823D3E055CD9483AF45BDC12DC4A47168CF04FC384F485F3EA588B72A1FD60BEC53AD6DF5B1A26FE4F9BBBCE560AAF27014F65D099A1994BE"
/>
</CodeGroup>

In the same ReportServer directory, web.config contains the appsettings and connectionsettings for the two-tier CLSA connection to the database.

In the ReportServer directory, the rsreportserver.config file contains a line in the Data section for your new data source.

E.g:
<Extension Name="KODIAK" Type="Kodiak.Infrastructure.ReportConnector.CgConnection,Infrastructure.ReportConnector"/>     

Client Side:
(for the Business Intelligent Report Designer (integrates into Visual Studio 2005)... Can be installed off of the SQL 2005 Disk):

Copy DLLs to Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies. You will also need CSLA, and Microsoft.ReportingServices.Infrastructure (you can get that off of your reporting server).

In the same directory, RSReportDesigner.config, you need lines for the your new data source in the Data and Designer sections. Note that the lines are different -- the data section refers to your project namespaces and assemblies, whereas the Designer section references Microsoft stuff.

E.g:
<Extension Name="CSLADATA" Type="XYZ.ReportConnector.Connection, XYZ.ReportConnector"/>

<Extension Name=" CSLADATA " Type="Microsoft.ReportingServices.QueryDesigners.VDTQueryDesigner,Microsoft.ReportingServices.QueryDesigners"/> 

In the same directory, you need the CodeGroup for your strong key, with its PublicKeyBlob.

In this directory's parent (the IDE directory itself), devenv.exe.config needs the application settings -- appsettings for CSLA authentication and connectionstring for two-tier db access. Or three tier.

You don't need to publish your reports!!! You can just send them from the client and the Reporting server will temporarily store them. This allows for a more dynamic reporting solution where you could change your RDL files on the fly. See this article. http://msdn2.microsoft.com/en-us/library/ms167274.aspx

For your Custom Data Extension you will need to call your login method for your CSLA Business Objects (passing username and password, perhaps as a parameter... You should setup SSL on Reporting Services). This method call is in the connection class "void IDbConnection.Open()". You call your logout here: "void IDbConnection.Close()".

You would get a CSLA Buisness Object List in this method "internal DataReader(string cmdText)" within the DataReader Class. Also you would read each record here: bool IDataReader.Read(). You need to get the name of the field here "string IDataReader.GetName". Here is where you get the datatype "System.Type IDataReader.GetFieldType(int fieldIndex)"... Returning "System.Type.GetType("System.<whatever the datatype is>")"; "object IDataReader.GetValue(int fieldIndex)" is where the report gets its actual values.

The rest of the classes in the Microsoft example can be left alone.

Of course whenever you try to do anything with SQL Reporting Services you will end up sending inordinate amounts of time banging your head. Don't say we didn't warn you! There are very few things you can to debug and you will just have to guess. But once you get everything in place the benefits do outweigh the pains of setup.

Glenn,

Thanks for the update!  It sounds like a lot of work, but its good to know there is a solution that is possible.

GlennMiller,

Thank you for the information. We have completed a full course for SSRS few weeks ago, and I was wondering how I could reuse our CSLA .NET BO with SSRS Reports. The course instructor was unable to answer this question for me. My basic question was how to bind SSRS Report to a DataSet or BO Datasource ?

I have developed a BO based on CSLA .NET, and it connects to SQL Server, MS Access & Adabas to retrieve Staff Information in a list format.

I was wondering how I could reuse this logic to develop reports using SQL Server Reports.

I will try the approach you referred to.

If you have any useful updated, I would appreciate letting me know.

Tarek.

I've only done this with local reports, but it should be just a matter of adding to the DataSources collection on the LocalReport instance.  If it's a BB derived class, make sure to create an IList and put the BO into the list, and give the list as a datasource.  The reports always expect something that implements IList or ICollection or something.

HTH
Andy

ajj3085,

Thank you for the info.

There is one small issue which is confusing me a little bit.

I found another nice article:

http://msdn.microsoft.com/en-us/library/ms251692(VS.80).aspx

By checking this article, it does not tell you to do any kind of inheritance from SSRS Interfaces. It looks like it is much simpler than I expected, since the article referenced in this thread ask you to implement several Interfaces which looks a bit complex.

Do you have any comment on this ?

Thank you.

Tarek.