hi i have to summarize the records into a single line based on the tax rate column. for example if the tax rate is 0% for all the stores within a state then i want a single line summary for that particular state.like wise if the tax rate is non 0% for all the stores within a state then i want store level detail records along with the subtotal for that state. the code i used to acheive this is:
TABLE FILE HOLD1
PRINT
storenumber
TaxRate
TaxOwed
TaxCollected
BY state_name
WHERE TaxRate NE '0.0000'
ON TABLE HOLD AS HOLDDETAIL
END
TABLE FILE HOLD1
PRINT
storenumber
TaxRate
TaxOwed
TaxCollected
BY state_name
WHERE TaxRate EQ '0.0000'
ON TABLE HOLD AS HOLDDETAILTAX0
END
TABLE FILE HOLDDETAIL
PRINT *
BY state_name
ON CODE_N SUBTOTAL
MORE
FILE HOLDDETAILTAX0
END
-EXIT
till here it worked fine. but if say suppose in a state there are 0% and non 0% taxrate stores then i wnat them in detail level for each store taht is 0 % and non 0% combined(with earlier code the report output is printing non 0% taxrate store in detail level for particular state and then giving subtotals and not 0% taxrate is given as single line summary).. please help me in solving this issueThis message has been edited. Last edited by: Kerry,
WebFOCUS 7.6.10 Windows Output: Excel,PDF
September 02, 2009, 10:16 AM
Francis Mariani
It might take us a few hours to untangle this:
quote:
if the tax rate is 0% for all the stores within a state then i want a single line summary for that particular state... if the tax rate is non 0% for all the stores within a state then i want store level detail records
quote:
if say suppose in a state there are 0% and non 0% taxrate stores then i wnat them in detail level for each store taht is 0 % and non 0% combined(with earlier code the report output is printing non 0% taxrate store in detail level for particular state and then giving subtotals and not 0% taxrate is given as single line summary
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
September 02, 2009, 11:00 PM
Doug
Can you duplicate this quandary with the CAR FILE, or another IBI Sample file?
September 03, 2009, 02:27 AM
Danny-SRL
If I understood you correctly, this might be a solution:
-* File subbu1.fex
DEFINE FILE CAR
SUBBU/A1=IF RPM EQ 0 THEN '0' ELSE '1';
SP/A1=' ';
END
TABLE FILE CAR
PRINT SUBBU NOPRINT
DCOST
RCOST
COMPUTE RCOST0/D7=IF SUBBU EQ '0' THEN RCOST ELSE 0; NOPRINT
COMPUTE RCOST1/D7=IF SUBBU EQ '1' THEN RCOST ELSE 0; NOPRINT
COMPUTE DCOST0/D7=IF SUBBU EQ '0' THEN DCOST ELSE 0; NOPRINT
COMPUTE DCOST1/D7=IF SUBBU EQ '1' THEN DCOST ELSE 0; NOPRINT
BY SP AS ' '
BY COUNTRY
BY SUBBU NOPRINT
ON COUNTRY SUBHEAD
" "
ON COUNTRY SUBFOOT
"Totals for <COUNTRY <+0> "
" <+0>RPM 0 <ST.DCOST0<ST.RCOST0"
" <+0>RPM OVER 0 <ST.DCOST1<ST.RCOST1"
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=SUBFOOT, HEADALIGN=BODY, JUSTIFY=RIGHT, $
TYPE=SUBFOOT, LINE=1, JUSTIFY=LEFT, $
ENDSTYLE
END
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
September 03, 2009, 08:57 AM
subbu
let me give u an example : say suppose TYPE A 0.00 TYPE A 0.00 TYPE A 0.00 TYPE A 0.00 TYPE A 0.00
TYPE B 1.00 TYPE B 2.00 TYPE B 0.00 TYPE B 4.00
i want to produces the following results:
TYPE AMOUNT ---- ------ TOTAL TYPE A 0.00 (that is single line summary)
TYPE B 1.00 2.00 0.00 4.00
TOTAL TYPE B 7.00
only when all the values in that type are 0 only then i want to show a single line summary of that type. else if the type has different values(may also have 0 sometimes) then i want detail level for each.
sorry if i was not clear before.
WebFOCUS 7.6.10 Windows Output: Excel,PDF
September 03, 2009, 09:49 AM
j.gross
Is this what you need? --
[1]. if there is no sales tax in the state (rate eq zero for all stores throughtout the state) produce just a single line summarizing the state.
[2]. otherwise (if any stores in the state have sales tax) summarize each store and then total the state.
If so, this approach should work:
define a state-level flag to indicate whether the state is type [1] or type [2];
define a store-sort-key as "ALL" in type [1]states, and store key in type [2].
sum ... BY state BY store-sort-key as Store
ON state SUBTOTAL MULTILINES
For example:
DEFINE FILE CAR
STATE/A20=COUNTRY;
STORE/A20=CAR;
RATE/I3=IMOD(ACCEL,3,'I3');
END
TABLE FILE CAR
SUM MAX.RATE AS MAXRATE
BY STATE
SUM SALES
BY STATE
BY STORE
BY RATE
ON TABLE HOLD
ON TABLE SET ASNAMES ON
END
-RUN
DEFINE FILE HOLD
SPLIT/I1=(MAXRATE GT 0);
XSTORE/A20= IF (SPLIT) THEN STORE ELSE '(ALL)';
XRATE/I3 MISSING ON = IF (SPLIT) THEN RATE ELSE MISSING;
END
TABLE FILE HOLD
SUM SALES
BY STATE
BY XSTORE AS '(STORE)'
BY XRATE AS '% TAX'
ON STATE SUBTOTAL MULTILINES
ON TABLE PCHOLD FORMAT PDF
END
- Jack Gross WF through 8.1.05
September 03, 2009, 11:01 AM
PBax
quote:
SPLIT/I1=(MAXRATE GT 0); XSTORE/A20= IF (SPLIT) THEN STORE ELSE '(ALL)'; XRATE/I3 MISSING ON = IF (SPLIT) THEN RATE ELSE MISSING;