10-18-2010 09:17 PM
I need to query the contact, history and opportunity tables to produce reports and charts. I was hoping to use Access and Excel but the way I have done this in the past is through ODBC. I would create a DSN but for that I need un/pw. I am not familiar using OLEDB with Access and Excel.
I was able to bring contact records into excel but contact_history gives me no records. I do this using OLEDB.
Furthermore, even if it did it is not helpful as I need a way to join the tables.
Crystal reports should work but there I need odbc since the OLE DB(ADO) provider ACT! OLE DV Provider for Reporting 2.0 simply does not connect.
So, that is where I am - stuck... I've tried some third party tools and obviously they contec to ACT somehow but I can't find what I need thru third parties.
10-18-2010 09:29 PM
When you use Excel and setup the connection to external data using OLEDB (or ODBC) you select a table, but you can change this option after setting up the connection to identify a SQL statement to join multiple tables. This is the most direct way to get the data you want from ACT! into Excel. I recommend using OLEDB2 as it's much easier to write the SQL you need, though you can do the same in the OLEDB provider as well.
If using OLEDB2 the SQL will look something like
Select * from contact c1 inner join history on contact_history ch on c1.contactid=ch.contactid inner join history h1 on h1.historyid=ch.historyid where c1.contact like 'something here to filter the contacts'
10-19-2010 08:35 AM
Once you setup the connection go to connection properties in Excel then change the command type and command text as per attached image.
11-21-2010 12:30 PM
I heard a rumor that the Dashboards in 2011 would be "clickable" - I see that the normal ones are but not ones built with the SQL chart control. Is there a new control for 2011? Will there bill clickable links with the chart control?
11-22-2010 08:46 PM
Hi Allen , forgive me to jump in here. As I was search thru . I notice that you are very familiar on this topic. May I ask , if I have 2008 ver of act can I use oledb2 to access the activity info ? Or I must upgrade my current ver to 2010 ? As my purpose is to activity info. Hope to heard from you. Paul
11-24-2010 03:22 AM