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 issue

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

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

Can you duplicate this quandary with the CAR FILE, or another IBI Sample file?

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

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.

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

quote:

SPLIT/I1=(MAXRATE GT 0);
XSTORE/A20= IF (SPLIT) THEN STORE ELSE '(ALL)';
XRATE/I3 MISSING ON = IF (SPLIT) THEN RATE ELSE MISSING;

I havent seen this kind of code before, I like it