Using SQL commands in Excel – Drilling down into ACT data
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.
« Windows 8 question Linkedin | Home | Windows 8 – Question on Linkedin »
Comments
Hello, Patricia, I am trying to connect from Excel 2007, to Sage ACT! Pro 2013 on my LAN. When I test the connection on the Connection tab of the Data Link Properties of the Excel Data Connection Wizard, that is successful, so that’s encouraging. However, I am getting the message “The connection in this workbook will no longer be identical to the connection defined in the external file located at …. The link to that external file will also be removed. Do you want to proceed with the changes to the connection in this workbook?”. To try to fix this connection problem, as suggested by another blogger, I am trying to manually update the ODC file by inserting odc:AlwaysUseConnectionFile just before the odc:Connection line. However, I don’t know how to find the ODC file, and how to update the ODC file if I could find it. This is all a new area to me. I have tried checking “Always attempt to use this file to refresh data” on the ‘Save Data Connection File and Finish’ but I still get the “The connection in this workbook will no longer be identical to the connection defined in the external file located at ….” message. Do you have a solution for the connection problem that you can share with me, please? If you can provide any help I would be grateful. Your SQL screen looks very interesting. I am aiming to run a SQL query against the Opportunities, Product/Services and Contact tables, which is why I want to connect from Excel to Sage ACT in the first place. Thanks again, regards, Eric
Leave a Comment