07-29-2012 04:57 AM
I’m trying to use Power Pivot in Excel 2010 to create a report that summarizes Product sales by Opportunity and Customers but the GUID fields that link CONTACT, OPPORTUNITY and PRODUCT aren’t showing up in Excel using ACT! OLE DB 2.0
These GUIDs (CONTACTID, OPPORTUNITYID, PRODUCTID) are need to link the three views.
A query of dbo.CUSTOMER_OPPORTUNITY in SSMS shows data in the View (top) but attempts to import into Excel show no usable columns for the same(bottom).
Rebuilt OLE DB objects using actdiag; repaired and reindexed db. Runing 2012 Premium SP1 hotfix 4 on Win 2008 R2 Server (virtual machine).
What am I doing wrong?
dbo.CUSTOMER_OPPORTUNITY in SSMS
07-29-2012 02:52 PM
I know that there is something weird that goes on if you open a OLEDB view directly into Excel, you get the issue you are facing, however if you create the same view table via SQL in connection properties rather than table the CONTACTID field is visible.
I'm not sure why you are having the problems you are descrigbing unfortunately since I cannot replicate it on any of my systems! Can you open up the views from the ACT2012Demo?
07-30-2012 12:50 PM
Pat Egan has a wonderful blog where she details how to connect to the OLEDB and create simple std pivot charts: http://pregen.wordpress.com/2012/07/05/kicking-up-business-intelligence-data-in-sage-act-2012/
08-02-2012 08:30 AM
Did a bit of research on this and the issue appears (based on very limited sources and my own anecdotal experiments) to be with excel and it's inability to display data of type GUID. In order to display the ID via an oledb connection, the datatype would have to be converted to VARCHAR.
08-02-2012 06:06 PM