Hi all,

These forums have helped me so much in the past, and I think it's finally time that I cave and ask my own question.

We have a complicated set of compound reports, most of which are broken down to the region/district level of our data. For each of these reports, we loop through and create the reports for each region/district. That's working, I have no problems there.

However, one of the reports is based on ALL regions, so it takes much longer to run.

In the other reports, let's say the region one for simplicity, it loops 9 times, once for each region, and filters "where region eq ®ION". Since that doesn't happen with the ALL regions report, running it the same way feels REALLY inefficient.

It works, of course, but I was wondering if there was a better way to do this?

I do the save as:

ON TABLE HOLD AS &OUTFILE FORMAT PDF

where &OUTFILE is a generated filename based on the region, set in the code itself. (Generates a name that tells a PowerShell script which region to give the file to) The ALL REGIONS report still needs to go out to each region individually, similar to the individual region reports.

So I'm wondering if there's a way to just change the filename of the output and save again without running through the code? Or to save the file some other way?

We have ReportCaster, so I was wondering if that was an option? I haven't done much with it, but these will be scheduled jobs anyway so if that's a potential solution, I'm all ears.

This message has been edited. Last edited by: FP Mod Chuck,

Sarah, Right after your
END

you can do another hold

Example:
HOLD AS DEF FORMAT PDF

Fernando

quote:

you can do another hold

Example:
HOLD AS DEF FORMAT PDF

So I could loop just on that hold?

I'm not too experienced with Compound Reports, so it's a little confusing to me.

Since the ON TABLE HOLD I'm currently using is after the first report, and there are two more reports in the compound report.

Where do you think I should put your suggested line so that it loops correctly?

Hi Sarah,

You could also do the following:
Query your all region first and place it into a hold file (formatted focus). Then a second hold format pdf.

The first hold could be used for your region specific queries.

This way you query your db once.

Fernando

Fernando,

Thank you so much for your reply.

I think, if I understand your suggestion correctly, it's what we're already doing (mostly).

Our first step in any of these programs is to find the regions by pulling "BY REGION" from the data source and doing a HOLD of that in format ALPHA.
The next step is to CNT the REGIONs from the first file, which we then read into a variable to act as the "Max" number in our loop.

-REPEAT REGIONPROC WHILE & REGNUM LT & REGMAX;

(had to edit this to put a space because it was turning my variables into symbols! Our code is proper)

Where REGIONPROC is a flag at the end of the compound report, REGNUM is the "current region" (incremented at the end of each loop), and REGMAX is the number previously explained.

Or do you mean making a full hold file outside of the loop with just the info I need, and then using THAT as the "TABLE FILE" instead of the actual synonym?

Sarah, Yes I do mean making the full hold file outside of the loop with just the info that you need, and then using it as the "TABLE FILE" instead of the actual synonym.

Fernando