locked
.NET 4 and Reporting RRS feed

  • Question

  • Hi

    I am going through a massive learning curve (.NET 4.0, SSRS and Report Builder) at the moment and I am a little lost! So any help with the following would be much appreciated.

    Our requirements is this: Users to be able to create their own ad-hoc reports and for the reports to continue to work without modification if the underlying systems and their databases are migrated to a different platform.

    The systems currently use SQL Server 2005 and Microsoft Access 2003.

    I know I can give the users Report Builder, but that would require generating a model that's tightly bound to the underlying database (correct?). So, I thought I'd take a service oriented approach and expose the data needed to Report Builder using WCF Data Services and/or WCF Services. This way the Report Builder works against a Service Layer (as opposed to a Database Layer) and the service layer can be changed to handle changes to the underlying databases without the reports breaking.

    There is also another reason for pursuing this option. By using Entity Framework 4.0 we are building an EF model of our business domain. So, we are keen to be able to reuse this EF model for reporting purposes.

    Has anybody tried something similar. I am not an SSRS / Report Builder expert so I'd really appreciate any thoughts and comments. Please remember that a non-functional (i.e. Architectural) requirement is to reuse the EF model for reporting purposes, including ad-hoc end-user generated reports.


    Regards Amir
    Thursday, May 27, 2010 1:16 PM

Answers

  • You can  build your various wcf services using rest and returning xml.

    You then need to give the people designing their reports a list of these urls.

    They then consumer them as an "external data source".

    http://technet.microsoft.com/en-us/library/dd220468.aspx

    Whether you use EF or something else is pretty much irrelevent to the end user - they just get xml.  So your WCF can use EF or whatever you like.

    You will want to encourage users to document their reports so you know who is using what.  If your WCF services need to change at some point in time then you can inform all the people consuming them.

    Having said all that.

    Give users unlimited access to your data and they will use it without thinking.  It's usually an idea  to have a separate machine providing a snapshot copy of yesterday's data.  They burn the thing up with some unexpectedly huge query and your business still runs.

    In the past, I've set up a machine with sql express on it and written ssis packages that copy the data to a reporting database.  This had a different data structur emore suited to reporting with rolled up subtotals etc.  You then don't need to worry about wcf etc decoupling the data since you already decoupled it.

    • Marked as answer by amir tohidi Friday, May 28, 2010 12:50 PM
    Thursday, May 27, 2010 4:21 PM

All replies

  • You can  build your various wcf services using rest and returning xml.

    You then need to give the people designing their reports a list of these urls.

    They then consumer them as an "external data source".

    http://technet.microsoft.com/en-us/library/dd220468.aspx

    Whether you use EF or something else is pretty much irrelevent to the end user - they just get xml.  So your WCF can use EF or whatever you like.

    You will want to encourage users to document their reports so you know who is using what.  If your WCF services need to change at some point in time then you can inform all the people consuming them.

    Having said all that.

    Give users unlimited access to your data and they will use it without thinking.  It's usually an idea  to have a separate machine providing a snapshot copy of yesterday's data.  They burn the thing up with some unexpectedly huge query and your business still runs.

    In the past, I've set up a machine with sql express on it and written ssis packages that copy the data to a reporting database.  This had a different data structur emore suited to reporting with rolled up subtotals etc.  You then don't need to worry about wcf etc decoupling the data since you already decoupled it.

    • Marked as answer by amir tohidi Friday, May 28, 2010 12:50 PM
    Thursday, May 27, 2010 4:21 PM
  • Thanks Andy.

    I have already got my RESTful services retrning XML data and I can generate reports on them. What I was trying to do was to use an EF model, instead of the Report Builder one, that the users could use to for creating ad-hoc reports. It turns out, the only way to do this would be to wite a custom "data processing extension" that would expose my EF model to SSRS. We certainly do not have the time, or desire, to write such a tool so we will have to see if Microsoft write one.

    In the mean time, we are going for Plan B (using SSIS and Cubes etc) for our reports.

    Thanks for your help.


    Regards Amir
    Friday, May 28, 2010 12:50 PM
  • Good call.

     

     

    Friday, May 28, 2010 6:15 PM