 |
|||||
|
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
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 WebFOCUS/FOCUS Forum on Focal Point [CLOSED] Not calculating the sub total for begining balance
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [CLOSED] Not calculating the sub total for begining balance Read-Only TopicGo | Search | Notify | Tools |
| [CLOSED] Not calculating the sub total for begining balance | Login/Join |
| Gold member |
I have the Sales Tax report which calculates the Begining Balance and Ending Balance and should also display the total of Begining Balance and Ending Balance. When I run the report, it calculates the Total for the Ending Balance, but the Total for 'Begining Balance' is blank. here is the code: -* Created by Report Assistant
-* HTML Tool
-* Created by Report Assistant
-* FF Line do not change this line! Field Name
-* FF Line do not change this line! Alias
-* FF Line do not change this line! Format
-* FF Line do not change this line! Segment
-* Programmer Date Ticket Description
-* --------------- ---------- -------- -----------------------------------------------
-* David Ibach 05/15/2005 N/A Report Creation.
-* David Ibach 11/02/2005 N/A Add Ending Balance Calculation.
-* David Ibach 01/15/2006 148623 BU OBJ SUB blank for accounts with beginning balance but no activity.
-*---------------------------------------------------------------------------------------------------------
-*
DEFINE FILE F0902
GL_PD/D2 = &GL_Period;
-*---------------------------------------------------------------------------------------------------------
-* CALCULATE BEGINNING BALANCE BASED ON THE PERIOD FOR WHICH REPORT IS BEING RUN.
-*---------------------------------------------------------------------------------------------------------
BeginBalance/P16.2 = IF GL_PD = 1 THEN BAL_FWD
ELSE IF GL_PD = 2 THEN BAL_FWD + NET_POSTING_01
ELSE IF GL_PD = 3 THEN BAL_FWD + NET_POSTING_01 + NET_POSTING_02
ELSE IF GL_PD = 4 THEN BAL_FWD + NET_POSTING_01 + NET_POSTING_02 + NET_POSTING_03
ELSE IF GL_PD = 5 THEN BAL_FWD + NET_POSTING_01 + NET_POSTING_02 + NET_POSTING_03 + NET_POSTING_04
ELSE IF GL_PD = 6 THEN BAL_FWD + NET_POSTING_01 + NET_POSTING_02 + NET_POSTING_03 + NET_POSTING_04 + NET_POSTING_05
ELSE IF GL_PD = 7 THEN BAL_FWD + NET_POSTING_01 + NET_POSTING_02 + NET_POSTING_03 + NET_POSTING_04 + NET_POSTING_05 + NET_POSTING_06
ELSE IF GL_PD = 8 THEN BAL_FWD + NET_POSTING_01 + NET_POSTING_02 + NET_POSTING_03 + NET_POSTING_04 + NET_POSTING_05 + NET_POSTING_06 + NET_POSTING_07
ELSE IF GL_PD = 9 THEN BAL_FWD + NET_POSTING_01 + NET_POSTING_02 + NET_POSTING_03 + NET_POSTING_04 + NET_POSTING_05 + NET_POSTING_06 + NET_POSTING_07 + NET_POSTING_08
ELSE IF GL_PD = 10 THEN BAL_FWD + NET_POSTING_01 + NET_POSTING_02 + NET_POSTING_03 + NET_POSTING_04 + NET_POSTING_05 + NET_POSTING_06 + NET_POSTING_07 + NET_POSTING_08 + NET_POSTING_09
ELSE IF GL_PD = 11 THEN BAL_FWD + NET_POSTING_01 + NET_POSTING_02 + NET_POSTING_03 + NET_POSTING_04 + NET_POSTING_05 + NET_POSTING_06 + NET_POSTING_07 + NET_POSTING_08 + NET_POSTING_09 + NET_POSTING_10
ELSE IF GL_PD = 12 THEN BAL_FWD + NET_POSTING_01 + NET_POSTING_02 + NET_POSTING_03 + NET_POSTING_04 + NET_POSTING_05 + NET_POSTING_06 + NET_POSTING_07 + NET_POSTING_08 + NET_POSTING_09 + NET_POSTING_10 + NET_POSTING_11
;
ENDBAL/P16.2 = IF GL_PD = 1 THEN NET_POSTING_01
ELSE IF GL_PD = 2 THEN NET_POSTING_02
ELSE IF GL_PD = 3 THEN NET_POSTING_03
ELSE IF GL_PD = 4 THEN NET_POSTING_04
ELSE IF GL_PD = 5 THEN NET_POSTING_05
ELSE IF GL_PD = 6 THEN NET_POSTING_06
ELSE IF GL_PD = 7 THEN NET_POSTING_07
ELSE IF GL_PD = 8 THEN NET_POSTING_08
ELSE IF GL_PD = 9 THEN NET_POSTING_09
ELSE IF GL_PD = 10 THEN NET_POSTING_10
ELSE IF GL_PD = 11 THEN NET_POSTING_11
ELSE IF GL_PD = 12 THEN NET_POSTING_12;
FY/D12.0 = &Fiscal_Year;
END
TABLE FILE F0902
SUM ACCOUNT_ID AS 'AID'
BUSINESS_UNIT AS 'BUNT'
OBJECT_ACCOUNT AS 'OBJ'
SUBSIDIARY AS 'SUBSID'
ENDBAL AS 'ENDBAL'
LEDGER_TYPE AS 'LEDGT'
LST.FISCAL_YEAR AS 'FY'
BeginBalance AS 'BB'
COMPUTE PRINTFLAG/A1 = IF BeginBalance EQ 0 AND ENDBAL EQ 0 THEN '0' ELSE '1';
BY COMPANY
BY ACCOUNT_ID
WHERE (OBJECT_ACCOUNT EQ '2210')
AND (LEDGER_TYPE EQ 'AA')
AND (FISCAL_YEAR EQ &Fiscal_Year);
WHERE TOTAL (PRINTFLAG EQ '1');
WHERE ( COMPANY EQ &Company.(OR(<00001,00001>,<00002,00002>,<00010,00010>,<00082,00082>,<00084,00084>,<00086,00086>,<00110,00110>,<00112,00112>,<00401,00401>,<00610,00610>,<00615,00615>,<00700,00700>,<00710,00710>,<00715,00715>,<00720,00720>,<00725,00725>,<00810,00810>,<01002,01002>,<01003,01003>,<01007,01007>,<01009,01009>,<01010,01010>,<01011,01011>,<01012,01012>,<01013,01013>,<01014,01014>,<01022,01022>,<01024,01024>,<01025,01025>,<01027,01027>,<01028,01028>,<01030,01030>,<01032,01032>,<01033,01033>,<01036,01036>,<01037,01037>,<01041,01041>,<01201,01201>,<01202,01202>,<01401,01401>,<01701,01701>,<01702,01702>)).Company. );
-*WHERE ( F0010.COMPANY EQ &Company.(OR(FIND F0010.COMPANY IN F0010)).Company. );
-*---------------------------------------------------------------------------------------------------------
-* FOR PERFORMANCE PURPOSES, SO JOIN TO F0911 NOT REQURIED, SAVE F0902 OUTPUT TO HOLD FILE.
-* THIS FILE IS THEN JOINED TO THE F0911.
-*---------------------------------------------------------------------------------------------------------
ON TABLE HOLD AS BALANCES FORMAT FOCUS
END
-RUN
-*---------------------------------------------------------------------------------------------------------
-* SECTION COMMENTED OUT, BUT USED FOR TROUBLESHOOTING. PRINTS THE CONTENTS OF THE HOLD FILE.
-*---------------------------------------------------------------------------------------------------------
-*? HOLD BALANCES
-*TABLE FILE BALANCES
-*PRINT *
-*END
-*---------------------------------------------------------------------------------------------------------
-* END PRINT HOLD FILE
-*---------------------------------------------------------------------------------------------------------
JOIN BALANCES.AID AND BALANCES.LEDGT AND BALANCES.FY IN BALANCES TO ALL F0911.F0911.ACCOUNT_ID AND F0911.F0911.LEDGER_TYPE AND F0911.F0911.FISCAL_YEAR IN F0911 AS J1
END
SET ALL=PASS
DEFINE FILE BALANCES
EndBal/P20.2 = BB + F0911.F0911.AMOUNT;
END
TABLE FILE BALANCES
SUM
F0911.F0911.AMOUNT AS 'Period,Amount'
BY BALANCES.COMPANY NOPRINT
BY F0911.F0911.ACCOUNT_ID NOPRINT
-*BY F0911.F0911.BUSINESS_UNIT AS 'Business,Unit'
-*BY F0911.F0911.OBJECT_ACCOUNT AS 'Object, Account'
-*BY F0911.F0911.SUBSIDIARY AS 'Subsidiary'
-*BY BALANCES.BusUnit AS 'Business,Unit'
BY BUNT AS 'Business,Unit'
BY BALANCES.OBJ AS 'Object, Account'
BY BALANCES.SUBSID AS 'Subsidiary'
BY BB AS 'Beginning,Balance'
BY ENDBAL NOPRINT
ACROSS F0911.F0911.DOCUMENT_TYPE AS 'Document Type'
COMPUTE EndBalance/P16.2 = BB + ENDBAL; AS 'Ending Balance'
HEADING
"HANSON Aggregates, Inc."
"Sales Tax - Company <F0911.F0911.COMPANY"
"G/L By Document Type"
"For G/L Period: &GL_Period Fiscal Year: &Fiscal_Year"
ON BALANCES.COMPANY SUBTOTAL AS 'COMPANY TOTAL'
ON BALANCES.COMPANY PAGE-BREAK
WHERE (F0911.F0911.OBJECT_ACCOUNT EQ '2210')
AND (F0911.F0911.LEDGER_TYPE EQ 'AA')
AND (F0911.F0911.G_L_POSTED_CODE EQ 'P')
-*---------------------------------------------------------------------------------------------------------
-* .EVAL USED FOR PASSING PARAMETER VALUES FROM ONE REPORT TO THE NEXT.
-*---------------------------------------------------------------------------------------------------------
AND (F0911.F0911.FISCAL_YEAR EQ &Fiscal_Year.EVAL)
AND (F0911.F0911.G_L_PERIOD_NUMBER EQ &GL_Period.EVAL);
-*AND (F0911.F0911.DOCUMENT_TYPE NE 'AG');
ON TABLE PCHOLD FORMAT &WFFMT.(<HTML,HTML>,<Excel 2000,EXL2K>,<Excel Formula,EXL2K FORMULA>).Select type of display output.
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
ORIENTATION=LANDSCAPE,
$
TYPE=REPORT,
GRID=OFF,
$
TYPE=DATA, ACROSSCOLUMN=F0911.F0911.AMOUNT,
FOCEXEC=app/gldetail.fex(Account_ID=N2 Fiscal_Year=&Fiscal_Year.EVAL Ledger_Type='AA' GL_Period=&GL_Period.EVAL Document_Type=F0911.F0911.DOCUMENT_TYPE ),
$
TYPE=HEADING,
LINE=1,
OBJECT=TEXT,
ITEM=1,
SIZE=10,
STYLE=BOLD,
$
TYPE=HEADING,
LINE=2,
OBJECT=TEXT,
ITEM=1,
SIZE=10,
STYLE=BOLD,
$
TYPE=HEADING,
LINE=3,
OBJECT=TEXT,
ITEM=1,
SIZE=10,
STYLE=BOLD,
$
TYPE=HEADING,
LINE=4,
OBJECT=TEXT,
ITEM=1,
SIZE=10,
STYLE=BOLD,
$
TYPE=HEADING,
LINE=4,
OBJECT=TEXT,
ITEM=2,
SIZE=10,
STYLE=BOLD,
$
TYPE=HEADING,
LINE=4,
OBJECT=TEXT,
ITEM=3,
SIZE=10,
STYLE=BOLD,
$
TYPE=HEADING,
LINE=4,
OBJECT=FIELD,
ITEM=1,
SIZE=10,
STYLE=BOLD,
$
TYPE=HEADING,
LINE=4,
OBJECT=FIELD,
ITEM=2,
SIZE=10,
STYLE=BOLD,
$
ENDSTYLE
ON TABLE SET HTMLCSS OFF
END
-* End Report Assistant
This message has been edited. Last edited by: Kerry, WebFOCUS 7.6.9 Windows 2003 HTML, Plain Text | ||
|
| Virtuoso |
-*TABLE FILE BALANCES -*PRINT * -*END Kpiracha, in trying to isolate the problem, if you uncomment the lines above (and add -EXIT right after) and run the report again can you see both balances for *each* account? Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2 Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K. | |||
|
| Expert |
njsden has the right idea. The issue should be in the first table file. I would suggest adding a -EXIT after the BALANCES file is created, and comment out the ON TABLE HOLD. Also, because you are joining from the balances file, you do not have to create it as a FOCUS file. Waz...
| |||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||
| Gold member |
For some reason I never received any email notification about this post. When I run the report I do see the begining balance and ending balance both but I don't see the 'Total' for the begining balance. I am thinking it is related to the way we are using the ACROSS statement. What I can't figure out is how to flow the report to show the 'Begining Balance' total. WebFOCUS 7.6.9 Windows 2003 HTML, Plain Text | |||
|
| Master |
Your sorting on it not summing it. The only thing you are summing and therefore the only total is period amount and EndBalance (in your compute). If you want this to total either add it to your SUM or compute a variable equal to it. Pat WF 7.6.8, AIX, AS400, NT AS400 FOCUS, AIX FOCUS, Oracle, DB2, JDE, Lotus Notes | |||
|
| Gold member |
great thank you. I did the sum for the 'Begining Balance' and then the Sum for the 'Perior Amount'. I do see the total for the 'Begining Balance' but it is not accurate. Is it possible that I can get the Begining Balance total, then document type and then Ending Balance. WebFOCUS 7.6.9 Windows 2003 HTML, Plain Text | |||
|
| Master |
I'm not sure how you changed the code, but if the total is not accurate it is possible that you have multiple records because you will have one F0911 record for each period. Try SUM FST.BB AS 'Beginning,Balance' and see if that resolves the issue. Pat WF 7.6.8, AIX, AS400, NT AS400 FOCUS, AIX FOCUS, Oracle, DB2, JDE, Lotus Notes | |||
|
| Gold member |
Is there a way I can upload a screenshot and show how the output looks like? WebFOCUS 7.6.9 Windows 2003 HTML, Plain Text | |||
|
| Virtuoso |
yes you can create a screenshot and post it on a publc website like photobucket or something like that then you can create a link in your message to that picture
| |||||||
|
| Gold member |
FST command just returned the balance 0:00 for the begining balance. I have uploaded the screenshot. We need to calculate the total of the 'Beginning Balance' without losing the formating. Is it possible. http://s863.photobucket.com/al...ReportScreenshot.jpg WebFOCUS 7.6.9 Windows 2003 HTML, Plain Text | |||
|
| Master |
If you run this much of your code and then TABLE FILE BALANCES PRINT * END Are your beginning balances accurate? You should have only one record per company/account id/ledger type/sub ledger/fiscal year so unless you are in year 1 or had a zero balance at the end of the prior year (plus your net_posting(s)) your balance shouldn't be 0.00. Unfortunately, I don't have access to your screen shot. Pat WF 7.6.8, AIX, AS400, NT AS400 FOCUS, AIX FOCUS, Oracle, DB2, JDE, Lotus Notes | |||
|
| Expert |
In your TABLE, you are using an alias fieldname, BB, so, ADD SET ASNAMES = ON at the top of the fex. In your DEFINE, you have EndBal, in mixed case. In the TABLE, you have ENDBAL in CAPS. Your DEFINE, EndBal, is not included in the TABLE. FYI Tom Flynn WebFOCUS 8.1.05 - PROD/QA DB2 - AS400 - Mainframe | |||
|
| Gold member |
After the following line: COMPUTE EndBalance/P16.2 = BB + ENDBAL; AS 'Ending Balance' We added: COMPUTE BeginBalance/P16.2 = BB AS 'Begining Balance' Which fixed the issue WebFOCUS 7.6.9 Windows 2003 HTML, Plain Text | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicFocal Point Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [CLOSED] Not calculating the sub total for begining balance
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [CLOSED] Not calculating the sub total for begining balanceCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |