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.
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