I have a request for Subtotals to be added to the results of a double list box. Is this possible?

There are 10 items in the double list box, however, no matter how many are chosen, I only need subtotals on the first two.

Has anyone seen any posts or documentation on how to create such an item?

Thanks,
Pondog

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

So, are you wanting subtotals as added information to the available selections within your d-listbox? Or subtotals within whatever report output is rendered based on the selections from your d-listbox control?

Subtotals within the report that is rendered based on the selection.

I can add "SUBTOTAL" to the end of the BY statement that contains the d-list items, however, it then subtotals every item selected; not just the first item.

ON TABLE SUMMARIZE COLUMN_1 COLUMN_2 AS 'Grand Total'

ON SORT_COLUMN SUBTOTAL COLUMN_1 COLUMN_2 AS 'Total'

Hey Tom,
Thanks for the feedback. My next question would be; How do I discern the column name? Since their selecting from a double-list box, how do I know which column is first?

Here is the BY code that I'm using in my d-list box

BY  &RP_FN02B_Sort2.(BY(<WeekDay,VIEW_PASESSAPPT.PASESSAPPT.APPTWKDYNM>,<ProviderStatus,VIEW_PASESSAPPT.DEPARTMENT_2.DEPTDESC>,<PROVIDER STATUS DESC,VIEW_PASESSAPPT.PROVSTATUS.PROVSTDESC>,<Department,VIEW_PASESSAPPT.DEPARTMENT.DEPTDESC>,<Division,VIEW_PASESSAPPT.DIVISIONS.DIVDESC>,<Provider,VIEW_PASESSAPPT.PROVIDERS.PROVNAME>,<Location,VIEW_PASESSAPPT.SCHLOCATNS.SCHLOCDSC>,<LocationCtgy,VIEW_PASESSAPPT.DEPARTMENT_1.DEPTDESC>,<Clinic,VIEW_PASESSAPPT.CLINICS.CLINICDESC>,<Session,VIEW_PASESSAPPT.PASESSAPPT.SESSNAM>)).Please select sort field(s).   

I tried exchanging the COLUMN_1 for the ampervariable to no avail. I still get a Fieldname not recognized type of error.

Use DM to figure it out, lookup GETTOK...

You were not clear what role the list composed using the double-listbox control served -- columns of the report, or sort keys. Tom (and I) assumed the former, but from the code in your last response I now assume the latter. -- It would have helped if you'd posted some screen shots -- (e.g., Excel output, raw and doctored).

You'll have to use Dialog Manager code (a series of -SETs, utilizing appropriate string-manip functions) to parse out the names from the string-value received as &RP_FN02B_Sort2.

Outline:

• Prepend ' '.
  
• Convert " BY " to "|".
  
• With "|" as the token delimiter, you can then -SET &BY1 = the first token and &BY2 the second,

and (assuming you want all columns summarized at the two highest-level sort-breaks)
ON &BY1 SUBTOTAL
ON &BY2 SUBTOTAL
does the trick.

Tom - Thanks. I'll check it out

j.gross - Being new to WF the thought of a double-list box serving another purpose didn't even occur to me. Next time I'll be sure to include a more definitive example of what I'm trying to achieve. Thanks for the helpful suggestions. They provide a good starting point.

Pondog

Pondog,

If you insert -? & SAVEPRINT into your fex, View Source will show you all the &variables and their contents. From there on you shold be able to chart a course of action. Or, you could post the View Source and we could come up with suggestions.

Hey Daniel,

Thanks for the tip on showing the variables. A quick update:
I only need to show the Subtotal for the first/highest level.
Also, I found some code posted by Francis; "a quick snippet of WevFOCUS Dialog Manager code to parse QUERY STRING" I think it'll help as far as getting the DM commands I need.

I followed your suggestion and have posted what I found in the View Source below. In the code I had selected "Department" and "Provider" with "Department" as my highest level. Of course, it only includes the most pertinent info having stripped off all the extraneous results. I can see what's being returned, my main focus will be on the DM commands since I'm not as proficient with them as I would like to be. Any suggestions are greatly appreciated.

 -DEFAULT &QM=01, &WFFMT='HTML';
-SET &QM_SELECT = IF ALL EQ 'ALL' THEN ' ' ELSE
-                 IF ALL EQ Q1 THEN 'WHERE FM EQ 01 OR 02 OR 03' ELSE
-                 IF ALL EQ Q2 THEN 'WHERE FM EQ 04 OR 05 OR 06' ELSE
-                 IF ALL EQ Q3 THEN 'WHERE FM EQ 07 OR 08 OR 09' ELSE
-                 IF ALL EQ Q4 THEN 'WHERE FM EQ 10 OR 11 OR 12' ELSE
-                 'WHERE FM EQ ALL';
DEFINE FILE VIEW_PASESSAPPT
DF_BookedAppts/D10CB=IF ( VIEW_PASESSAPPT.PASESSAPPT.FYFM LE VIEW_PASESSAPPT.RPTPERIOD.FYFM )
THEN (VIEW_PASESSAPPT.PASESSAPPT.APPTS35HRS + VIEW_PASESSAPPT.PASESSAPPT.APPTSLT35) ELSE 0;
DF_ProvCapacity/D10CB=IF ( VIEW_PASESSAPPT.PASESSAPPT.FYFM LE VIEW_PASESSAPPT.RPTPERIOD.FYFM ) THEN (VIEW_PASESSAPPT.PASESSAPPT.DEFCAPACTY + VIEW_PASESSAPPT.PASESSAPPT.ADDCAPACTY + VIEW_PASESSAPPT.PASESSAPPT.OVERBOOKED) ELSE 0;
DF_SessionCnt/D10CB = IF ( VIEW_PASESSAPPT.PASESSAPPT.FYFM LE VIEW_PASESSAPPT.RPTPERIOD.FYFM )
THEN (VIEW_PASESSAPPT.PASESSAPPT.SESSIONCNT + VIEW_PASESSAPPT.PASESSAPPT.SESSLT35)  ELSE 0;
DF_Arriveds/D10 = IF ( VIEW_PASESSAPPT.PASESSAPPT.FYFM LE VIEW_PASESSAPPT.RPTPERIOD.FYFM ) THEN VIEW_PASESSAPPT.PASESSAPPT.ARRIVEDS ELSE 0;
DF_NoShows/D10 = IF ( VIEW_PASESSAPPT.PASESSAPPT.FYFM LE VIEW_PASESSAPPT.RPTPERIOD.FYFM ) THEN VIEW_PASESSAPPT.PASESSAPPT.NOSHOWS ELSE 0;
DF_Bumpeds/D10 = IF ( VIEW_PASESSAPPT.PASESSAPPT.FYFM LE VIEW_PASESSAPPT.RPTPERIOD.FYFM ) THEN VIEW_PASESSAPPT.PASESSAPPT.BUMPEDS ELSE 0;
DF_Appts35Hrs/D10 = IF ( VIEW_PASESSAPPT.PASESSAPPT.FYFM LE VIEW_PASESSAPPT.RPTPERIOD.FYFM ) THEN VIEW_PASESSAPPT.PASESSAPPT.APPTS35HRS ELSE 0;
DF_Avalfilled/D10 = IF ( VIEW_PASESSAPPT.PASESSAPPT.FYFM LE VIEW_PASESSAPPT.RPTPERIOD.FYFM ) THEN VIEW_PASESSAPPT.PASESSAPPT.AVALFILLED ELSE 0;
DF_Availunfilled/D10 = IF ( VIEW_PASESSAPPT.PASESSAPPT.FYFM LE VIEW_PASESSAPPT.RPTPERIOD.FYFM ) THEN VIEW_PASESSAPPT.PASESSAPPT.AVALUNFILL ELSE 0;
DF_FROZFILLED/D10 = IF ( VIEW_PASESSAPPT.PASESSAPPT.FYFM LE VIEW_PASESSAPPT.RPTPERIOD.FYFM ) THEN VIEW_PASESSAPPT.PASESSAPPT.FROZFILLED ELSE 0;
DF_FROZunFILLED/D10 = IF ( VIEW_PASESSAPPT.PASESSAPPT.FYFM LE VIEW_PASESSAPPT.RPTPERIOD.FYFM ) THEN VIEW_PASESSAPPT.PASESSAPPT.FROZUNFILL ELSE 0;
DF_DefinedCpcty/D10 = IF ( VIEW_PASESSAPPT.PASESSAPPT.FYFM LE VIEW_PASESSAPPT.RPTPERIOD.FYFM ) THEN VIEW_PASESSAPPT.PASESSAPPT.DEFCAPACTY ELSE 0;
DF_AddCpcty/D10 = IF ( VIEW_PASESSAPPT.PASESSAPPT.FYFM LE VIEW_PASESSAPPT.RPTPERIOD.FYFM ) THEN VIEW_PASESSAPPT.PASESSAPPT.ADDCAPACTY ELSE 0;
DF_Overbkd/D10 = IF ( VIEW_PASESSAPPT.PASESSAPPT.FYFM LE VIEW_PASESSAPPT.RPTPERIOD.FYFM ) THEN VIEW_PASESSAPPT.PASESSAPPT.OVERBOOKED ELSE 0;
DF_Bookslots/D10CB = IF ( VIEW_PASESSAPPT.PASESSAPPT.FYFM LE VIEW_PASESSAPPT.RPTPERIOD.FYFM ) THEN VIEW_PASESSAPPT.PASESSAPPT.BOOKSLOTS ELSE 0;
Access_Provider/A26=IF VIEW_PASESSAPPT.SCHPROVDRS.EXCSCHACC NE 'Y' THEN 'Access Providers' ELSE 'Non Access Providers';
END
TABLE FILE VIEW_PASESSAPPT
HEADING
-* " Provider Sessions History - &ACCESSPROV_TEXT"
-* " Report Period:   &QM_TEXT  &FYEAR"
-* " &TITLE "
-* " "
-* " Org:  &ORG  ---  Department:  &DEPTNUM_TEXT  ---  Division:  &DIVNUM_TEXT  ---  Provider Status Ctgy:  &REPCAT_TEXT"
-* " Provider:  &PROVNUM_TEXT  ---  Location Ctgy:  &LOCCAT2_TEXT ---  Sched Location:  &SCHLOCNUM_TEXT  ---  Clinic:  &CLINICNUM_TEXT</2"
SUM
DF_SessionCnt AS 'Total, Sessions'
DF_BookedAppts AS 'Booked, Appts'
DF_BookedAppts NOPRINT
DF_SessionCnt NOPRINT
COMPUTE DF_ApptsperSess/D6CB = DF_BookedAppts /  DF_SessionCnt; AS 'Total Appts, per Total Sess'
DF_Avalfilled/D10CB AS 'Avail, Filled'
DF_Availunfilled/D10CB AS 'Avail, Unfilled'
DF_FROZFILLED/D10CB AS 'Frz, Filled'
DF_FROZunFILLED/D10CB AS 'Frz, Unfilled'
DF_DefinedCpcty/D10CB AS 'Defined, Capacity'
DF_AddCpcty/D10CB AS 'Added, Capacity'
DF_Overbkd/D10CB AS 'OvrBkd'
COMPUTE DF_ProvCpcty/D10CB = (DF_DefinedCpcty + DF_AddCpcty + DF_Overbkd ); AS 'Prov, Capacity'
DF_Bookslots AS 'Booked, Slots'
COMPUTE DF_SchedUtilRate/D9.1% = ( DF_Bookslots /DF_DefinedCpcty ) * 100; AS 'Sched Util, Rate'
COMPUTE DF_CYArrNoshow/D9 = ( DF_Arriveds / ( DF_Arriveds + DF_NoShow )); NOPRINT
COMPUTE DF_CYArrivedRate/D9.1% = ( DF_CYArrNoshow * DF_SchedUtilRate ); AS 'Sched Util, ARR %'
COMPUTE DF_AddedCap%/D9.1% = ( DF_AddCpcty / DF_Bookslots ) * 100; AS 'Added, Cap %'
COMPUTE DF_Overbook%/D9.1% = ( DF_Overbkd / DF_Bookslots ) * 100; AS 'OvrBkd, %'

-*BY  LOWEST Access_Provider NOPRINT
BY  VIEW_PASESSAPPT.DEPARTMENT.DEPTDESC AS 'Department' BY VIEW_PASESSAPPT.PROVIDERS.PROVNAME AS 'Provider'

-*ON Access_Provider RECOMPUTE AS ''
WHERE ( VIEW_PASESSAPPT.PASESSAPPT.APPTWKDYNM NE 'Sat' OR 'Sun' );
WHERE ( VIEW_PASESSAPPT.PROVSTATUS.REPCAT EQ _FOC_NULL );
-*WHERE  (VIEW_PASESSAPPT.DEPARTMENT_2.DEPTDESC EQ &DEPTDESC.(FIND VIEW_PASESSAPPT.DEPARTMENT_2.DEPTNUM,VIEW_PASESSAPPT.DEPARTMENT_2.DEPTDESC IN view_pasessappt).Provider Status:. );
WHERE ( VIEW_PASESSAPPT.PASESSAPPT.FYEAR EQ 2016 );
WHERE VIEW_PASESSAPPT.SCHPROVDRS.PROVRSRCE NE 'Y';
WHERE ( VIEW_PASESSAPPT.PASESSAPPT.ORG EQ 'HSF' );
WHERE ( VIEW_PASESSAPPT.PASESSAPPT.PROVBARDEP EQ _FOC_NULL );
WHERE ( VIEW_PASESSAPPT.PASESSAPPT.PROVBARDIV EQ _FOC_NULL );
WHERE ( VIEW_PASESSAPPT.PASESSAPPT.PROVIDER EQ _FOC_NULL )
AND (  VIEW_PASESSAPPT.SCHLOCATNS.SCHLOCNUM EQ _FOC_NULL )
AND (  VIEW_PASESSAPPT.LOCATIONS.LOCCAT2 EQ _FOC_NULL )
AND (  VIEW_PASESSAPPT.CLINICS.CLINICNUM EQ _FOC_NULL );
-* WHERE  VIEW_PASESSAPPT.SCHPROVDRS.EXCSCHACC EQ 'Y';
-* WHERE  VIEW_PASESSAPPT.SCHPROVDRS.EXCSCHACC NE 'Y';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SUMMARIZE AS 'Total'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
-INCLUDE np_gphc_theme
$ Copyright 1996-2011 Information Builders, Inc. All rights reserved.
TYPE=REPORT, PAGECOLOR=rgb(255 255 255),$
TYPE=REPORT,GRAPHCOLOR='BLACK',GRAPHCOLORNEG='RED',$
TYPE=REPORT,OBJECT=MENU,COLOR='rgb(255 255 255)',HOVER-COLOR='rgb(000 000 000)',BACKCOLOR=rgb(035 031 032),HOVER-BACKCOLOR=rgb(117 149 181),BORDER-COLOR=rgb(255 255 255),$
TYPE=REPORT,OBJECT=CURRENT-ROW,HOVER-BACKCOLOR=rgb(002 060 120), BACKCOLOR=rgb(002 060 120),$
TYPE=REPORT, CALC-LOCATION=TOP, $
TYPE=REPORT,OBJECT=STATUS-AREA, JUSTIFY=LEFT, PAGE-LOCATION=BOTTOM BOTTOM,COLOR='rgb(000 000 000)',BACKCOLOR='rgb(217 217 217)',$
TYPE=REPORT, OBJECT=CALC-AREA,COLOR='rgb(255 255 255)',BACKCOLOR=rgb(000 112 074),$
TYPE=REPORT, ARICONSET=WHITE, ARSTYLESET=bipib,$
UNITS=IN, $
type=report, color=rgb(000 000 000), font='ARIAL', size=9,$ grid=OFF,$  //This was not used for it added white space to margin Barry Solomon
type=pagenum, style=italic, size=6, justify=right,$
type=tabheading,style=bold+italic,size=14,justify=center,color=rgb(000 000 000),$
type=tabfooting,style=bold+italic,size=12,justify=center,border-top=heavy, border-top-color=rgb(035 031 032),$
type=heading, size=10, style=bold,justify=left,color=rgb(000 000 000), backcolor=rgb(255 255 255), $
type=footing,style=bold,size=10,justify=left,$
type=title,backcolor=rgb(000 107 082), color=rgb(255 255 255),style=-underline+bold,$
type=data, backcolor=(rgb(255 255 255) rgb(241 241 241)),$
type=acrosstitle, border-top=light, border-bottom=heavy, border-top-color=rgb(035 031 032), border-bottom-color=rgb(035 031 032), size=9, style=-underline+bold,backcolor=rgb(255 255 255), color=rgb(000 000 000),$
type=acrossvalue,backcolor=rgb(000 107 082),color=rgb(255 255 255),style=bold,$
type=subhead,size=10,style=bold,border-top=heavy, border-bottom=light, border-top-color=rgb(035 031 032), border-bottom-color=rgb(035 031 032),$
type=subfoot,size=10,style=bold,border-top=light, border-bottom=medium, border-top-color=rgb(035 031 032), border-bottom-color=rgb(035 031 032),$
type=subtotal,backcolor=rgb(159 161 140),color=rgb(255 255 255),style=bold,border-top=light,border-top-color=rgb(035 031 032),$
type=grandtotal,backcolor=rgb(000 107 082),color= rgb(255 255 255),style=bold,border-top=light,border-bottom=heavy,border-top-color=rgb(66 70 73),border-top-style=solid,border-bottom-style=double,$
*GRAPH_SCRIPT .... 

Thanks,
Pondog

So it seems the value of the "list of sort keys" parameter in your case is

 VIEW_PASESSAPPT.DEPARTMENT.DEPTDESC AS 'Department' BY VIEW_PASESSAPPT.PROVIDERS.PROVNAME AS 'Provider'  
************************************                    **********************************               

The list, of however many selected sort keys, has two alternating "delimiter" strings: BY (which separates adjacent sort elements) and AS (which separates the column-name from the column-heading text in each sort element. So, to isolate the first (or nth) sort column name, you need two levels of token extraction:


Pick off the first (or nth) token, with " BY " as the effective token delimiter, using the approach I outlined last Wednesday, to get

 VIEW_PASESSAPPT.DEPARTMENT.DEPTDESC AS 'Department'

Then using the same method with " AS " as the effective token delimiter, extract the first token to get

 VIEW_PASESSAPPT.DEPARTMENT.DEPTDESC 

That's the column-name of the first sort-key field; just assign that to &BY1 and code

ON &BY1 ...

Thanks Jack,

I've been going about it backward. I used

-SET &RP_FN02B_STRING = GETTOK(&RP_FN02B_Sort2, &RP_FN02B_Sort2.LENGTH, 3, '', 12, 'A15');  

to get " 'Department' " and have been trying to TRIM/TRIMV to remove the single quotes (with no success) and use " Department " as my item to subtotal.

It didn't occur to me to isolate the actual column name. Thanks for the redirection!

I followed your lead and it works with just the first isolation alone ("BY"). I guess since the "AS" statement effectively renames the column to it's original title it seems transparent.

I was able to finally isolate the second "AS" statement. It took a little thinking since there's an "AS" in the column name, but it now works. Thanks a bunch for pointing me in the right direction. This has definitely been a learning experience!!

Thanks to everyone!!!



Pondog

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

quote:

...there's an "AS" in the column name...

I alluded to that possibility, by including the surrounding blanks:

quote:

Then using the same method with " AS " as the effective token delimiter...

Meaning, replace " AS " (4 chars) with the delimiter character of your choice.

Thanks for the thankyou.