Today's blog is about using SQL commands directly in Excel. I am going after data in Sage ACT, but you can use the same technique to go after a wide variety of data sources that are SQL based.
| First off, open Excel. I am showing you screen shots from Excel 2010. You want to click on the data tab and pick "from other data sources". |  |
| After choosing From other data sources choose From Data Connection wizard which brings up the following dialog box. Choose Other in our case so we can get to ACT data. |  |
| The next screen shows other sources. We want to pick the second one on this shot, which is the ACT OLE provider 2.0. |  |
| Click NEXT and on the next screen enter in the path to your ACT database and put in the username and password. |  |
| Click OK and on the next screen click a particular table. Since we are going to enter a complex SQL string it really doesn't matter which table we choose. |  |
| Click next and on the next screen click finish. |  |
| Now you are presented with a dialog box which asks where do you want to put the data, and if you want a table or report. We will choose Table, but the important thing to note is the Properties box. This is where we will find the way to enter our SQL command. |  |
| After clicking Properties, you will see the following dialog box. You want to click on the second tab called Definition and then on the Command Type box you want to click the dropdown and choose SQL. They really do a great job of hiding this feature. |  |
On the next screen you can now paste in a SQL string. Here's the one I am using today:
Select dbo.CONTACT.PHONE,dbo.contact.state,O.COMPANY, O.OPPORTUNITY_NAME, PRODUCT_SERVICE.NAME, PRODUCT.PRODUCT, CAST(O.CREATE_DATE AS CHAR(11)) AS 'Create Date', CAST(O.TOTAL AS DECIMAL(18,2)) AS 'Total', O.STATUSnum, O.Record_ManagerFrom dbo.OPPORTUNITY O LEFT Join dbo.OPPORTUNITY_PRODUCTSERVICE On O.OPPORTUNITYID = dbo.OPPORTUNITY_PRODUCTSERVICE.OPPORTUNITYID LEFT Join dbo.PRODUCT_SERVICE On dbo.PRODUCT_SERVICE.PRODUCTSERVICEID = dbo.OPPORTUNITY_PRODUCTSERVICE.PRODUCTSERVICEID LEFT Join dbo.PRODUCT On dbo.PRODUCT_SERVICE.PRODUCTID = dbo.PRODUCT.PRODUCTID LEFT Join dbo.CONTACT_OPPORTUNITY On dbo.CONTACT_OPPORTUNITY.OPPORTUNITYID = O.OPPORTUNITYID LEFT Join dbo.CONTACT On dbo.CONTACT.CONTACTID = dbo.CONTACT_OPPORTUNITY.CONTACTID Where O.Statusnum = 0ORDER BY O.Company, O.OPPORTUNITY_NAME
As you can see, it's quite complex and joins together multiple tables. Ah, but it produces a nifty report. Here's what it looks like in Excel.
In our example today, I am going after ACT data, but it works on all kinds of CRM applications or any tables that are SQL based. Have fun creating some cool reports.