I am hoping to get the assistance on how to optimize what I am doing in WEBFOCUS.

I currently have a master stored procedure that does the following with the input parameters it is given.

1. Runs logic to get a list of people into a temporary table.

2. Runs logic to get a list of accounts into a temporary table.

3. Uses these tables as join tables to much larger tables (transactions, orders, etc.) to generate multiple reports.

Current the result sets are just multiple select statements.

My requirements are as follows.

Create my source Person and Accounts data sources once (I don't want to have to go out to the database and recreate these for each report).

Only retrieve from database the values that are related to source tables (I only want orders and transactions retrieved that relate to Persons and/or Accounts). I don't want WEBFOCUS to pull in the whole table and reduce down on WEBFOCUS side.

From their I am comfortable creating the reporting content I would need.

How I am currently trying to do it.

I just hooked up my stored procedure to a master file. It has my four result sets. However it appears when I report off of each result set it executes the stored procedure each time which is not desired. Maybe there is a way to save all four result sets in a hold file and I'm just not doing it right.

Any thoughts?

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

Create master files for the two result sets instead of the SP. Create a cluster master for the joined structure and write the reports off that. You should be able to schedule the SP to run in batch and leave the two tables in a place you'll find with your reports.