Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Double List Subtotal

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Double List Subtotal
 Login/Join
 
Platinum Member
posted
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,


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 116 | Location: Birmingham, Al | Registered: July 23, 2015Report This Post
Virtuoso
posted Hide Post
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.
 
Posts: 1113 | Location: USA | Registered: January 27, 2015Report This Post
Platinum Member
posted Hide Post
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
 
Posts: 116 | Location: Birmingham, Al | Registered: July 23, 2015Report This Post
Expert
posted Hide Post
ON TABLE SUMMARIZE COLUMN_1 COLUMN_2 AS 'Grand Total'

ON SORT_COLUMN SUBTOTAL COLUMN_1 COLUMN_2 AS 'Total'


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Platinum Member
posted Hide Post
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.


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 116 | Location: Birmingham, Al | Registered: July 23, 2015Report This Post
Expert
posted Hide Post
Use DM to figure it out, lookup GETTOK...


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Virtuoso
posted Hide Post
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.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Platinum Member
posted Hide Post
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
 
Posts: 116 | Location: Birmingham, Al | Registered: July 23, 2015Report This Post
Virtuoso
posted Hide Post
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

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Platinum Member
posted Hide Post
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
 
Posts: 116 | Location: Birmingham, Al | Registered: July 23, 2015Report This Post
Virtuoso
posted Hide Post
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 ...


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Platinum Member
posted Hide Post
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,


WebFOCUS 8.1.05
Windows, All Outputs
 
Posts: 116 | Location: Birmingham, Al | Registered: July 23, 2015Report This Post
Virtuoso
posted Hide Post
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.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Double List Subtotal

Copyright © 1996-2020 Information Builders