Refresh error

[Petzl]

Just learning about PowerPivot and how cool it is. Single user setup. SQL Database on my PC. Running a connection to Other sources and using Other (OLEDB/ODBC). I connect to my ACT! Database using the ACT! OLE DB provider. Data all works fine. I then add a record to my ACT! database and refresh the Powerpivot and spreadsheet. On the Powerpivot refresh I get a bunch of errors in the related fields I have setup. The fix for me is to go into the 'Manage Relationships' area, click to edit a relationship, change nothing, click OK and the report is refreshed correctly!!!! I should clarify that the error shows as a 'Calculation error'.

What am I doing wrong?

 

[powerpivotpro]

I *think* I have seen this once before myself. Do you have relationships established via calculated columns? Meaning, calculated columns (one or more) are the columns that link two tables together.

 

[Petzl]

Thanks for the response Rob. I really appreciate the help.

I have 3 tables typically. I have Opportunities, OpportunitiesProductService and ProductService. OpportunitiesProductService is the linking table between the two tables. Relationships created and that works fine.

I then added other tables and manually created links. This is where I seem to run into the issues I posted. I would add a Stage table, create the link and then on the OpportunitiesProductService table I would create a calculated column that related the Stage table back to one of the initial 3 tables.

Hope that makes sense? Is this something that cannot be done or should be done a different way?

 

[powerpivotpro]

No, what you are doing is completely fine. It sounds to me like a bug in the relationship code. Is the workbook sensitive or is it something you can share with me? I'd like to get it to my former colleagues back at Microsoft.

 

[Petzl]

Hi Rob,

No...not sensitive data at all. Just testing on an ACT!2012 demo database hoping to be able to replicate onsite.

Do you want it by your email at the bottom of your signature?

 

[powerpivotpro]

Yes please.

 

[Petzl]

Thanks Rob...on it's way. Let me know if there is a problem receiving it.