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, PondogThis message has been edited. Last edited by: Pondog,
WebFOCUS 8.1.05 Windows, All Outputs
November 18, 2015, 12:48 PM
CoolGuy
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?
8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
November 18, 2015, 02:02 PM
Pondog
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.
WebFOCUS 8.1.05 Windows, All Outputs
November 18, 2015, 02:22 PM
Tom Flynn
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.
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.
November 18, 2015, 03:50 PM
Pondog
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
WebFOCUS 8.1.05 Windows, All Outputs
November 19, 2015, 02:16 AM
Danny-SRL
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.
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
November 19, 2015, 10:50 AM
Pondog
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
WebFOCUS 8.1.05 Windows, All Outputs
November 22, 2015, 12:28 PM
j.gross
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
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!!!
PondogThis message has been edited. Last edited by: Pondog,
WebFOCUS 8.1.05 Windows, All Outputs
November 23, 2015, 11:44 AM
j.gross
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.