Reporting/BI best practices

Posted on 2011-10-04
Last Modified: 2012-08-14

I am trying to figure out whether it's best to report off of a view, table or stored procedure. I know it depends on the situation and what one is looking to accomplish, but could someone please let me know the various situations when it's best to use each of these?

Question by:erp1022
    LVL 18

    Assisted Solution

    I use stored procedures whenever possible.  It gives you a layer for potential logic without touching your report.

    Views are a nice way to format your data and give you the ability to reuse similar table joins in multiple reports.  

    Hitting tables is my last choice.  It is a very rigid architecture which I prefer to ignore.
    LVL 100

    Accepted Solution

    In one large project I worked on we built all the reports against views and stored procedures.  In that way we didn't have to give the users permission to the tables so they couldn't get at them directly to produce reports.

    In most instances if the report is relatively straight forward I will report directly against the tables.  In many of those cases I didn't have permissions to build views or stored procedures.

    Stored procedures give you the advantage of being able to preprocess the data with summaries, specialized filters, logic on which field to choose, etc.  They also tend to be a bit faster than the tables or views.

    Views and stored procedures help protect you from changes in the tables.  A table can be changed but if the change doesn't affect the fields in the view or stored procedure then the report doesn't require any updating.

    Other options that are preferred particularly wih Crystal is to report against a dataset.  Then the report can be moved to work against a different database without problems because it is still built against athe recordset you just pass a dataset built aginst the appropriate database.

    Another option, I used was to build the reports against XML files. The XML files can be built by the user in an application and then used in the report.  Again it makes moving between databases and even table changing easy.

    Things to consider when choosing the data source of the report
    1.  Security of the data from user misuse (extraction of extra data, extraction of data they shouldn't see, potential to update the data outside the application)
    2.  Is the database changing?  New fields in tables?
    3.  Is there intricate manipulation that might be faster if done on the server?
    4.  Are you filtering based on data in another view or select statement
    5.  Will you be running the report against several databases?


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
    Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now