I’m using a bind variable in my query to prompt users to enter a date. This works fine in the query, but when I try to automate this in a script it doesn’t prompt anymore. In my query I’m using Where A.YEAR_MONTH = :YYYYMM_Data and I’ve created a variable in the script which is YYYYMM_Data , but there is no prompt.
Is there a way to make the prompt work in a script? I’m not looking to schedule the script I just want the users (who are not SQL users) to have the ability to just hit Run and have the SQL run for them - but they need the date prompt.
Automation is meant to run by the scheduler and not need user input. If you want to automate the report and generate the date at runtime you would add a Variable activity before you execute the SQL. Automation will bind the variable at run time.
IF you don’t want to schedule the report but and want end users to generate the report. I would suggest using either the Query Builder or Toad Data Reports with your query. These both will prompt the user for the value when executing.
Debbie
Thanks for your response. I have created a variable activity in the automation sequence, but it’s not working as a prompt it’s only using a value - will it not work as a prompt?
Is Toad Data Reports a separate product or part of Toad for Data Analysts? I’m using an Oracle db with version 3.1.0.638 of Toad for Data Analysts.
Thanks.
Toad Data Reports is part of Toad, but it sounds like you just need them to run your SQL. Do they have Toad on their desktops? If so, save it as editor (.sql file) and have them open that file in Toad and run it. For an automated job your variable instead of prompting would use a relative date like last week or prior month or yesterday, no user input other than clicking run.
Thanks, that answers my question about the prompt. And, yes, they will have TOAD on their desktop, but I don’t really want them to see the SQL or change it in anyway since they don’t really understand the db structure so I thought I would just put it in a script so they could run all the code at once. There are several pieces of code.
But, I can just have them change the date in the variable in Automation.
Thanks.
If you create a Toad Report you can add this to the project manager. The user simply double clicks on the report. It prompts for the bind var and then generates the report. See screenshot.
Debbie
Thanks, I was able to get this to work. But, I know the users will want to work with this data in Excel. Can the report be exported to Excel? I couldn’t see that as an option.
One more thing on the report. How do you change the format of the fields? I have numbers in my data and they are showing formatted as currency in the report and I want them to remain numbers.
I have a couple of follow up questions based on Debbie’s response to my original question.
In the report preview window you can export to several formats and email. See image.
Debbie
To format a field, go into the edit mode. Select the field. Press the little arrow and it will expand with some options. Find the format option and you will get select of formatting.
Debbie
Thanks for your quick reply. One last question, I hope.
I need the report to be in cross tab format with, for example, Store Num on the Y axis, Date of amout on the X axis and the amount measure for that X Y combination.
Is that possible?
Thanks.
If your SQL does not output the result set in a cross-tab sequence you can add a pivot table to your data report.
There is also a Pivot Grid window that can also be used. To use this, right click on your result set of Query Builder or editor, and send to Pivot Grid. Pivot your fields and save the file. When opening this file it will prompt for bind var value , then execute query and refresh pivot.
Debbie
