I'm looking for a way to create a HOLD file that reports, located within the individual panels can access. It appears to me that each panel is creating a separate session to the DBMS (MSSQL). Is this a possibility?

This message has been edited. Last edited by: Don Garland,

Adding a bit more context as I continue to experiment with this idea.

I've been playing with SAME_DB as a solution to creating a HOLD file in one FEX and using it in another. Running the 1ST FEX, I can see the temdb table being created on the SQL Server, and according the FM, the HOLD file should persist as long as the original FEX session is active or another is referencing it. " .... table is dropped automatically when the session that created the table ends and all other tasks have stopped referencing it. The .....

Here is an example to support my questions below;

1ST FEX

-* This FEX creates the HOLD file and runs a report to return the TOP 1 Data
EXAMPLE - Initial FEX to create the HOLD on the DBMS
TABLE FILE DBNAME
PRINT FIELD1
      FIELD2
BY    SORTFIELD
ON TABLE HOLD FORMAT SAME_DB PERSISTENCE GLOBAL_TEMPORARY
END
-RUN


TABLE FILE ????
PRINT *
BY HIGEST 1 SORTFIELD
END
-RUN
-EXIT

2ND FEX

-* This FEX uses the HOLD file and runs a report 
TABLE FILE ????
PRINT *
BY LOWEST 1 SORTFIELD
END
-RUN
-EXIT

Here is what I think I know for sure, based on the manual;
1. The manual indicates that there is a window open by the 1ST FEX, and as long as that FEX (or any other FEX accessing that file) is in session with the DBMS, the HOLD file is available.

Here is what is not clear to me;
1. What is the proper way to access the HOLD (????) FILE file;
A.Within the 1ST fex (which no doubt is in session) and B. the 2ND FEX, which normally is a new session. But should keep the file open if it can get it's hands on it.

Any thoughts or questions on this scenario would be greatly appreciated.

Thank you,

This message has been edited. Last edited by: Don Garland,

Hi

You could use FOCCACHE and also Format FOCUS (if the file contains for example couple of tens of thousands of records)
TABLE FILE CAR
SUM SALES RCOST
BY COUNTRY
BY CAR
ON TABLE HOLD AS FOCCACHE/DAT1 FORMAT FOCUS
END

and then in subsequent requests in different panels/pages you could create reports/graphs based on the FOCCACHE file
TABLE FILE FOCCACHE/DAT1
SUM SALES
BY COUNTRY
END

Foccache will create for each user session per IP address a dedicated caching folder/files
If the user closes hos browser than the foccache file will not be available
You can also control the time the foccache file is available within a session, so if the user doesn't close his browser for 4 hours you can define that the foccache file will be available for maximum of 2 hours within a session, for example

I played with this scenario but ultimately decided not to hold on the report server for this.

Instead, I created an indexed table on the DBMS. A scheduled, stored procedure refreshes this table, early morning. This one trip to the DB server, covers the data needs of the parameters and dop-downs for the initial Dashboard page and cut load time more than half.

Although I didn't use it, it was helpful to go through the exercise of researching the use of FOCCACHE and looking at the setting.

Thank you

Indeed, if you have a complex dashboard - the data does not change on the fly (or even it does, I've had a FOCUS table refresh every 30 seconds), not only the load time, but the stress and congestion on the database is way less. I bet on its peaking when all the workers fire up their computers in the morning... now you'll get complaints they're missing their coffee break as its too fast...