OT: Dynamic Reporting Using ReportingServices Local Mode in WinForms CSLA App

BigPines2 posted on Wednesday, September 09, 2009

I am using ReportingServices in Local mode in my WinForms app. Although, ReportingServices has some nice things about it, I am getting tired of designing new reports for every simple list based report I want to run. Unfortunately, the report requires a strongly typed data source so you have to create a new report (and object) for each use. This is tedious and does not allow the flexibility I would like.

Eventually I am going to need something more dynamic. For instance, our operators are currently using an app that allows them to define their own custom reports. They define the fields and then only those columns show up on the report when it is run. I would eventually like to do something similar.

Ideally I'd like to use the same report for all list based "simple" reporting and just change the column headers and title. I thought of just binding to a schema with all string fields and then plugging in whatever string collection object I want to display but even this requires you to know how many columns you want to display and different lists have a different number of columns. Am I simply using the wrong tool? Is there something better to use for these generalized dynamic type of list reports?

I know there are lots of smart people on this forum and I'm sure somebody has already tackled this problem. Any advice is appreciated.

Mike

P.S. Because I got tired of creating new complicated strongly typed CSLA collections for every list type of report (I don't do code gen yet), I created a special report object inheriting from CommandBase that simply hits the DB and converts the SafeDataReader into a DataTable to send directly to the report. In the report designer, I set the report's data source to the specific Stored Procedure the object will be hitting. I know this is a real departure from CSLA's strongly typed objects but for reporting purposes, I think strongly typed lists are overkill. This option allows me to continue to use the data portal in a standard way and yet avoid most of the extra work of the strongly typed list. Anyway, this doesn't get me where I want to eventually go but it does help somewhat.

JonnyBee replied on Wednesday, September 09, 2009

Hi,

You can generate a report definition programmatically.

I know that there are developers that have generated the rdlc file (which is an xml-file) with headerinfo and columns based on the data object and then bound the report and data in reportviewer to produce dynamic reports.

Have a look at the samples from www.gotreportviewer.com:

• Dynamic Table Report  as an example of to dynamically generate rdlc files. This sample is available in both C# and VB -the link provided here is for C#.
• Dynamic Matrix sample shows how to generate RDLC at run-time based on user-specified options.

The rdlc specification can be found on this page SqlServer 2008 Reporting.

/jonnybee

BigPines2 replied on Wednesday, September 09, 2009

JonnyBee:

Hi,

You can generate a report definition programmatically.

I know that there are developers that have generated the rdlc file (which is an xml-file) with headerinfo and columns based on the data object and then bound the report and data in reportviewer to produce dynamic reports.

Have a look at the samples from www.gotreportviewer.com:

• Dynamic Table Report  as an example of to dynamically generate rdlc files. This sample is available in both C# and VB -the link provided here is for C#.
• Dynamic Matrix sample shows how to generate RDLC at run-time based on user-specified options.

The rdlc specification can be found on this page SqlServer 2008 Reporting.

This looks like it is *EXACTLY* what I was looking for! I will dig into this right away. :)

Thank you very much!

Mike

P.S. I will report back on how it goes.

ajj3085 replied on Wednesday, September 09, 2009

Hmm... I was hoping to look at Reporting Services to let my users make their own reports, so I don't have to. Anyone have any luck with that?

JoeFallon1 replied on Wednesday, September 09, 2009

We just implemented an entire susbsystem of our Web app to allow users to design and manage reports using Reporting Services. It was quite a large undertaking. We built 20-30 different models which are connected to a common DataSource (think "connection string"). When a user creates a report based on the model they are automatically using the right data source and are limited to the fields in the model. We had 2 developers designing models.

I handled the Web Service interactions for deploying the models to the client's Report Server instance. One of the key things we stumbled over was the need to merge the DataSourceView XML into the end of the SemanticModel XML and deploy them as a single XML file! We also decided to encrypt the models and enforce a licensing scheme so that clients who want end users to build reports have to license each set of models for each major subsystem of our app.

A client "Admin" can assign permissions to users for licensed models and also let users assign permissions for each report that they create.

Not a whole lot of CSLA involved (if any) in all of this except for the Manage Models and Manage reports screens.

The creating, building and deploying of models through a web service is handled by a WinForms wizard that I wrote. I found a fantastic wizard.dll that makes building them a snap. (See http://www.codeproject.com/KB/miscctrl/DesignTimeWizard.aspx?msg=1308991)

I want to quit Web development and build wizards for a living! The wizard dll came with source code which I tweaked to make it even easier to use. Many people recommended adding a stack to track forward navigation and then just popping off the stack for the Back button. So I added the sample code to the source and it worked great! I found a small bug and was able to tweak the source code to handle that edge case. (pop-up message boxes that leave you on the same page cause the page to get added to the stack again. So you need to make an extra Pop when navigating Back otherwise the user has to click Back a 2nd time.)

This is the code fix:

if (index == wiz.PageIndex && wiz.PageStack.Count > 0)
  index = (int)wiz.PageStack.Pop();

I was even able to add my company logo to the wizard dll so that each page has it in the bottom left corner. I wrote another wizard for a different project and really enjoyed working with it.

Joe

 

ajj3085 replied on Thursday, September 10, 2009

Cool... that's what I was hoping for, building models and then letting the users go at it via reportbuilder.

Fortunately, it sounds like your application is larger and b/c its off the shelf, that's where the complexity came in.. my app is likely much smaller in scope (it has only me developing it for a few years) and internal.

So you did build a UI to query the available reports on the report server? That was my largest question mark... if the reports are on the server, how does my application get the list of them, know how to prompt for parameters (or does RS handle that?).

I do have one wrinkle that maybe your experience could be adapted for. The company I work for is family owned, and own three other companies. Seperate, but not really (they all use the same two locations and are all on the main companys domain). So I had to build branding in my application, and the database itself is seperate, but all four databases are on the same server... so I need to keep the reports seperate. Any thoughts on that?

Oh... also, did you use Sql 2008 or 2005? We're still on 2005, but I heard there were many improvements to reportbuilder in 2008.

Thanks
Andy

JoeFallon1 replied on Thursday, September 10, 2009

1. "So you did build a UI to query the available reports on the report server?"
Yes.

2. "if the reports are on the server, how does my application get the list of them"
You use Web Services. Luckily the WebService is identical for SQL 2005 and 2008! So you only need 1 Web Reference to create the proxy. I usually create a class that wraps the proxy so that I can construct it with all of the values that the proxy will need to make the connection. Many of them are "setup options" in our database so it is a simple matter of reading them out of the DB, getting an instance of my wrapper class and you now have a properly constructed proxy which can talk to the web service. By using DB values you can actually talk to different report servers using the exact same code since the DB values will be different for each client.

3. We decided up front to have a "well known" folder structure on the Report Server which we could then leverage in all of our code. When you use the Report Server UI and click Home you are at the top level. We have web service methods to "CreateFolders" which then go:

\AppName
  \Version
    \Models
    \Reports
    \DataSources
      \MyAppDBConnection (we have our own name for this but it is the connection to the app database)

4. We have other web service methods to deploy models, re-deploy models for a new version, copy reports from an old version to a new version (surprisingly there is no such feature in the MS product - you can *move* reports but not copy them.)

5. Report Builder 1.0 in 2005 is much simpler for a client to use. Report Builder 2.0 in 2008 is more powerful and thus more complex. A basic enduser has a shot at building reports in 1.0 - not so easy in 2.0. May need more advanced users for that. Or training.

Joe

 

 

 

 

 

 

 

ajj3085 replied on Thursday, September 10, 2009

Joe, thanks for the great feedback! I feel much more comfortable taking this approach now.