Report approach RRS feed

  • Question

  • Hi all, we are starting our report development now and some doubts have come up to my mind. I figure out some possible solutions, as below:


    1) create a view and associate it to the report.

    Good > easy to develop

    Bad > limited when we need to compute complex things


    2) create a SProcedure and associate it to the report

    Good > powerful when needed to compute complex things

    Bad > not portable among DBs; difficult to mantain


    3) code all behind (C# ou VB) and then populate a "temporary table" with all prepared result

    Good > it is able to compute powerful things; easy to mantain; easy to develop

    Bad > has to work with a temporary table; has to handle concurrent users


    What would be the best solution my friends? and why?


    Thanks to all !

    Tuesday, April 3, 2007 8:15 PM

All replies

  • Unless you just need sporadic reports here and there, I usually find it is better to prepare a separate database for reporting. This database is optimized for reporting (e.g. pre-calculated fields etc.). The reporting database is updated in the background so it stays current (replication or SSIS ). You can then use a dedicated reporting tool (such as reporting services) to build reports on that database

    The advantage is that reports takes less time to complete, you can handle concurrent requests, reports can and usually do cut across entities boundaries so it isn't efficient to deal with them using domain objects (which I what I usually have in the business layers), you can add/change reports easily

    I've written about  it in the context of SOA in a pattern I call Aggregated Reporting here:

    Tuesday, April 3, 2007 10:36 PM
  • I also prefer this solution. The big quizz here is how to control several users trying to print the same report. Then n machines would try to access the same table (assuming its 1 table per report) to create the report. How could I control this concurrency? Maybe using an unique ID? That sounds pretty cumbersome...

    any idea?

    Wednesday, April 4, 2007 12:49 PM
  • The solution I talked about would have a set of tables with data optimized for reports (a mall Datamart)  and not tables that are created per report
    The reporting database is updated in the background as updated to the OLTP database happen (the frequency of update depends on your needs)
    Since the running reports would only perform read operations on this database concurrency isn't that much of a problem

    Monday, April 9, 2007 12:55 PM