Hello - I have two questions.
First off, unless I’m not seeing things right, when automating a Toad Report, the exporting options are limited to PDF, RTF and on other I don’t recognize. If I automate a query, it allows for .xls export. Am I missing something??
Second, is it possible to use a Bind Variable within a query that is being automated?
Thanks!
You are correct, the Automation activity that refreshed a Toad Data Report does not offer to export to Excel. I did not think this was useful. I can add as an enhancement if you like. Perhaps you can let me know why you would want this format.
My reasoning for leaving it out was that pdf, rtf, and html are the most common file formats for sending to end users. The mht is a single web page file.
Debbie
I entered your request for Export to Excel from Toad Data Report as Cr69,872. I’m not sure if I can get this into the next release, but I will try.
I forgot to answer your second question regarding bind variables. Yes, you can use bindvariables with Toad Reports. It takes two steps.
Add a ‘Set Variable’ to your activity. Give it a name and initial value. There are only strings and number varaible types. So initialize the value as a string such as ‘EMPTY’ or 0 if a number. IE: Variable name = ‘A’. Value is ‘SMITH’
Use a bind variable in your SQL with the same name and automation will bind it to the value.
IE: select * from atable where name = :A;
If you need to set the value of a variable from a driving query you would need to use the Loop dataset activity. There are some examples of this available in other postings. Let me know if you need me to locate them.
Debbie
Thank you for the quick response. The reason for wanting the .xls format is our end users need to work the data once they get it. With PDF’s, generally it’s harder for them to create calculations / groups and totals.
If you could add that as an enhancement, that would be excellent! How would we then update our Toad install with that change?
Would that enhancement effect the use of a Loop Dataset Activity? I ran into the same issue when automating mulitple exports using a Loop Dataset.
Thanks again!
Once the enhancement is in the build you would only need to change the export type in the Toad Data Report activity.
If you need to automate exporting data to excel for now you would need to use the Select To File activity.
What issues did you run into when using the loop dataset activity? I can help debug if you have the time to go into details.
Debbie
The issue with the Loop Dataset is when I hit "TEST", it goes through the database connection and setting the variable, then it just stops at "Begin Loop Dataset". It doesn't create any output files. No other messeges come up.
I've attached a screenshot of where the process ends.
Thanks,
Tom
Unfortunately that is not enough data for me to debug as I cannot see all of your script and the settings of each activity.
The loop dataset must be preceded by an Execute Script activity that names the result set variable. You can also set the row count variable here to confirm there are rows in this result set. Then the loop data activity needs to be set to that result set variable. Then for each row you need to deference the column to set the variable.
Take a look at this posting. There is a screen shot and steps on this.
http://tda.inside.quest.com/thread.jspa?messageID=83341�
If it doesn’t seem obvious what is occurring, then zip up all of your files you are using, (automation script and dependant query files, etc ) and send to me at dpeabody@quest.com. I won’t be able to execute your queries but I will be able to see all the attributes of the scripts.
Debbie
I have reviewed your automation script. Please see attached word file and make the suggested changes. Let me know how this changes things.
Debbie
P.S. I was unable to email directly and is kicked back all of my emails.
I have a couple of corrections to your automation script.doc (96 KB)
Thanks! I’ll make those changes and see how it works. I really appreciate how fast you’re able to get these answers back to me. It’s very helpful in keeping the progress moving forward.
Tom
My pleasure:)
Okay. It turns out that I have a bug in my code. If there are no rows in the table used by the Loop Dataset activity, the automation script hangs.
I have entered this as CR69908 and will fix ASAP. It won’t make this weeks Beta but will be in the one after this.
Sorry for the inconvenience but thanks for bringing this to my attention.
Debbie
This CR has been fixed and will be in the 1/18/10 2.6 Beta build. (It missed today’s Beta). I handled the abnormal ending when no rows were retrieved in the Execute SQL activity. I also added logging the number of rows to make it easier to debug.
Debbie
Thanks!
I do not see the CR69872 (excel export from reports in automation) in the newest beta version release notes - do you know when this will be available? Thanks, Christy
It is scheduled for the TDA 3.0 release and I do not have any set dates for that release. (Sorry, I asked if I could include it but it got deferred to the next release).
I could add to the next code base and it would come out in the first Beta. Would you be able to use a Beta build or not?
Debbie
Yes, I would use a Beta build. Thanks, Christy
Cr 69,872 has been coded and will be in the next Beta. In this CR I added exporting Toad Data Reports in Excel and CSV formats for Automation.
Debbie
