Create multiple sheets in 1 workbook 1 query

Toad Data Point & Toad Intelligence Central
1

Hi All,

I have a fairly large and nasty query that has for example employee id in it among other fields. Can I create and report that creates a sheet for each employee id? For example you can build a report that groups by employee id I would just need it to create a new sheet instead of a page break?

Thanks

2

You would need to use a loop dataset in automation. Use a driving query that selected a distinct list of employeed id numbers. Then set that value in a variable. Execute a query that filters on the current id and export that to an excel file.

If you export to the same excel file for each execution it will put the result set into a new worksheet. See my Blog for examples.

Debbie

3

Check out attachement (was created in TDA 2.5)
0) Automation File : dept_emp.tas

  1. Master Query : dept.sql
  2. Child Query : emp.sql
  3. Export Wizard Template : emp_exp_temp.txp
  4. Output Excel file : emp_emp1.xls

I was not able to use Master query dataset value nor a variable value in loop dataset Child query as a parameter.
So use find/replace in the emp.sql file.

It add new sheet for each dept.

hope this helps !

Mr. Madhi M.
toad_analyst_nested_export_sample.zip (7.21 KB)

4

You are very close in having this script work. Please see attached changes. (Note: I had to change to my connection so change them back for your use)

Here is what I did:

  1. You need to have a template of the SQL file so that you can change the value. I added empbak.sql to use as the template. When you do a find and replace it will alwyas find the “xdeptno” and replace with value and write to emp.sql. If you don’t use a template file it will overwrite the “xdeptno” and subsequent loops will not do the proper find and replace.

  2. Use Select to File instead of a Export Wizard template for the export. I like to do this because I added the deptno in the file name to make each export unique. Is that how you wanted it? If not use the same file and it will create a new worksheet.

  3. You don’t need the last fnd and replace as the loop will handle it.

Debbie
dept_emp.tas (9.17 KB)

5

I still like using a bind var in my SQL instead of the find and replace. Attached is an example of this. I am using the attached sql file in the script along with an initializing var activity.

Debbie
dept_empWVar.tas (8.77 KB)

6

I still like using a bind var in my SQL instead of the find and replace. Attached is an example of this. I am using the attached sql file in the script along with an initializing var activity.

Debbie
empWvar.sql (41 Bytes)