[CLOSED] drill downs off of temp tables or files

Hello,

Not sure I know how to do this or not. I currently have a report with three drill downs that originate from an sql server proc. Instead of re-running that proc each time when I drill down, I would lile to drill down off of a temp file or table of some sort. I saw some examples of using file def but I'm not quite sure how to code this out starting with the first proc and linking to second and third. Any examples out there I could model after.

Here is my original code for first proc with no file def:

 -* File divnew.fex
ENGINE SQLMSS SET DEFAULT_CONNECTION IVB
SQL SQLMSS
EX ORBIT.dbo.adherev2 '&dte','&edte'
;
TABLE FILE SQLOUT

SUM
     SCHED
     COMP
BY EmpDivisionName
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS DIVAD
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
     RIGHTGAP=0.125000,
$
TYPE=TITLE,
     STYLE=BOLD,
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=TABFOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=HEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=FOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=SUBHEAD,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBFOOT,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBTOTAL,
     BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
     SIZE=9,
$
TYPE=ACROSSTITLE,
     STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     BACKCOLOR=RGB(210 210 210),
     STYLE=BOLD,
$
ENDSTYLE
END
DEFINE FILE DIVAD
 SCHED1/D14.2=SCHED;
 COMP1/D14.2=COMP;
 END

TABLE FILE DIVAD
PRINT
     COMPUTE COMPLI/D8.2% = IF COMP EQ 0 THEN 0 ELSE( COMP1 / SCHED1 ) * 100; AS 'Adherence %'

BY EmpDivisionName AS 'Division,'
HEADING
"Center Adherence Report"
"Between <+0>&dte<+0> and <+0>&edte"
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE SUMMARIZE COMPLI AS 'TOTAL'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     FONT='ARIAL',
     SIZE=9,
     RIGHTGAP=0.125000,
$
TYPE=DATA,
     BORDER-TOP=LIGHT,
     BORDER-BOTTOM=LIGHT,
     BORDER-LEFT=LIGHT,
     BORDER-RIGHT=LIGHT,
$
TYPE=DATA,
     COLUMN=N1,
     BACKCOLOR=RGB(153 204 255),
     FOCEXEC=groupnew2(DIV=N1 dte=&dte.QUOTEDSTRING edte=&edte.QUOTEDSTRING),
$
TYPE=DATA,
     COLUMN=N2,
     JUSTIFY=CENTER,
$
TYPE=TITLE,
     BORDER-TOP=LIGHT,
     BORDER-BOTTOM=LIGHT,
     BORDER-LEFT=LIGHT,
     BORDER-RIGHT=LIGHT,
     BORDER-TOP-COLOR='BLUE',
     BORDER-BOTTOM-COLOR='BLUE',
     BORDER-LEFT-COLOR='BLUE',
     BORDER-RIGHT-COLOR='BLUE',
     BACKCOLOR=RGB(255 102 0),
     STYLE=BOLD,
$
TYPE=TITLE,
     COLUMN=N1,
     JUSTIFY=LEFT,
$
TYPE=TITLE,
     COLUMN=N2,
     JUSTIFY=CENTER,
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=TABFOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=HEADING,
     SIZE=12,
     COLOR='WHITE',
     BACKCOLOR='BLUE',
     STYLE=BOLD,
$
TYPE=HEADING,
     LINE=1,
     OBJECT=TEXT,
     ITEM=1,
     FONT='VERDANA',
$
TYPE=HEADING,
     LINE=2,
     OBJECT=TEXT,
     ITEM=1,
     FONT='VERDANA',
     SIZE=8,
$
TYPE=HEADING,
     LINE=2,
     OBJECT=TEXT,
     ITEM=2,
     FONT='VERDANA',
     SIZE=9,
$
TYPE=HEADING,
     LINE=2,
     OBJECT=TEXT,
     ITEM=3,
     FONT='VERDANA',
     SIZE=9,
$
TYPE=HEADING,
     LINE=2,
     OBJECT=TEXT,
     ITEM=4,
     FONT='VERDANA',
     SIZE=9,
$
TYPE=HEADING,
     LINE=2,
     OBJECT=TEXT,
     ITEM=5,
     FONT='VERDANA',
     SIZE=9,
$
TYPE=HEADING,
     LINE=2,
     OBJECT=TEXT,
     ITEM=6,
     FONT='CALIBRI',
     SIZE=9,
$
TYPE=FOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=SUBHEAD,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBFOOT,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBTOTAL,
     BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
     SIZE=9,
$
TYPE=ACROSSTITLE,
     STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     COLOR='WHITE',
     BACKCOLOR=RGB(117 117 117),
     STYLE=BOLD,
     JUSTIFY=CENTER,
$
ENDSTYLE
END

Thanks for any help possible!

Joe

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

WebFocus 7.7.02 WinXP

Posts: 236 | Registered: May 12, 2006

Francis Mariani

Expert

posted

Hide Post

Joe,

Look into "FOCCACHE" in the documentation. There's very little on this subject in the Developer Studio Online Help, but other documentation may have more.

quote:
Two applications are provided during installation: a default application called baseapp and an application containing sample files called ibisamp. In addition, when you connect to the server a temporary directory called foccache is added as the first directory in the search path. When you want to be able to reuse data within the same browser session, you can store the data in the form of a HOLD, SAVE, or SAVEB file in the foccache directory. As long as the browser session remains active, the files stored in the foccache directory can be referenced in requests.

The applications available for inclusion in the search path are identified by name, type, and physical location. With the exception of the baseapp application, which is always part of the search path, (and the foccache directory, which is automaticallly added to the search path when you connect to the server), you must explicitly add applications to your search path.

You are also likely to find discussions on FOCCACHE in the forum: FocalPoint Google Search - FOCCACHE

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Show Post

JOE

Platinum Member

posted

Hide Post

Is FOCCACHE the only way. I do not have active reports or MRE. Just regular old webfocus 7.6.1

WebFocus 7.7.02 WinXP

Posts: 236 | Registered: May 12, 2006

Ignored post by JOE posted

Show Post

Spence

Platinum Member

posted

Hide Post

In theory you could do this. I’ve never been able to get in to work.
Create the hold file HCAR01 from your stored procedure.
Then the drill down reports will TABLE FILE HCAR01.

Amazing this post is still active after almost two weeks - it's WebFOCUS 001.

Hard to believe this does not work:
APP HOLDMETA BASEAPP
-*APP HOLDDATA BASEAPP

TABLE FILE CAR
SUM SALES
BY COUNTRY
ON TABLE HOLD AS HCAR01 FORMAT ALPHA
END
(HOLDMETA for metadata, HOLDDATA for data)

WF 8 version 8.2.04. Windows.
In focus since 1990.

Posts: 189 | Location: pgh pa | Registered: October 06, 2004

Ignored post by Spence posted

Show Post

Francis Mariani

Expert

posted

Hide Post

Joe,

It's available for regular WebFOCUS, but unfortunately it was introduced in v7.6.5.

FOCCACHE isn't the only way to do it.

Here's an example - a pair of fexes, one that creates the hold file and a report with hyperlinks, the other is the drilldown. This example puts the HOLD file and MASTER in Application folder BASEAPP. You have to be aware that this isn't necessarily a good idea - if two people run the same report at the same time, one might clobber the files of the other. A more elaborate scheme must be designed if you want a robust version of this - I would create a folder for each user that runs the report, and then use APP commands to PREPEND the user file to the Application Path, but hopefully the example gives you something to start with.

fex fpjoe1:

-SET &ECHO='ALL';

SET PAGE=NOLEAD
-RUN

-DEFAULT &COUNTRY = 'FOCNONE';

APP HOLDMETA baseapp
APP HOLDDATA baseapp
-RUN

TABLE FILE CAR
SUM
SALES
BY COUNTRY
BY CAR
BY MODEL
ON TABLE HOLD AS HCAR001 FORMAT ALPHA
END
-RUN

TABLE FILE HCAR001
SUM
SALES
BY COUNTRY
BY CAR
BY MODEL

ON TABLE SET STYLESHEET *
TYPE=DATA, COLUMN=COUNTRY, FOCEXEC=FPJOE2 (COUNTRY=COUNTRY), $
ENDSTYLE

ON TABLE PCHOLD AS H001 FORMAT HTML
END
-RUN

fex fpjoe2:

-SET &ECHO='ALL';

SET PAGE=NOLEAD
-RUN

-DEFAULT &COUNTRY = 'FOCNONE';

FILEDEF HCAR001 DISK baseapp/hcar001.ftm
-RUN

TABLE FILE HCAR001
SUM

COMPUTE ANCHOR1/A2 = '<a';
COMPUTE ANCHOR2/A4 = '</a>';
SALES
BY COUNTRY
BY CAR
BY MODEL

WHERE COUNTRY EQ '&COUNTRY';

HEADING
"<ANCHOR1 HREF='JavaScript: history.back();'>Back<ANCHOR2"
ON TABLE PCHOLD AS H001 FORMAT HTML
END
-RUN

quote:
When you want to be able to reuse data within the same browser session, you can store
the data in the form of a HOLD, SAVE, or SAVEB file in the foccache directory, which is
automatically created when the connection to the server is established. This becomes the
first directory in the application path.
As long as the browser session remains active, the stored files remain in the foccache
directory and can be referenced in requests using standard two part names. For example,
the first request below creates a HOLD file in foccache, which is referenced by the second
request:
TABLE FILE CAR
PRINT CAR
ON TABLE HOLD AS FOCCACHE/myfile
END
TABLE FILE FOCCACHE/myfile PRINT *
END
94

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Show Post

JOE

Platinum Member

posted

Hide Post

Francis..I could have up to 100 users using this report. Is there some way to save this to the user's C:TEMP ?

WebFocus 7.7.02 WinXP

Posts: 236 | Registered: May 12, 2006

Ignored post by JOE posted

Show Post

Waz

Expert

posted

Hide Post

If this is running on a WebFOCUs server somewhere, then having the file saved on the users c:\temp is not going to happen.

If you have the users Id, then I would suggest holding the data in a file that has the users id in it, to its separate from others.

Waz...

Prod:	WebFOCUS 7.6.10/8.1.04	Upgrade:	WebFOCUS 8.2.07	OS:	Linux	Outputs:	HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006

Ignored post by Waz posted

Show Post

JOE

Platinum Member

posted

Hide Post

Thanks Waz,

These temp files or tables only need to be active while the user's session is open. Is this the only solution (unless I get FOCCAPCHE)?Could you explain more about getting the user ID into the hold data. Are you saying possibly create folders with each user's id?

Thanks

WebFocus 7.7.02 WinXP

Posts: 236 | Registered: May 12, 2006

Ignored post by JOE posted

Show Post

Waz

Expert

posted

Hide Post

A lot of this depends on your environment, Is this being run from MRE, or self service, or something else.

I'm not suggestion to create separate folders, but separate files, in one folder.

Have you tried FOCCACHE ?, I would expect that it does not exist in you version, but sometimes these features do exist, but are not documented or officially released until a later version.

Waz...

Prod:	WebFOCUS 7.6.10/8.1.04	Upgrade:	WebFOCUS 8.2.07	OS:	Linux	Outputs:	HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006

Ignored post by Waz posted

Show Post

Please Wait. Your request is being processed...

Read-Only Topic

Focal Point

Focal Point Forums

WebFOCUS/FOCUS Forum on Focal Point

[CLOSED] drill downs off of temp tables or files

Focal Point | Privacy Statement | Terms of Service