Hi, is there a way to sort an accordion report by both the t...

[Ryan Sullivan]

Hi, is there a way to sort an accordion report by both the total sum of the main group and by the sum of the sub group

In the example below, there is currently a descending sort on the dollar amounts by the subgroup (you can see that in the expanded MILEAGE group). But we want to sort the the main group (like Weekly Min, Vacation, etc.) by descending dollar amounts too.

 

Screenshot 2021-12-01 164614.jpg864680 112 KB

 

Does anyone have a solution

Below is the code for reference:

-*COMPONENT=Join_J001

JOIN VWDRIVERDETAILS.VWDRIVERDETAILS.PYT_ITEMCODE IN TRANSMETRIC/VWDRIVERDETAILS

TO UNIQUE PAYTYPE.PAYTYPE.PYT_ITEMCODE IN TRANSMETRIC/PAYTYPE TAG J001 AS J001

END

-*COMPONENT=Define_VWDRIVERDETAILS

DEFINE FILE TRANSMETRIC/VWDRIVERDETAILS

PayType/A25=IF VWDRIVERDETAILS.VWDRIVERDETAILS.PYT_ITEMCODE EQ 'BULL' THEN 'BULLPEN' ELSE IF VWDRIVERDETAILS.VWDRIVERDETAILS.PYT_ITEMCODE IN ( 'DELAY' , 'DETONL' , 'DETEND' , 'BRKONL' ) THEN 'DELAY' ELSE IF VWDRIVERDETAILS.VWDRIVERDETAILS.PYT_ITEMCODE IN ( 'FLAT' , 'SHUTTL' , 'FLTMOV' ) THEN 'FLAT' ELSE IF VWDRIVERDETAILS.VWDRIVERDETAILS.PYT_ITEMCODE EQ 'DHOL' THEN 'HOLIDAY' ELSE IF VWDRIVERDETAILS.VWDRIVERDETAILS.PYT_ITEMCODE IN ( 'HRS' , 'SHTLEH' , 'OT1' ) THEN 'HOURLY' ELSE IF VWDRIVERDETAILS.VWDRIVERDETAILS.PYT_ITEMCODE EQ 'LAYOVR' THEN 'LAYOVER' ELSE IF VWDRIVERDETAILS.VWDRIVERDETAILS.PYT_ITEMCODE IN ( 'EQUBB' , 'DULBB' , 'DCKUNL' , 'DUL' , 'DRVLD' , 'LFTUNL' , 'DRL' , 'DRULST' ) THEN 'LOADING/UNLOADING' ELSE IF VWDRIVERDETAILS.VWDRIVERDETAILS.PYT_ITEMCODE IN ( 'TSM' , 'P-PAYM' , 'WKMIBN' , 'BONUS' ) THEN 'MILEAGE' ELSE IF VWDRIVERDETAILS.VWDRIVERDETAILS.PYT_ITEMCODE IN ( 'MIN' , 'MINACC' , 'MINTO1' , 'MINRT1' , 'GRNTPA' ) THEN 'MINIMUMS' ELSE IF VWDRIVERDETAILS.VWDRIVERDETAILS.PYT_ITEMCODE IN ( 'METRO' , 'P-PAYF' , 'WKND' , 'DBON' , 'FLAHUB' , 'BORO' ) THEN 'PREMIUMS' ELSE IF VWDRIVERDETAILS.VWDRIVERDETAILS.PYT_ITEMCODE IN ( 'STOPS' , 'DULC' , 'STPF' , 'DRPHKR' , 'STPEVT' , 'STPFL' , 'STPEVC' , 'DRPHK' , 'STP-DP' , 'STPFC' , 'DULBBT' , 'DRPHPL' , 'DRPHKD' , 'STPDP' ) THEN 'STOPS' ELSE IF VWDRIVERDETAILS.VWDRIVERDETAILS.PYT_ITEMCODE EQ 'TRNPY' THEN 'TRAINER/TRAINEE' ELSE IF VWDRIVERDETAILS.VWDRIVERDETAILS.PYT_ITEMCODE EQ 'WKLYMN' THEN 'WEEKLY MIN' ELSE IF VWDRIVERDETAILS.VWDRIVERDETAILS.PYT_ITEMCODE IN ( 'ADJP' , 'SUBDED' , 'OT' ) THEN 'zzzExclude'

ELSE IF VWDRIVERDETAILS.VWDRIVERDETAILS.PYT_ITEMCODE EQ 'VACA' THEN 'VACATION' ELSE 'OTHER' ;

END

ENGINE INT CACHE SET ON

SET PAGE-NUM=NOLEAD

SET SQUEEZE=ON

-DEFAULTH &WF_HTMLENCODE=ON;

SET HTMLENCODE=&WF_HTMLENCODE

 

SET HTMLCSS=ON

-DEFAULTH &WF_EMPTYREPORT=ON;

SET EMPTYREPORT=&WF_EMPTYREPORT

 

-DEFAULTH &WF_ARVERSION=1;

SET ARVERSION=&WF_ARVERSION

 

-DEFAULTH &WF_SUMMARY='Summary';

-DEFAULTH &WF_TITLE='WebFOCUS Report';

-DEFAULT &RVP = _FOC_NULL;

-DEFAULT &DIRECTOR = _FOC_NULL;

-DEFAULT &COSTCENTER = _FOC_NULL;

TABLE FILE TRANSMETRIC/VWDRIVERDETAILS

SUM VWDRIVERDETAILS.VWDRIVERDETAILS.PYD_AMOUNT/P21M

BY HIGHEST PayType/A20

BY TOTAL HIGHEST VWDRIVERDETAILS.VWDRIVERDETAILS.PYD_AMOUNT NOPRINT

BY VWDRIVERDETAILS.VWDRIVERDETAILS.PYT_ITEMCODE NOPRINT

BY J001.PAYTYPE.PYT_DESCRIPTION

ACROSS HIGHEST VWDRIVERDETAILS.VWDRIVERDETAILS.PERIODENDING AS 'Period Ending'

WHERE VWDRIVERDETAILS.VWDRIVERDETAILS.SVP EQ &SVP.(OR(FIND VWDRIVERDETAILS.VWDRIVERDETAILS.SVP IN TRANSMETRIC/VWDRIVERDETAILS |FORMAT=A200V,SORT=ASCENDING)).SVP:.;

WHERE VWDRIVERDETAILS.VWDRIVERDETAILS.RVP EQ &RVP.(OR(FIND VWDRIVERDETAILS.VWDRIVERDETAILS.RVP IN TRANSMETRIC/VWDRIVERDETAILS |FORMAT=A200V,SORT=ASCENDING)).RVP:.;

WHERE VWDRIVERDETAILS.VWDRIVERDETAILS.DIRECTOR EQ &DIRECTOR.(OR(FIND VWDRIVERDETAILS.VWDRIVERDETAILS.DIRECTOR IN TRANSMETRIC/VWDRIVERDETAILS |FORMAT=A200V,SORT=ASCENDING)).DIRECTOR:.;

WHERE VWDRIVERDETAILS.VWDRIVERDETAILS.COSTCENTER EQ &COSTCENTER.(OR(FIND VWDRIVERDETAILS.VWDRIVERDETAILS.COSTCENTER IN TRANSMETRIC/VWDRIVERDETAILS |FORMAT=A10V,SORT=ASCENDING)).PC:.;

WHERE VWDRIVERDETAILS.VWDRIVERDETAILS.ASGN_TYPE EQ 'DRV';

WHERE PayType NE 'zzzExclude';

WHERE VWDRIVERDETAILS.VWDRIVERDETAILS.PERIODENDING EQ '11272021' OR '11202021' OR '11132021' OR '11062021' OR '10302021' OR '10232021';

WHERE VWDRIVERDETAILS.VWDRIVERDETAILS.TAXABILITY EQ 'Y';

WHERE VWDRIVERDETAILS.VWDRIVERDETAILS.PYD_STATUS EQ 'REL';

ON TABLE PCHOLD FORMAT HTML

ON TABLE RECOMPUTE

ON TABLE SET CACHELINES 100

ON TABLE SET AUTOFIT OFF

ON TABLE SET GRWIDTH 1

ON TABLE SET EXPANDABLE ON

ON TABLE SET EXPANDBYROWTREE ON

ON TABLE SET DROPBLNKLINE ON

ON TABLE SET STYLE *

INCLUDE=IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/ENIADefault_combine.sty,$

TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, SUMMARY=&WF_SUMMARY.QUOTEDSTRING, $

TYPE=ACROSSTITLE, ACROSS=N1, SIZE=14, STYLE=NORMAL, $

TYPE=TITLE, COLUMN=N1, SIZE=12, STYLE=NORMAL, $

TYPE=TITLE, ACROSSCOLUMN=N1, SIZE=14, STYLE=NORMAL, $

TYPE=TITLE, COLUMN=N2, SIZE=12, STYLE=NORMAL, $

TYPE=ACROSSVALUE, ACROSS=N1, FONT='ARIAL', SIZE=12, $

TYPE=DATA, ACROSSCOLUMN=N1, ALT='Driver Pay BY Type WIP 2_1_1', SIZE=12, TARGET='_blank', FOCEXEC=IBFS:/WFC/Repository/NFI_Dedicated/Drivers/Reports/Driver_Pay_BY_Type_WIP_2_1_1.fex(PERIODENDING=VWDRIVERDETAILS.VWDRIVERDETAILS.PERIODENDING PYT_ITEMCODE=VWDRIVERDETAILS.VWDRIVERDETAILS.PYT_ITEMCODE SVP=&SVP.QUOTEDSTRING RVP=&RVP.QUOTEDSTRING DIRECTOR=&DIRECTOR.QUOTEDSTRING COSTCENTER=&COSTCENTER.QUOTEDSTRING), $

TYPE=DATA, COLUMN=N1, SIZE=12, $

TYPE=DATA, COLUMN=N2, SIZE=12, $

ENDSTYLE

END

 

-RUN

[Martin Yergeau]

Not sure if its going to work, but you can try the below

TABLE FILE TRANSMETRIC/VWDRIVERDETAILS

SUM VWDRIVERDETAILS.VWDRIVERDETAILS.PYD_AMOUNT/P21M

BY TOTAL HIGHEST VWDRIVERDETAILS.VWDRIVERDETAILS.PYD_AMOUNT NOPRINT

BY HIGHEST PayType/A20

BY TOTAL HIGHEST VWDRIVERDETAILS.VWDRIVERDETAILS.PYD_AMOUNT NOPRINT

BY VWDRIVERDETAILS.VWDRIVERDETAILS.PYT_ITEMCODE NOPRINT

BY J001.PAYTYPE.PYT_DESCRIPTION

FYI, BY HIGHEST PayType/A20 is just ordering the PayType alphabetically from Z to A (descending order)

[David Beagan]

I created an accordion report example that I think reproduces your situation. It sorts by the row totals at the subgroup PRODUCT level but not at the group CATEGORY level.

TABLE FILE ggsales

SUM UNITS/I11C

BY HIGHEST CATEGORY

BY TOTAL HIGHEST UNITS NOPRINT

BY PRODUCT

ACROSS HIGHEST DATE

WHERE DATE GE '19970701'

ON TABLE SET EXPANDBYROWTREE ON

END

 

 

I tried putting in another BY TOTAL HIGHEST UNITS but that didnt work. So it seems like a hold file is needed.

I created a separate a separate hold file that calculates the dollar amounts for the CATEGORY and incorporate that back into the main report with DB_LOOKUP to get a defined field named Order ( alternatively I could have used a join instead of DB_LOOKUP ). Then I can use BY HIGHEST UNITS to get the desired sorting.

TABLE FILE ggsales

BY TOTAL HIGHEST UNITS

BY CATEGORY

ON TABLE SET HOLDLIST PRINTONLY

ON TABLE HOLD AS ORDER

END

 

DEFINE FILE ggsales

Order/I11 = DB_LOOKUP(ORDER,CATEGORY,CATEGORY,UNITS);

END

 

TABLE FILE ggsales

SUM UNITS/I11C

BY HIGHEST Order NOPRINT

BY HIGHEST CATEGORY

BY TOTAL HIGHEST UNITS NOPRINT

BY PRODUCT

ACROSS HIGHEST DATE

WHERE DATE GE '19970701'

ON TABLE SET EXPANDBYROWTREE ON

END

 

 

image.png798236 7.21 KB

 

The following code was useful for showing the numbers to validate the sorting is correct:

TABLE FILE ggsales

SUM UNITS/I11C

BY HIGHEST CATEGORY

BY TOTAL HIGHEST UNITS NOPRINT

BY PRODUCT

ACROSS HIGHEST DATE

ROW-TOTAL

WHERE DATE GE '19970701'

ON CATEGORY RECOMPUTE

ON TABLE RECOMPUTE

ON TABLE SET STYLE *

TYPE=SUBTOTAL, BACKCOLOR=RGB(240 245 245), $

END

[Sarah Buccellato]

Hi Ryan, were Martin and Davids posts helpful