Community
Showing results for 
Search instead for 
Do you mean 
Reply

Tables available to ACT! OLE DB Provider for Reporting

New Member
Posts: 4
Country: Australia

Tables available to ACT! OLE DB Provider for Reporting

[ Edited ]

Hi all,

 

I was wondering what defines which SQL tables are available through the OLE DB provider?

 

I expect it's a bit of an odd question so a bit of background - I'm mucking about with Act! on a test database, fiddling with custom dashboards, Excel data connections, and SQL. I'd been having trouble getting some of the more specific results I was after - couldn't get stuff like SELECT TOP(1) column_name etc to work in the XML, so I figured I'd try making a new database view and just accessing that.

 

I noted that the tables available through the OLE DB provider in Excel were a bit limited, though, and new views don't show up. So, possibly a SQL newbie question, but can you expand the list of tables available to the ACT! OLE DB Provider for Reporting 2.0?

 

I wasn't sure which subforum this question belonged to the most.

 

Edit: Might also add that if anyone knows a consultant who works with this, I wouldn't mind a link.

New Member
Posts: 4
Country: Australia

Re: Tables available to ACT! OLE DB Provider for Reporting

Does anyone know an expert who might know more about this?
Platinum Elite Contributor
Posts: 6,653
Country: USA

Re: Tables available to ACT! OLE DB Provider for Reporting

All the tables are available uysing the OLEDB Provider. The provider makes the logical connections to the various individual tables that make up the, for example, contact table allowing you to simply reference the one table rather than the individual tables and make the necessary connections yourself.

Roy Laudenslager
ACT! Certified Consultant
ACT! Report Expert
Durkin Impact Report Designer
www.techbenders.com
royel@techbenders.com
541-343-8129
New Member
Posts: 4
Country: Australia

Re: Tables available to ACT! OLE DB Provider for Reporting

Thanks for your reply.

 

You say that all the tables are available - what about views?

 

Due to lack of success with subqueries and logic such as SELECT TOP(1) when writing queries in Act.Dashboard.DataChart.xml, I tried making a database view.

 

I can utilize this database view easily in Excel by connecting an SQL server data source and authenticating using ACTREADER.

 

However I can't even see this view if I connect using OLE DB 2.0, and attempting to SELECT from the new view in Act.Dashboard.DataChart.xml causes the query in question to fail resulting in an empty dashboard section.

 

What this means is that I can current provide more complex and comprehensive data to users of Excel than I can to users of the actual Act program!

 

Ideally I would like to be able to select from the new view I have created in SQL Server Management Studio via OLE DB 2.0. Is this possible, or am I limited to the views created by Act?