Over the past few weeks I have had several requests to design some unique reports and in each of these cases using SQL Views was the selected solution.  Before I explain when you might want to use a SQL View I should first explain what they are.

As the name implies a SQL View is just a way to view (or display) your SQL data.  What is special about a view is that one view can contain data from several tables, different databases as well as including summary information.  Another nice feature of Views is they are objects in your SQL database, which means you can control who has rights to see that information.

We all know that the Sales Order information is held in a couple of different tables.  The header information is in the SOFOM table and the details are in the SOFOD table. What if you wanted to display a summary listing of Sales Order data including the sum of the Sales Order?  Using the built in tables you would have to group the records by the SO_ID and sum up the Sales Order Detail information.  But with a SQL view you can quickly provide this information in a Crystal Report or within an external application such as SalesLogix. 

Now inside my Crystal report I can use this View in place of the SOFOM and SOFOD table.  I am still able to use the Report Selection Criteria to only show Open Sales Orders, or whatever other criteria that I choose.

Another situation I ran into was that I needed to link together data from two different database’s for a report.  If you have ever tried to link two different data sources in a Crystal Report you know that this can get to be a messy proposition.  My solution was to create a view that linked the data together and then use that view in my report.  What I needed to join was our CRM solution (SalesLogix) with our ERP solution (Expandable).  The CRM system had the marketing segment data that we use to identify if a customer is a Medical office, Spa / Salon, or Other Medical site.  We needed to use this information joined with our sales data inside of Expandable.