[SOLVED] Order rows by lowest subtotal and remove subtotal tag

Focal Point

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/8517066586

February 27, 2017, 12:31 AM

Durai

[SOLVED] Order rows by lowest subtotal and remove subtotal tag

I need to order region rows by lowest subtotal and also i am getting region name for each subtotal row which also needs to be removed.could any one help me please?

TABLE FILE GGSALES
SUM UNITS
BY REGION
BY PRODUCT
ON REGION SUB-TOTAL AS 'Sub Total'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
$
TYPE=SUBTOTAL,
OBJECT=TAG,
BY=1,
SIZE=8,
$
END

This message has been edited. Last edited by: FP Mod Chuck,

February 27, 2017, 04:43 AM

Dave

Use a HOLD file with the subtotals in it and use it as a BY-field in the next TABLE statement.

Either hidden or not

TABLE FILE GGSALES
	SUM UNITS WITHIN REGION AS 'SUBTOT'
		UNITS
	BY REGION
	BY PRODUCT
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS Q
END

TABLE FILE Q
SUM UNITS
BY SUBTOT 
BY REGION
BY PRODUCT
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
$
TYPE=SUBTOTAL,
OBJECT=TAG,
 BY=1,
 SIZE=8,
$
END

February 27, 2017, 05:28 AM

Durai

Thank you very much Dave.i need the sub total as one of the row below the units(by region)

February 27, 2017, 07:56 AM

MartinY

TABLE FILE GGSALES
	SUM UNITS WITHIN REGION AS 'SUBTOT'
		UNITS
	BY REGION
	BY PRODUCT
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS Q
END

TABLE FILE Q
SUM UNITS
BY SUBTOT NOPRINT
BY REGION
BY PRODUCT
ON REGION SUBTOTAL AS 'Sub Total'
ON TABLE PCHOLD FORMAT HTML
ON TABLE NOTOTAL
ON TABLE SET STYLE *
$
TYPE=SUBTOTAL,
OBJECT=TAG,
 BY=1,
 SIZE=8,
$
END

February 27, 2017, 04:46 PM

Crymsyn

Would something like this work for you?

It does a multi verb to store the subtotal and can sort by that in the next step

 TABLE FILE GGSALES
SUM UNITS AS 'REGION_ST'
BY REGION

SUM UNITS
BY REGION
BY PRODUCT
ON TABLE HOLD AS HOLD_GGSA
END

TABLE FILE HOLD_GGSA
SUM UNITS
BY LOWEST REGION_ST NOPRINT
BY REGION
BY PRODUCT
ON REGION_ST SUBFOOT
"Sub Total <REGION_ST"
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
TYPE=SUBFOOT,
SIZE=8,
$
ENDSTYLE
END

February 27, 2017, 11:44 PM

Durai

Thank you very much martin it is working fine .but in the subtotal tag i am getting 'Sub Total Midwest' needs to be replaced with 'Sub Total' only.is it possible?

February 28, 2017, 12:11 AM

Durai

Thank you very much Crymsyn.

March 02, 2017, 03:49 PM

FP Mod Chuck

Change this line of code from
"Sub Total to
"Sub Total"

March 02, 2017, 04:06 PM

FP Mod Chuck

Oops I meant change this line
"Sub Total To
"Sub Total"

March 02, 2017, 04:08 PM

FP Mod Chuck

Wow... It keeps deleting part of the original line

"Sub Total to
"Sub Total"

March 03, 2017, 04:53 AM

Durai

[QUOTE]Originally posted by Chuck Wolff:
Wow... It keeps deleting part of the original line

thank you Chuck but it keeps "Sub total" i need sub total without double quotes

March 03, 2017, 07:57 AM

MartinY

quote:
but it keeps "Sub total" i need sub total without double quotes

What do you mean by that ?
The labelling : « Sub Total » is not displayed with double quotes.

SET ASNAMES = ON
TABLE FILE GGSALES
SUM UNITS AS 'REGION_ST'
BY REGION

SUM UNITS
BY REGION
BY PRODUCT
ON TABLE HOLD AS HOLD_GGSA
END

TABLE FILE HOLD_GGSA
SUM UNITS
BY LOWEST REGION_ST NOPRINT
BY REGION
BY PRODUCT
ON REGION_ST SUBFOOT
"Sub Total <REGION_ST"
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
TYPE=SUBFOOT,
SIZE=8,
$
ENDSTYLE
END