There are many ways to get your data into a Microsoft Excel spread sheet for reporting purposes. Business Objects Crystal and Sage SalesLogix both offer an option to kick out report data to an Excel spread sheet.  Last week I worked with a client who wanted exactly that, a set of data exported to Excel so the users could manipulate it as they needed.  She came to me under the assumption that this could only be done through Crystal.  Now the benefit for going through Crystal is the parameters established in the report are honored.   But it is possible to export your data from the SalesLogix database directly into Excel and with a little help from SQL Views you are able to filter and organize your data for the export.  The best part is the data is able to be refreshed as often as required in excel and the users do not need to be in SalesLogix to run the report.

The first step in this process was to think about all of the data you would like to see in the final export.  In this instance my client had several version of the sheet she wanted to produce so the first set of views brought all of the data together that could be used in any of the Excel files, we did this without using any filters.  This  allowed her to then customize additional SQL views to support each version of the file she wanted.   Creating the views was really the toughest part, we ended up rewriting our views a couple of times to support additional fields requested after user testing of a sample file.  So I encourage you to really plan things out with your end users before sitting down to write the SQL views, hopefully planning up front will result in less overall work effort.

For our example today we will use a view I created for the Crystal Report writing class.  This view is a Contact based and provides the number of History, Activity, Opportunities, and Tickets related to each Contact in your system.  This view is nothing Fancy but will help prove our point.

Create View sysdba.VContactCounts

SELECT LastName, FirstName,

(SELECT COUNT(HISTORYID) AS Expr1 FROM sysdba.HISTORY WHERE (CONTACTID = c.CONTACTID)) AS HistoryCount,

(SELECT COUNT(ACTIVITYID) AS Expr1 FROM sysdba.ACTIVITY WHERE (CONTACTID = c.CONTACTID)) AS ActivityCount,

(SELECT COUNT(OPPORTUNITYID) AS Expr1 FROM sysdba.OPPORTUNITY_CONTACT WHERE (CONTACTID = c.CONTACTID)) AS OpportunityCount,

(SELECT COUNT(TICKETID) AS Expr1 FROM sysdba.TICKET WHERE (CONTACTID = c.CONTACTID)) AS TicketCount

FROM sysdba.CONTACT AS c

GROUP BY ContactID,LastName, FirstName

Once your view is giving you the data the way you want to see it the easy stuff begins.  I am working with MS Office 2003 so please forgive me MS Office 2007 people.  The client I worked with had Office 2007 so this will work there also,  If I get access to a 2007 machine I will post the steps for you.

The items required to be successful at this are the name of your SQL server, the sysdba password for SQL Server.  The name of your SalesLogix database and of course the name of your SQL view.  

Open a new MS Office Excel file.   

Select the  Data>Import External Data>Import Data menu item

The following screen will appear:

Here we are going to select New SQL Server Connection which brings up the following screen.

This is where you list out the SQL Server Name and sysdba password.  On selection of the "Next" button the following screen appears:

Here we select the database name and then search for the SQL View.  This is a little tricky, the views are listed here first then tables.

You see here the check box for Save Password.  If you do not want the users to have access to this sysdba password then I suggest setting this to true.  They will have access only to the data released via this connection.

On this screen you see the cell where the export will start building the export.  You can click on the appropriate cell in the back ground to reset. Then click Finish and the export should run immediately.  IF there are more then one views providing you the data this process can take a few seconds. Moving forward, all the users has to do is hit the  button, or Data Refresh Menu Item to update the file

Now the data is in excel you can do anything you want with it to include sorting or building formulas or graphs.   

FYI, I played around with other methods of creating the connection to the database but this was the most straight forward. If I discover a better method I will update this post.

Hope this helps someone.  Thanks for reading. Geo