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     [CLOSED] Can you apply straight TOTALS and RECOMPUTES to sub and grand totals

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Can you apply straight TOTALS and RECOMPUTES to sub and grand totals
 Login/Join
 
Master
posted
Now on App Studio, wf8206...wrote a report thats sorts by provider and shows Encounters Per Session etc --see below....

TABLE FILE HOLD_PROVDATA_FINAL
SUM
COMPUTE EncPerSess/D12.2 = ( ( HOLD_PROVDATA_FINAL.SEG01.Sess_NbrAppts / HOLD_PROVDATA_FINAL.SEG01.Sess_HrsApptLength ) * 36 ) / 9;
COMPUTE EncPerHour/D12.2 = HOLD_PROVDATA_FINAL.SEG01.Sess_NbrAppts / HOLD_PROVDATA_FINAL.SEG01.Sess_HrsApptLength;
COMPUTE WrvuPerUnit/D12.2 = HOLD_PROVDATA_FINAL.SEG01.wrvus . HOLD_PROVDATA_FINAL.SEG01.units;
BY LOWEST HOLD_PROVDATA_FINAL.SEG01.RPTBANBR
BY LOWEST HOLD_PROVDATA_FINAL.SEG01.PROVIDERNAME

If I add column totals with the RECOMPUTE option the grand total is handled just as the detail. However they do not want this. For the grand total they really want the SUM of the EncPerSess and EndPerHour column but want the WrvuPerUnit to be recomputed.

Right or wrong they essentially want the session averages computed at the detail level but then added in the grand total. However the WrvuPerUnit must be recomputed.

Can you somehow mix and match what happens at a sub or grand total?

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


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Virtuoso
posted Hide Post
Maybe you should go with building the report's data rows yourself.
Meaning that you create details rows, sub-total rows and grand-total row just the way you need each of them.
Then merge into one file to then print them in the proper order in the final report.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Master
posted Hide Post
quote:
grand-total r

We got this tool to get away from that! Plus this report may have multiple breaks. I'll open a case. Hopefully there is a way to set the recomputed or sum by columns....Thanks!


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Virtuoso
posted Hide Post
quote:
Hopefully there is a way to set the recomputed or sum by columns....

AFAIK not yet...


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Virtuoso
posted Hide Post
I'm having a hard time envisioning what this output will look like. Can you show us what you need using a sample file?


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Master
posted Hide Post
is there a way to upload a picture????

Short of that, lets change things slightly for illustrative purposes. Lets report:


RptBaNbr (by)
Provider (by)
Appts (sum)
Hrs (sum)
AvgApptsPerHr (compute: Appts/Hrs)
wRVUs (sum)
visits (sum)
wRVUsPerVisit (compute: wRVUs / Visits)


The subtotal at RptBaNbr/Provider (or at any level that may one day be added) must show all the above fields as follows:

RptBaNbr (by)
Provider (by)
Appts (summed)
Hrs (summed)
AvgApptsPerHr (summed)
wRVUs (summed)
visits (summed)
wRVUsPerVisit (computed as Total wRVUs / Total Visits)


The dilemma that arises is that the AvgAppsPerHr is computed at the detail (provider) level but instead must be summed at the subtotal level. Its a sum of averages.



RptBaNbr....Provider...Appts...Hrs...AvgEncPerSession...Wrvus...Visits...WrvuPerUnit
30302.......Jones......40......20...........2............10......1.5........6.67
30302.......Smith......19......4.75.........4............12......1.83.......6.56
30302.......Johnson....10......6............1.67.........17......8..........2.1
*****TOTAL.............69......30.75........7.67.........39......11.33......3.44


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Virtuoso
posted Hide Post
Use RECOMPUTE on the BY field and SUBTOTAL SUM. for the ON TABLE

Something like this:
  
TABLE FILE retail_samples/wf_retail
SUM GROSS_PROFIT_US
REVENUE_US
COMPUTE REV_SQFEET/D12.2=REVENUE_US / AREA_SQ_FT ;
BY PRODUCT_CATEGORY RECOMPUTE AS 'Subtotal:'
BY PRODUCT_SUBCATEG
ON TABLE PCHOLD FORMAT HTML
ON TABLE SUBTOTAL SUM. AS 'Total:'
END


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Master
posted Hide Post
so the subtotal will simply SUM all 3 of these?

But in my case I do not want my subtotal to sum everything. I still need the wrvus per visits computed as it were on the detail provider line: total wrvus / total visits....however I need sessions per hour TOTALED at the subtotal line whereas it had been computed for the provider row.

See my example: Column = AvgEncPerSession...note how the provider rows are calculated vs the TOTAL....similarly look at how wrvus per visit are calculated on the provider row vs the TOTAL.

Thanks!


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Expert
posted Hide Post
ON TABLE SUBTOTAL (list of column names you want subtotalled)
ON TABLE RECOMPUTE (column name that you want recomputed)


T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Expert
posted Hide Post
You could have gleaned this from documentation.

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Master
posted Hide Post
actually I was flipping thru the 8206 doc that IBI sent...looking for an answer but saw something that lead me to believe this might not be possible. Perhaps I searched on something that took me down the wrong path....


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Master
posted Hide Post
ok, we just played with this. We see nothing in the 8206 doc that allows one to believe this is possible...but knowing webfocus there us usually a way.

We tried to apply a subtotal row and then (still using the GUI, right click options) went in and tried to set specific columns to SUMS or AVEs. However Average of a row is not the same recomputing it. So that did not work.


Here is the code generated by the GUI..after adding the subtotal then saying its a recompute and then trying to override specific columns to be SUMS. It seems the recompute, once seen prevails; ie EncPerSess is not the sum of the computed values that preceded it.

ON HOLD_PROVDATA_FINAL.SEG01.RPTBANBR RECOMPUTE
wRVUPerVisit AS '*TOTAL'
ON HOLD_PROVDATA_FINAL.SEG01.RPTBANBR SUBTOTAL
SUM. EncPerSess AS '*TOTAL'

This is really the first time this issue ever arose for us. If we are missing something, let us know.


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Master
posted Hide Post
quote:
COMPUTE WrvuPerUnit/D12.2 = HOLD_PROVDATA_FINAL.SEG01.wrvus . HOLD_PROVDATA_FINAL.SEG01.units;


I don't understand what this COMPUTE is. ↑

Is it supposed to be multiplying the two fields?

To recompute one COMPUTE field, but sum the others, you will need to
1. Hold the report with the RECOMPUTEd fields that you want to sum, and include the the two fields that you are going to want recomputed in the total.
2. Then do a request on the hold file and do the COMPUTE that you want recalculated in that file.

Something like this:
 
TABLE FILE HOLD_PROVDATA_FINAL
SUM
   COMPUTE EncPerSess/D12.2 = ( ( HOLD_PROVDATA_FINAL.SEG01.Sess_NbrAppts / HOLD_PROVDATA_FINAL.SEG01.Sess_HrsApptLength ) * 36 ) / 9;
   COMPUTE EncPerHour/D12.2 = HOLD_PROVDATA_FINAL.SEG01.Sess_NbrAppts / HOLD_PROVDATA_FINAL.SEG01.Sess_HrsApptLength;
   HOLD_PROVDATA_FINAL.SEG01.wrvus
   HOLD_PROVDATA_FINAL.SEG01.units;
BY LOWEST HOLD_PROVDATA_FINAL.SEG01.RPTBANBR
BY LOWEST HOLD_PROVDATA_FINAL.SEG01.PROVIDERNAME
ON TABLE HOLD AS HOLD_COMPUTES
END
-RUN

TABLE FILE HOLD_COMPUTES
SUM EncPerSess/D12.2
    EncPerHour/D12.2
    COMPUTE WrvuPerUnit/D12.2 = wrvus / units;
BY LOWEST RPTBANBR
BY LOWEST PROVIDERNAME
ON TABLE RECOMPUTE
END

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


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report This Post
Master
posted Hide Post
quote:
COMPUTE WrvuPerUnit/D12.2 = HOLD_PROVDATA_FINAL.SEG01.wrvus . HOLD_PROVDATA_FINAL.SEG01.units;



that's should have been:
COMPUTE WrvuPerUnit/D12.2 = HOLD_PROVDATA_FINAL.SEG01.wrvus / HOLD_PROVDATA_FINAL.SEG01.units;

As far as the two step solution..I will think this thru...hate to have to have a second step but perhaps that will work.


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Master
posted Hide Post
Two steps works but we hope to find an alternate method.


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report 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     [CLOSED] Can you apply straight TOTALS and RECOMPUTES to sub and grand totals

Copyright © 1996-2020 Information Builders