Use Stored Procedure Instead of Crystal Report Query

When building any application that has data entry you will quickly require reports. These reports can come in a variety of formats. They may be in a .csv, .pdf, .rdf, Reporting services, Crystal Reports and a multitude of other options. However; when generating a Crystal Report or a Reporting Service Report you can actually link tables together, prompt for parameters and sorting records all within the designer screen. This might work when you know that your requirements will never change, however; this is rarely the case. If you build all of this in Crystal Reports and then need to change the tables or parameters it is very challenging to accomplish without spending a lot of time. If you always get into a habit of creating a stored procedure for each report than you can pass the parameters into the stored procedure and take advantage of several key benefits.

1. If a field name or table changes and you make it in the stored procedure then you do not have to touch the report definition. For example; take the following query;

  Select first_name as first_name,
           last_name as last_name,
           middle_name as middle_name,
           home_phone_num as phone_num
    From  Employee
    Order by last_name

Now build a report off of this definition. Then client comes and says that they want to use the business phone number instead. If you did this in the designer you would have to open it up and make the changes everywhere it says home_phone_num. However; if you build a “middle” layer then all you do is change the stored procedure like so…..

    Select first_name as first_name,
           last_name as last_name,
           middle_name as middle_name,
           Business_phone_num as phone_num
    From  Employee
    Order by last_name

2. You allow SQL to manage the where and order by requirements which will always be more efficient than doing it in the reporting platform.
3. If you have a several linked tables than doing it in a stored Procedure will allow you to debug it and reuse the data when needed. (Instead of having to build a report to see the data)

I hope that I have convinced you to consider using a “middle” layer (Stored Procedure) for all of your reporting needs. If you think of other benefits than send them to me and I will add them to the list.

Looking for quality web hosting? Look no further than Arvixe Web Hosting!

Tags: , , , , , , , , , | Posted under DotNet/Windows Hosting, Programming/Coding | RSS 2.0

Author Spotlight

David Bauernschmidt

David Bauernschmidt

I live in the historical triangle of Virginia where I am married with two daughters. I have spent over 13 years working for a Fortune 500 company in the computer area. I started in VB 6.0 and by the time I ended my employment I was supervising a development team where we built many web applications. When my first daughter was born I wanted to spend more time with her so I left and became a programmer analyst for local government as well as launch my own company. Since then I have grown James River Webs into a profitable web design and application company helping small businesses create a big presence on the internet. As an employee I have created web application used by citizens and other companies. I enjoy fly fishing, and spending time with my family. I also enjoy learning new approaches and development tools when it comes to developing applications.

Leave a Reply

Your email address will not be published. Required fields are marked *


− 7 = 2

You may use these HTML tags and attributes: <a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>