Automation and Linked Query

I have been using the 2.6 Beta and really love all of the functionality and would love to use it rather than an Access Database for Automation.There is one thing that I am having an issue with though that is holding up the transition.

I have for example an excel file with multiple pivot tables on separate sheets all done thorough linked queries to an oracle database. After reading everything and trying out different methods I cannot find anyway for the automatic in TDA to first run SQL to look for the latest DDL of my table, then if DDL date is greater than SYSDATE - 1 open up excel and refresh the linked queries, close excel and ideally save the file.

I know this gets into VBA territory but really this functionality would be key and could completely remove Access from running all of this VBA to open excel, save report, etc. It would be huge if it where possible for the automater to do the above and come back and upload the file to a FTP.

Maybe I am missing something but with the current beta I do not see that functionality to refresh the linked queries or check to see the table DDL and then continue with the script.

I would first run a query that returned a row if needing to update. Use the Execute script activity and set the row count variable.

Then add an if…else activity based on the value of the row count var.

I would not use a linked query to excel, because as you stated you have to open to refresh. Instead, I would use the TDA Pivot Grid and corresponding activity. This does refresh through automation.

FTP should work as expected. Experiment with these ideas and let me know how it goes. I think your use case is a very good one and I would like to work out how you can do this.


Thank you so much for your fast response. I will try out that query to pull the row variable.

I have not tried using the TDA Pivot Grid as of yet…my directs for whom these reports are used love the function in excel to “drill-down” from pivot tables just by clicking on the value. Is this possible with the TDA Pivot Grids?

Yes. When you click on a row in TDA it will display the underlying data. However, this would not happen in exported pdf of the pivot grid. I see your dilemma on this if the end user wants to drill into the info.

On the one hand our pivot allows refresh where Excel Pivot is not doing this. And on the other hand the end user can’t manipulate data. Mnnnn…I’d have to think about that one. I know there is an outstanding enhancement for refreshing Excel Pivot but I am not sure where that stands as it doesn’t look that Excel supports sending pivoted info. Perhaps your end users want to use TDA to inspect data???

I will enter this use case in our change requests and give it some thought for the next release. I am not sure what the resolution will be. CR71,392. Please post any updates on this.