I have to produce a report with a variable filename. This variable that is used as the filename is also used on the where filter.

Example:

TABLE FILE CAR
PRINT
CAR
SEATS
DEALER_COST
RETAIL_COST
BY COUNTRY
WHERE COUNTRY EQ &COUNTRY;
ON TABLE NOTOTAL
ON TABLE SAVE FILENAME
'C:\DATA\REPORT_&COUNTRY.XLS'
FORMAT EXL2K
END
-RUN

I'd like to have the &COUNTRY variable as part of the filename for each individual report that will be generated.


So the results should be:

Report_England.xls - contains all the cars info for England
Report_France.xls - contains all the cars info for France
Report_Italy.xls - contains all the cars info for Italy
and so on ...

Thank you.

This message has been edited. Last edited by: Kerry,

There are many examples of this already in the forum.

e.g. How to send a file to specific folder on my hard drive

Of cause it does help to get the syntax for the table request correct.

The variable must be enclosed in single quotes or it will be interpreted as a verb object
never mind the problem of spaces in the value. (ignore this if the variable comes in as a quoted string)

Also &COUNTRY.XLS will not work you either need .EVAL.XLS or ...XLS

quote:

TABLE FILE CAR
PRINT
CAR
SEATS
DEALER_COST
RETAIL_COST
BY COUNTRY
WHERE COUNTRY EQ '&COUNTRY'
ON TABLE NOTOTAL
ON TABLE SAVE FILENAME
'C:\temp\REPORT_&COUNTRY...XLS'
FORMAT EXL2K
END
-RUN

If you want the file to be saved to the client PC look at this post

[SOLVED]How to change the suggested name of the file to be saved

This message has been edited. Last edited by: <JG>,

Thanks Waz and JG for your inputs.

With a set number of departments, I was able to create a variable name for the file. Basically hardcoding the filter based on the number of departments.

-SET &NBR = 39;
-LOOP01
-SET &NBR = &NBR + 1;
-IF (&NBR GT 52) AND (&NBR LT 97) GOTO LOOP01 ;
-IF &NBR EQ 98 GOTO EXIT2 ;
-SET &FIL = 'FILEDEF PRC_DTLRPT DISK C:\DATA\Price_DEPT_' | &NBR | '.xls' ;
&FIL
-RUN
-* create report for Dept *
TABLE FILE HLDPRCFINAL
PRINT
PLAN_AMT/P12.2C AS 'Eff Price'
BY DEPT_ID AS 'Department'
BY DEPT_DESCR AS ''
BY PROMOTION_ID AS 'Event ID'
BY PROMOTION_DESCR AS ''
BY SKU_ID AS 'SKU'
BY SKU_DESCR AS ''
BY CURR_RETAIL_MAX AS 'Current Retail'
BY START_DATE AS 'Start Date'
BY END_DATE AS 'End Date'
BY PLAN_TYPE AS 'Plan Type'
WHERE DEPT_ID EQ '&NBR';
ON TABLE SET PAGE-NUM OFF
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE HOLD AS PRC_DTLRPT FORMAT EXL2K
END
-* run the report
-RUN
-GOTO LOOP01
-EXIT2

My issue now is that if I don't know the departments on the Hold file, how can I use the distinct departments in the Hold File as the variable for the filename and the filter.

Thanks

Your HOLD AS name can be an amper variable as well.

Joel,

Where does your list of departments come from.

Do a pre table step and save the output then -READ it inside the loop.
The number of lines for the save gives you the iterations for the loop.

Something like this

TABLE FILE HLDPRCFINAL
BY DEPT_ID
-* WHATEVER WHERE test
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE
END
-RUN
-SET &LOOPCNT=&LINES;
-REPEAT LOOP01 &LOOPCNT TIMES
-READ SAVE &NBR.A2.
-SET &FIL = 'FILEDEF PRC_DTLRPT DISK C:\DATA\Price_DEPT_' | &NBR | '.xls' ;
&FIL
-RUN
-* create report for Dept *
TABLE FILE HLDPRCFINAL
PRINT
PLAN_AMT/P12.2C AS 'Eff Price'
BY DEPT_ID AS 'Department'
BY DEPT_DESCR AS ''
BY PROMOTION_ID AS 'Event ID'
BY PROMOTION_DESCR AS ''
BY SKU_ID AS 'SKU'
BY SKU_DESCR AS ''
BY CURR_RETAIL_MAX AS 'Current Retail'
BY START_DATE AS 'Start Date'
BY END_DATE AS 'End Date'
BY PLAN_TYPE AS 'Plan Type'
WHERE DEPT_ID EQ '&NBR';
ON TABLE SET PAGE-NUM OFF
ON TABLE SET BYDISPLAY ON
ON TABLE NOTOTAL
ON TABLE HOLD AS PRC_DTLRPT FORMAT EXL2K
END
-* run the report
-RUN
-LOOP01
-EXIT2

Thanks JG! That worked.

Another question. It looks like it's just reading the first record of the SAVE file when it loops because of the -RUN statement. It looks like the -RUN statement resets the pointer of the SAVE file READ back to the top and just reads the first record.

How can I preserve the pointer so that it would read the next record on ths SAVE file and not reset the pointer. I still need to set my FILEDEF that's why I would need to issue the -RUN statement.

Thanks

You need to add the NOCLOSE option:

-READ filename[,] [NOCLOSE] &name[.format.][,]...

I just took the -RUN command and everything works fine now.

Keep in mind that with removing the -RUN, your TABLE FILE's generated by the loop will be run in one block, if you need to have some sort of checking for errors, then put the -RUN back in and use the NOCLOSE option.