Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     creating a report with detail and summary,

Read-Only Read-Only Topic
Go
Search
Notify
Tools
creating a report with detail and summary,
 Login/Join
 
Gold member
posted
I created a hold file with the summary data in my first report with input parameters trying to create the second report using the same input table as the first report I get the error message " Error Parsing File WF_GL_DETAIL_VIEW(FOC295) A Value Is Missing For: &PERIOD1" which is one of two paramaters from the first report (hold file)


WebFOCUS 7.7.1
Windows 2000
Output: Excel and PDF
 
Posts: 82 | Registered: October 04, 2007Report This Post
Virtuoso
posted Hide Post
Can you post the code please...we need more info.


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Gold member
posted Hide Post
-* File gl_detail_sum.fex
TABLE FILE WF_GL_DETAIL_VIEW
SUM
'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.ACCOUNTNO' AS 'Acct'
'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.CENTER' AS 'Ctr'
'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.TRANSTYPE' AS 'TT'
'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.TRANSAMNT/P20.2'
AS 'Opening,Balance'
BY 'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.ACCT' NOPRINT
BY 'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.CENTER' NOPRINT
BY 'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.TRANSTYPE' NOPRINT
WHERE ( WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.PERIOD LT &PERIOD1.Period. ) AND ( WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.FISCAL_YEAR EQ &FISCALYEAR1.Fiscal Year. );
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS SUM1 FORMAT FOCUS INDEX ACCT TRANSTYPE WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.CENTER
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='SCREEN',
LEFTMARGIN=0.000000,
RIGHTMARGIN=0.000000,
TOPMARGIN=0.000000,
BOTTOMMARGIN=0.000000,
SQUEEZE=ON,
ORIENTATION=LANDSCAPE,
$
TYPE=REPORT,
GRID=OFF,
FONT='COURIER',
SIZE=10,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
$
TYPE=DATA,
COLUMN=N6,
FONT='COURIER',
$
TYPE=DATA,
COLUMN=N2,
FONT='COURIER',
$
TYPE=DATA,
COLUMN=N1,
FONT='COURIER',
$
TYPE=DATA,
COLUMN=N4,
FONT='COURIER',
$
TYPE=DATA,
COLUMN=N5,
FONT='COURIER',
$
TYPE=TITLE,
COLUMN=N6,
FONT='COURIER',
$
TYPE=TITLE,
COLUMN=N2,
FONT='COURIER',
$
TYPE=TITLE,
COLUMN=N1,
FONT='COURIER',
$
TYPE=TITLE,
COLUMN=N4,
FONT='COURIER',
$
TYPE=TITLE,
COLUMN=N5,
FONT='COURIER',
$
TYPE=REPORT,
COLUMN=N4,
SQUEEZE=1.916667,
$
TYPE=REPORT,
COLUMN=N5,
SQUEEZE=0.291667,
$
ENDSTYLE
END


WebFOCUS 7.7.1
Windows 2000
Output: Excel and PDF
 
Posts: 82 | Registered: October 04, 2007Report This Post
Expert
posted Hide Post
DaveZ,

Search on APP HOLD and/or APP FI or APP FILEDEF...

Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Gold member
posted Hide Post
After adding APP FI I still get error when selecting new masterfile for next report, following is my fex

-* File gl_detail_sum.fex
APP FI SUM1 C:/TEMP/SUM1.ftm
TABLE FILE WF_GL_DETAIL_VIEW
SUM
'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.ACCOUNTNO' AS 'Acct'
'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.CENTER' AS 'Ctr'
'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.TRANSTYPE' AS 'TT'
'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.TRANSAMNT/P20.2'
AS 'Opening,Balance'
BY 'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.ACCT' NOPRINT
BY 'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.CENTER' NOPRINT
BY 'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.TRANSTYPE' NOPRINT
WHERE ( WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.PERIOD LT &PERIOD1.Period. ) AND ( WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.FISCAL_YEAR EQ &FISCALYEAR1.Fiscal Year. );
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS SUM1 FORMAT FOCUS INDEX ACCT TRANSTYPE WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.CENTER
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='SCREEN',
LEFTMARGIN=0.000000,
RIGHTMARGIN=0.000000,
TOPMARGIN=0.000000,
BOTTOMMARGIN=0.000000,
SQUEEZE=ON,
ORIENTATION=LANDSCAPE,
$
TYPE=REPORT,
GRID=OFF,
FONT='COURIER',
SIZE=10,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
$
TYPE=DATA,
COLUMN=N6,
FONT='COURIER',
$
TYPE=DATA,
COLUMN=N2,
FONT='COURIER',
$
TYPE=DATA,
COLUMN=N1,
FONT='COURIER',
$
TYPE=DATA,
COLUMN=N4,
FONT='COURIER',
$
TYPE=DATA,
COLUMN=N5,
FONT='COURIER',
$
TYPE=TITLE,
COLUMN=N6,
FONT='COURIER',
$
TYPE=TITLE,
COLUMN=N2,
FONT='COURIER',
$
TYPE=TITLE,
COLUMN=N1,
FONT='COURIER',
$
TYPE=TITLE,
COLUMN=N4,
FONT='COURIER',
$
TYPE=TITLE,
COLUMN=N5,
FONT='COURIER',
$
TYPE=REPORT,
COLUMN=N4,
SQUEEZE=1.916667,
$
TYPE=REPORT,
COLUMN=N5,
SQUEEZE=0.291667,
$
ENDSTYLE
END


WebFOCUS 7.7.1
Windows 2000
Output: Excel and PDF
 
Posts: 82 | Registered: October 04, 2007Report This Post
Expert
posted Hide Post
DaveZ,

You will not be able to hold a file on the C: drive as you have coded it;
you hold data in an application folder that is on your APP PATH.

WebFOCUS holds data on the Reporting Server where DRIVE:/ibi/apps is located.

This may be the C: drive, I've seen it on D:, E:, U: etc...

But, TEMP would be in DRIVE:/ibi/apps/temp, if you actually have a TEMP folder???

For your exercise, you are holding as FOCUS file, .foc, not .ftm. So, use APP HOLD TEMP.

"IF" the folder TEMP "IS NOT" on the APP PATH, to use the file later,
APP APPENDPATH TEMP adds the TEMP folder to the end of the PATH
APP PREPENDPATH TEMP adds the TEMP folder to the start of the PATH

Since it is a .foc file, APP FI will not be needed. It is needed if you hold as a ftm file.

Hope this helps...

Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
<JG>
posted
allocating a file as a target can be done 1 of 2 ways

using the APP FILEDEF syntax
or the old FILEDEF syntax

using APP then the target must be an APP folder
using the old FILEDEF syntax the target can be any location that you have mapped network
access to
 
Report This Post
Gold member
posted Hide Post
got this message 0 NUMBER OF RECORDS IN TABLE= 319561 LINES= 11238

I look on the server and no file created, I must have my sytnax wrong, I created a folder on the server under apps
here is my fex

Thanks guys for all the help

-* File gl_detail_sum.fex
APP FILEDEF SUM1 C:\ibi\apps\qavis\TEMPFILES
TABLE FILE WF_GL_DETAIL_VIEW
SUM
'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.ACCOUNTNO' AS 'Acct'
'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.CENTER' AS 'Ctr'
'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.TRANSTYPE' AS 'TT'
'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.TRANSAMNT/P20.2'
AS 'Opening,Balance'
BY 'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.ACCT' NOPRINT
BY 'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.CENTER' NOPRINT
BY 'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.TRANSTYPE' NOPRINT
WHERE ( WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.PERIOD LT &PERIOD1.Period. ) AND ( WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.FISCAL_YEAR EQ &FISCALYEAR1.Fiscal Year. );
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS SUM1 FORMAT FOCUS INDEX ACCOUNTNO TRANSTYPE WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.CENTER
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='SCREEN',
LEFTMARGIN=0.000000,
RIGHTMARGIN=0.000000,
TOPMARGIN=0.000000,
BOTTOMMARGIN=0.000000,
SQUEEZE=ON,
ORIENTATION=LANDSCAPE,
$
TYPE=REPORT,
GRID=OFF,
FONT='COURIER',
SIZE=10,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
$
TYPE=DATA,
COLUMN=N6,
FONT='COURIER',
$
TYPE=DATA,
COLUMN=N2,
FONT='COURIER',
$
TYPE=DATA,
COLUMN=N1,
FONT='COURIER',
$
TYPE=DATA,
COLUMN=N4,
FONT='COURIER',
$
TYPE=DATA,
COLUMN=N5,
FONT='COURIER',
$
TYPE=TITLE,
COLUMN=N6,
FONT='COURIER',
$
TYPE=TITLE,
COLUMN=N2,
FONT='COURIER',
$
TYPE=TITLE,
COLUMN=N1,
FONT='COURIER',
$
TYPE=TITLE,
COLUMN=N4,
FONT='COURIER',
$
TYPE=TITLE,
COLUMN=N5,
FONT='COURIER',
$
TYPE=REPORT,
COLUMN=N4,
SQUEEZE=1.916667,
$
TYPE=REPORT,
COLUMN=N5,
SQUEEZE=0.291667,
$
ENDSTYLE
END


WebFOCUS 7.7.1
Windows 2000
Output: Excel and PDF
 
Posts: 82 | Registered: October 04, 2007Report This Post
Expert
posted Hide Post
APP HOLD TEMPFILES, no need for a FILEDEF

It's probably APP HOLD qavis

Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Gold member
posted Hide Post
I ran the fex it created four files sum1.foc, sum1.mas,foc$hold.mas and foc$hold.ftm went to create the next report I selected the masterfile and get the same error as before.


WebFOCUS 7.7.1
Windows 2000
Output: Excel and PDF
 
Posts: 82 | Registered: October 04, 2007Report This Post
<JG>
posted
Try adding
ON TABLE SET HOLDLIST PRINTONLY
to your first extract,
Because you do not have this line the hold file is going to contain
multiple columns with the same name.

Also make sure that another version of the SUM1 master does not exist
in an APPS folder higher up in your APPPATH
 
Report This Post
Gold member
posted Hide Post
The first report runs fine and created an output file on the server, trying to create the next report(hopefully the detail, part two) click on report, choose my master file and get same error as before "Error Parsing File WF_GL_DETAIL_VIEW(FOC295) A Value Is Missing For: &PERIOD1" these are the parameters from the first report.


WebFOCUS 7.7.1
Windows 2000
Output: Excel and PDF
 
Posts: 82 | Registered: October 04, 2007Report This Post
Expert
posted Hide Post
Add this to the top of your fex

-DEFAULT &PERIOD1 = ''

Your above code creates a FOCUS hold file, not a report.

Since the above is a summary file, AND, you are looking to create a detail file, put the code in the same program...

Step1: Summary
Step2: Detail

Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Master
posted Hide Post
If I am understanding the problem, you are trying to drill down from a summary to a detail, but I don't see where that you are executing the report programs. The code that you have included only creates a file, it really doesn't need a stylesheet. I am surprised that you didn't get an error on: 'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.TRANSAMNT/P20.2'. Since there are no spaces in the field names you don't need the single quotes. The /P20.2 should not be within the quotes. Since you are using only one file you really don't need the file prefixes 'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.

Try setting &ECHO on (-SET &ECHO = ALLWink and post the output from the run.


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Virtuoso
posted Hide Post
Dave

your error message says

"A Value Is Missing For: &PERIOD1"

in your second report there is no need to say

WHERE ( WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.PERIOD LT &PERIOD1.Period. ) AND ( WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.FISCAL_YEAR EQ &FISCALYEAR1.Fiscal Year. );


You created a hold file that holds only the records for the special period.

An other point is that your second report is summing all the records and you put it in a file with the name SUM1.
But what is your first step?




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Gold member
posted Hide Post
Lets start from the beginning I need to create a report that has both detail and summary records, the summary record is the opening balance for an account and the detail is a month's of general ledger transactions for that account. My thought process was/is that I create three passes of the master file, now the user will be asked to input the period(month) and fiscal year, hopefully using the same parameters in the first two passes. Once I have created the first output I select report and this is where I get the pharsing error pointing to the parameters. As for the definitions the system has dedefine them. I copied part of the first pass to the second pass to try getting pass the error.

-* File gl_detail_sum.fex
-DEFAULT &PERIOD1 = ''
-DEFAULT &FISCALYEAR1 = ''
APP HOLD qavis C:\ibi\apps\qavis\TEMPFILES
TABLE FILE WF_GL_DETAIL_VIEW
SUM
'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.ACCOUNTNO' AS 'Acct'
'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.CENTER' AS 'Ctr'
'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.TRANSTYPE' AS 'TT'
'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.TRANSAMNT/P20.2'
AS 'Opening,Balance'
BY 'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.ACCT' NOPRINT
BY 'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.CENTER' NOPRINT
BY 'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.TRANSTYPE' NOPRINT
WHERE ( WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.PERIOD LT &PERIOD1.Period. ) AND ( WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.FISCAL_YEAR EQ &FISCALYEAR1.Fiscal Year. );
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS SUM1 FORMAT FOCUS INDEX ACCOUNTNO TRANSTYPE WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.CENTER
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='SCREEN',
LEFTMARGIN=0.000000,
RIGHTMARGIN=0.000000,
TOPMARGIN=0.000000,
BOTTOMMARGIN=0.000000,
SQUEEZE=ON,
ORIENTATION=LANDSCAPE,
$
TYPE=REPORT,
GRID=OFF,
FONT='COURIER',
SIZE=10,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
$
TYPE=DATA,
COLUMN=N6,
FONT='COURIER',
$
TYPE=DATA,
COLUMN=N2,
FONT='COURIER',
$
TYPE=DATA,
COLUMN=N1,
FONT='COURIER',
$
TYPE=DATA,
COLUMN=N4,
FONT='COURIER',
$
TYPE=DATA,
COLUMN=N5,
FONT='COURIER',
$
TYPE=TITLE,
COLUMN=N6,
FONT='COURIER',
$
TYPE=TITLE,
COLUMN=N2,
FONT='COURIER',
$
TYPE=TITLE,
COLUMN=N1,
FONT='COURIER',
$
TYPE=TITLE,
COLUMN=N4,
FONT='COURIER',
$
TYPE=TITLE,
COLUMN=N5,
FONT='COURIER',
$
TYPE=REPORT,
COLUMN=N4,
SQUEEZE=1.916667,
$
TYPE=REPORT,
COLUMN=N5,
SQUEEZE=0.291667,
$
ENDSTYLE
END
APP HOLD qavis C:\ibi\apps\qavis\TEMPFILES
TABLE FILE WF_GL_DETAIL_VIEW
PRINT
'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.ACCOUNTNO' AS 'Acct'
'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.CENTER' AS 'Ctr'
'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.TRANSTYPE' AS 'TT'
'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.TRANSAMNT/P20.2'
WHERE ( WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.PERIOD EQ &PERIOD1.Period. ) AND ( WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.FISCAL_YEAR EQ &FISCALYEAR1.Fiscal Year. );
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS SUM2 FORMAT FOCUS INDEX ACCOUNTNO TRANSTYPE WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.CENTER
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='SCREEN',
LEFTMARGIN=0.000000,
RIGHTMARGIN=0.000000,
TOPMARGIN=0.000000,
BOTTOMMARGIN=0.000000,
SQUEEZE=ON,
ORIENTATION=LANDSCAPE,
$
TYPE=REPORT,
GRID=OFF,
FONT='COURIER',
SIZE=10,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
$
TYPE=DATA,
COLUMN=N6,
FONT='COURIER',
$
TYPE=DATA,
COLUMN=N2,
FONT='COURIER',
$
TYPE=DATA,
COLUMN=N1,
FONT='COURIER',
$
TYPE=DATA,
COLUMN=N4,
FONT='COURIER',
$
TYPE=DATA,
COLUMN=N5,
FONT='COURIER',
$
TYPE=TITLE,
COLUMN=N6,
FONT='COURIER',
$
TYPE=TITLE,
COLUMN=N2,
FONT='COURIER',
$
TYPE=TITLE,
COLUMN=N1,
FONT='COURIER',
$
TYPE=TITLE,
COLUMN=N4,
FONT='COURIER',
$
TYPE=TITLE,
COLUMN=N5,
FONT='COURIER',
$
TYPE=REPORT,
COLUMN=N4,
SQUEEZE=1.916667,
$
TYPE=REPORT,
COLUMN=N5,
SQUEEZE=0.291667,
$
ENDSTYLE
END


WebFOCUS 7.7.1
Windows 2000
Output: Excel and PDF
 
Posts: 82 | Registered: October 04, 2007Report This Post
Virtuoso
posted Hide Post
Dave

What I see here is that you are creating two holded files SUM1 and SUM2

If you leave out all the styling codes (which you do not need if you create holdfiles) then is is wat you do.

first

TABLE FILE WF_GL_DETAIL_VIEW
SUM
'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.ACCOUNTNO' AS 'Acct'
'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.CENTER' AS 'Ctr'
'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.TRANSTYPE' AS 'TT'
'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.TRANSAMNT/P20.2'
AS 'Opening,Balance'
BY 'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.ACCT' NOPRINT
BY 'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.CENTER' NOPRINT
BY 'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.TRANSTYPE' NOPRINT
WHERE ( WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.PERIOD LT &PERIOD1.Period. ) AND ( WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.FISCAL_YEAR EQ &FISCALYEAR1.Fiscal Year. );
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS SUM1 FORMAT FOCUS INDEX ACCOUNTNO TRANSTYPE WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.CENTER
END


So you get a IMHO strange report

I would do this

TABLE FILE WF_GL_DETAIL_VIEW
SUM
'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.TRANSAMNT/P20.2'
AS 'Opening,Balance'
BY 'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.ACCT' 
BY 'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.CENTER'
BY 'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.TRANSTYPE' 

WHERE ( WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.PERIOD LT &PERIOD1.Period. ) AND ( WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.FISCAL_YEAR EQ &FISCALYEAR1.Fiscal Year. );
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS SUM1 FORMAT FOCUS INDEX ACCOUNTNO TRANSTYPE CENTER 
END


Summing your by fields is not very effective.

Now you do more or less the same for the detail file.
instead of SUM you do a PRINT.
and that file is also hold as a focus file wit the same indexes.

I would do the PRINT first and for the SUM use the HOLDfile, now you have to go to the database only one time, this will b much quicker.

Then what....

You have two hold files format FOCUS with 4 INDEXES.

What would be the next step? Who is going to use what?




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Virtuoso
posted Hide Post
Dave

to show you what I mean


TABLE FILE WF_GL_DETAIL_VIEW
PRINT
'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.TRANSAMNT'
AS 'Opening,Balance'
BY 'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.ACCT' 
BY 'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.CENTER'
BY 'WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.TRANSTYPE' 

WHERE ( WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.PERIOD LT &PERIOD1.Period. ) AND ( WF_GL_DETAIL_VIEW.WF_GL_DETAIL_VIEW.FISCAL_YEAR EQ &FISCALYEAR1.Fiscal Year. );
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS DETAILGL FORMAT FOCUS INDEX ACCOUNTNO TRANSTYPE CENTER 
END
-RUN
-* now create from detail the summed report
TABLE FILE DETAILGL
SUM
TRANSAMNT

BY ACCT 
BY CENTER
BY TRANSTYPE 
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS SUM1 FORMAT FOCUS INDEX ACCOUNTNO TRANSTYPE CENTER 
END


try this....

If you want to keep your holdfiles on some place for further use you need the APP, but if you create the reports at the same time there is no need for that.

BTW take a look at FML for GL reporting the best option....




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Gold member
posted Hide Post
Thank you for the suggestion, I will try that

After creating the two hold files I would bring link them together by the indexes and create a report from them in pdf format.


WebFOCUS 7.7.1
Windows 2000
Output: Excel and PDF
 
Posts: 82 | Registered: October 04, 2007Report This Post
Expert
posted Hide Post
Actually, Dave, what I would do is create an extra phony sortfield for each hold file (doesn't need to be a FOCUS file). For instance if you want the summary to print first, the sort value would be 1 and 2 for the detail.

Then your hold file masters would be identical in content and you could use the MORE command (Universal Concatenation) to produce your report.

Joining isn't going to help you.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Virtuoso
posted Hide Post
try this

TABLE FILE WF_GL_DETAIL_VIEW
SUM
TRANSAMNT
BY ACCT 
BY TRANSTYPE 
PRINT
TRANSAMNT 
BY ACCT 
BY TRANSTYPE 
ACROSS CENTER
WHERE ( PERIOD LT &PERIOD1.Period. ) AND ( FISCAL_YEAR EQ &FISCALYEAR1.Fiscal Year. );
ON TABLE COLUMN-TOTAL
ON TABLE ROW-TOTAL
END


Just for the fun...
You might be surprised....

If it works well depends maybe on the number of CENTER fields.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Gold member
posted Hide Post
I need two passes at the masterfile due to the column opening balance must include all transactions summed from prior periods for this fiscal year (that's the summary part), then the next past is to show all gl transactions for the current period in detail format report would look like
acct center openbal transamnt
010101 000 9999999
50
100
101
then totals for acct


WebFOCUS 7.7.1
Windows 2000
Output: Excel and PDF
 
Posts: 82 | Registered: October 04, 2007Report This Post
Virtuoso
posted Hide Post
That can be done in one report


DEFINE FILE WF_GL_DETAIL_VIEW
OPENBALANS/D15.2=IF PERIOD EQ &PERIOD1 AND  FISCAL_YEAR EQ &FISCALYEAR1 THEN TRANSAMT ELSE 0;
TRANSACT/D15.2=IF PERIOD GT &PERIOD1 AND  FISCAL_YEAR EQ &FISCALYEAR1 THEN TRANSAMT ELSE 0;
END
TABLE FILE WF_GL_DETAIL_VIEW
SUM
OPENBALANS 
BY ACCT 
BY CENTER
PRINT
TRANSACT 
BY ACCT 
BY CENTER
BY TRANSTYPE 
ON ACCT SUBTOTAL
WHERE ( PERIOD GE &PERIOD1.Period. ) AND ( FISCAL_YEAR EQ &FISCALYEAR1.Fiscal Year. );
ON TABLE COLUMN-TOTAL
END


You might want to compute the closing balans too (openbalans + transact) only for the last line BY ACCT and BY CENTER....




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Gold member
posted Hide Post
I got pulled off of it for a day or so, I will get caught up this afternoon and try the suggestions.


WebFOCUS 7.7.1
Windows 2000
Output: Excel and PDF
 
Posts: 82 | Registered: October 04, 2007Report This Post
Gold member
posted Hide Post
Thanks That works great! I learned alot. One other question the define field openbalans since that is a sum field does that need to be a stationarry field, or can I moved it to the far right as the last column on the report?(after the print feilds)


WebFOCUS 7.7.1
Windows 2000
Output: Excel and PDF
 
Posts: 82 | Registered: October 04, 2007Report This Post
Virtuoso
posted Hide Post
Yes you can Dave

add an extra line

TABLE FILE WF_GL_DETAIL_VIEW
SUM
OPENBALANS NOPRINT
BY ACCT 
BY CENTER
PRINT
TRANSACT 
BY ACCT 
BY CENTER
BY TRANSTYPE 
ON ACCT SUBTOTAL
COMPUTE NEWOPEN/D15=C1; AS ''
....





Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     creating a report with detail and summary,

Copyright © 1996-2020 Information Builders