Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 285
  • Last Modified:

Reporting/BI best practices


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?

2 Solutions
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.
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?


Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now