Individual PDFs via Toad Data Report

Toad Data Point
1

Hey all.

I am trying to generate individual PDF for each user id via Toad Data Report. To loop through the records, I use the loop activity. Here is how it is setup:

tdr loop.png

I have a bind variable in the SQL in the TDR file equal to user id from Loop_data_1. However, individual PDFs are created, but each PDF contains all records.

Am I missing a step?

2

Update: I learned that my TDR file did not save the SQL update after I added the

WHERE TBL1.TBL1_ID = ‘#Loop_data_1_SQL.TBL1_ID#’

This is very strange. I even did SAVE AS.

So, now my TDR file has this dind variable, but the Automation script prompts with a dialog box to enter the value for ‘#Loop_data_1_SQL.TBL1_ID#’

3

Hi,

It should work if the SQL in Loop_data_1 returns the single column containing user IDs and the SQL in data report uses :BindVarName form of bind var referencing (notice the using of a colon).

To make sure there is no bug in here, reviewing your failed script might be of a great help.

Thanks,

Igor.

4

Loop_data_1 returns 98 records. I want to generate one PDF per record.

Do I have to have Set Variable and/or Set Variable Value activities in the Automation script?

5

I figured this out. Here is what the automation looks like:

8030.Capture.png

  1. In Set_Variable_1: Set variable name - v_id

  2. Insert the full query in Loop_data_1

  3. In Set_Variable_Value_1: set v_id = #Loop_data_1_SQL.TBL1_ID#

  4. In TDR SQL where clause: Where tbl1.tbl1_id = :v_id

6

Glad to hear you’ve solved the problem.

Igor.