I have an Automation script that uses two variables (Month and Year). I use a Set Variable to create them and a Set Variable Value to establish them. The script then runs a series of scripts that use those variables to create and distribute month end reports and Excel exports.
Opening up the Automation script and editing the Set Variable Value each time is really not too painful, however I am wondering if there is a way to collect those variables much like the : convention used in SQL scripts and the Query Builder.
This question is really about other user run Automation Scripts that would benefit from this.
I suggest you’re assigning new values to those two variables (#month# and #year#) and then run the series of scripts using those two particular values. If this is the case then why not to add series of values for those two variables somwhere (say, Excel spreadsheet) and then loop through this spreadsheet using Loop Dataset activity? E.g., such spreadsheet might contain those values:
Well, not really. What I would like to do is have the Set Variable Value somehow interactively prompt for the values on #Month# and #Year#, much like using the Where convention found in the Query Builder where you assign the value as something like '=:Year" and ‘=:Month’ . When you execute, a window like the following pops. That would be ideal.
How do you suggest it should be working if Automation script is scheduled? Any popup dialog obviously freezes script execution so we’re making specific efforts to get rid of situations like this.
Yes, this is true. The idea would be to run the Automation script interactively.
I was really just curious if the functionality I described was in any way available. I am aware that Automation scripts, by their very nature, are designed to be run as scheduled processes.
If the answer on the interactively prompted variables is no, I can accept that. No sense in burning any more time on it.
Hi, I saw this discussion and am running into the same issue with getting variable prompts to work on SQL through automation. My idea would be to allow users with TDP to run the automation and hopefully have them prompt the values they need into the queries, without specifically updating the SQL to the query. Essentially can variable prompts be set up similar to how they are used in the query editor in automation? Thanks!
Bind variable prompts are support for Query Builder, SQL Editor, Data Reports, Pivot Grid and Export Wizard. The purpose of an automation script is to be run without a user. Can you provide more detail about your usecase of what an end user would need to use an automation script and be prompted for bind values?
Sure thing Debbie. Those of us who write SQL queries often have to give our query/report processes over to others who don’t necessarily know how to code SQL. With that being said, the use of variable prompts can be important for them to run queries, but change criteria on them without having to actually update each SQL file for them. A good scenario is say about 5 different queries that need to run and export reports for a process. Instead of them going to each of them individually, even using prompts, but running and exporting them in a specific format, they can use the automation task which will have the queries pre-stored, and the export specifications already set. My idea in a specific case is all the reports go to tables in an access database for further reconciliation against large data sets. With that being said, this prompt in the automation will allow them to accomplish many reports and automatic exports while only hitting the run button of the automation and entering the variable prompts and the reports generate as needed automatically, allowing in increase efficiency, plus allowing users who have little to no experience to run the same things we run on a day to day basis or for backup purposes. I saw another post from another user, who I believe was trying to accomplish the same thing with his company with other employees with less query knowledge in mind.
Lastly, I will say, that the workaround at this point is to have the variable set action at the beginning of the automation task and basically instruct the users to input each variable on that variable action before running the automation. This is fairly basic and will suffice for now, but if prompted, it could surely make it just a tad bit easier, especially for users with no toad data point experience at all.
What is your end user using to request the run of the automation script? Are you saying he has Toad Data Point installed and he runs the script from TDP? Or are you wanting to publish the automation script to Toad Intelligence Central and be able to enter bind values and run the script from the Web Portal? The latter seems the most elegant and useful.
I need to have "Less Technical: staff run automation that controls the ins and outs but they have to be able to control the bind variables for the date search on the query.
Is this on the list to be done in 4.1 and is there an estimated timeline?
I have some good news. The latest Beta (Toad Data Point Beta 4.2.0.659) has this new feature. Any time you define a variable or email activity you will see a check box labeled “Prompt?”. If you check this the user will be prompted for the values of the variable and email prior to running. Whatever variable values they enter will be used instead of what is in the file. We had expanded this feature to include this type of support when published to Toad Intelligence Central and end users running automation scripts from Web Portal. Enjoy[;)]
I have some good news. The latest Beta (Toad Data Point Beta 4.2.0.659) has this new feature. Any time you define a variable or email activity you will see a check box labeled “Prompt?”. If you check this the user will be prompted for the values of the variable and email prior to running. Whatever variable values they enter will be used instead of what is in the file. We had expanded this feature to include this type of support when published to Toad Intelligence Central and end users running automation scripts from Web Portal. Enjoy[;)]
Hi there,
Can anyone provide and example of how to configure the PROMPT discussed above now available; with syntax and whatever else is required for Toad to cut up data by date ranges?
Thanks
