Community
Showing results for 
Search instead for 
Do you mean 
Reply

Still having issues with OLE DB, unique idendifiers don’t show up in Excel (see pics)

Copper Contributor
Posts: 20
Country: United States

Still having issues with OLE DB, unique idendifiers don’t show up in Excel (see pics)

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

OLEDB pic3.jpg

 

 

 

OLEDB pic2.jpg

 

 

 

OLEDB pic1.jpg

 

 

 

Bronze Elite Contributor
Posts: 2,115
Country: United_Kingdom

Re: Still having issues with OLE DB, unique idendifiers don’t show up in Excel (see pics)

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?

Vivek Gargav
Caldere Associates Ltd.
www.caldere.com
vgargav@caldere.com
My Blog
Bronze Elite Contributor
Posts: 2,115
Country: United_Kingdom

Re: Still having issues with OLE DB, unique idendifiers don’t show up in Excel (see pics)

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/

Vivek Gargav
Caldere Associates Ltd.
www.caldere.com
vgargav@caldere.com
My Blog
Employee
Posts: 1,163
Country: USA

Re: Still having issues with OLE DB, unique idendifiers don’t show up in Excel (see pics)

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.

 

http://stackoverflow.com/questions/7694926/excel-oledb-data-types

Matthew Wood
Act! SDK Support
Community Moderator
Bronze Super Contributor
Posts: 1,231
Country: USA

Re: Still having issues with OLE DB, unique idendifiers don’t show up in Excel (see pics)

I don't know Excel but to convert a GUID to a nvarchar you would use this statement in the ACTOLEDB.2 reader.

 

SELECT  CONVERT(nvarchar(36), CONTACTID) AS strCONTACTID  FROM  CONTACT

 

-- Jim Durkin

Copper Contributor
Posts: 20
Country: United States

Re: Still having issues with OLE DB, unique idendifiers don’t show up in Excel (see pics)

Thanks Jim,

 

I asked that question on a couple SQL fourms and no one could give me such a simple answer.