Focal Point
[CLOSED] Can you apply straight TOTALS and RECOMPUTES to sub and grand totals

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/5647034296

January 27, 2020, 12:58 PM
RobertF
[CLOSED] Can you apply straight TOTALS and RECOMPUTES to sub and grand totals
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
January 27, 2020, 02:16 PM
MartinY
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
January 27, 2020, 02:45 PM
RobertF
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
January 27, 2020, 02:51 PM
MartinY
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
January 27, 2020, 02:51 PM
BabakNYC
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
January 27, 2020, 04:20 PM
RobertF
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
January 27, 2020, 04:38 PM
BabakNYC
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
January 27, 2020, 05:00 PM
RobertF
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
January 28, 2020, 02:55 AM
Tony A
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 
January 28, 2020, 02:57 AM
Tony A
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 
January 28, 2020, 10:16 AM
RobertF
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
January 28, 2020, 12:15 PM
RobertF
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
January 28, 2020, 12:21 PM
Hallway
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
WF(Prod):8202M1
WF(Test):8202M4
OS/Platform:Win 10
Outputs:All
January 28, 2020, 01:13 PM
RobertF
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
January 29, 2020, 01:01 PM
RobertF
Two steps works but we hope to find an alternate method.


WebFOCUS 8206.08
Windows, All Outputs