Hi all,

I'm currently working on a report that showed how many students we had graduate by academic period. I was able to create this report without any issue but I then got the question from a user if I could also produce this report that would show the cumulative values, not just the single values for that academic period.

In other words, they want to go from this:

Fall 2010 | Fall 2011 | Fall 2012
15 | 20 | 10

To this:

Fall 2010 | Fall 2011 | Fall 2012
15 | 35 | 45

Is there any way to do this using WebFocus? Any tips or ideas are greatly appreciated.

-Egon

This message has been edited. Last edited by: <Kathryn Henning>,

You can do this with the LAST command.

e.g.

TABLE FILE CAR
SUM CNT.BODYTYPE
COMPUTE ACC_CNT/I9 = IF COUNTRY NE LAST COUNTRY THEN CNT.BODYTYPE ELSE CNT.BODYTYPE + LAST ACC_CNT ;
BY COUNTRY
ACROSS SEATS
END

Brilliant! Thank you!

I have a follow up question. I've expended on the model above which works perfectly for the individual rows but I have now expended the report to have two BY columns. I am doing a subtotal on the first BY column and when I do that the recompute seems to continue on adding instead of starting over for that particular row. Any suggestions?

SUM 
     CNT.COMPLETIONS.COMPLETI.PERSON_UID AS 'Term Completers'
     COMPUTE COMP_CNT/I9 = IF ( J6.TESTODS_PERSON_SENSITIVE_RACE_SLOT.REPORTED_RACE NE LAST J6.TESTODS_PERSON_SENSITIVE_RACE_SLOT.REPORTED_RACE ) 
     AND ( COMPLETIONS.COMPLETI.Sport NE LAST COMPLETIONS.COMPLETI.Sport ) THEN CNT.COMPLETIONS.COMPLETI.PERSON_UID 
     ELSE CNT.COMPLETIONS.COMPLETI.PERSON_UID + LAST COMP_CNT; AS 'Cumulative Completers'
BY  COMPLETIONS.COMPLETI.Sport
BY  J6.TESTODS_PERSON_SENSITIVE_RACE_SLOT.REPORTED_RACE AS 'Race/Ethnicity'
ACROSS LOWEST COMPLETIONS.COMPLETI.ACADEMIC_PERIOD_GRAD AS 'Graduation Term'
     
ON COMPLETIONS.COMPLETI.Sport RECOMPUTE
     'COMPLETIONS.COMPLETI.PERSON_UID'
     COMP_CNT AS '*TOTAL'  

Are you able to replicate this in one of the sample files like the CAR file ?

Waz,
Your solution works well when you sort by COUNTRY only. If you sort by CAR also you will have MISSING problems.
I came up with this:

  
SET ASNAMES = ON
TABLE FILE CAR
SUM CNT.BODYTYPE
BY COUNTRY
BY CAR
BY SEATS AS SEATS ROWS 2 OVER 4 OVER 5
ON TABLE HOLD AS WW MISSING OFF
END

TABLE FILE WW
SUM
COMPUTE ACC_CNT/I9 = IF COUNTRY NE LAST COUNTRY THEN BODYTYPE ELSE IF CAR NE LAST CAR THEN BODYTYPE ELSE BODYTYPE + LAST ACC_CNT ;
BY COUNTRY
BY CAR
ACROSS SEATS
ON COUNTRY SUBTOTAL
END

What do you say?

Egon,
It seems to me that SUBTOTAL is what you want, not RECOMPUTE.

Thank you all for the help. Danny I tried your solution and played around with it but it seems that you have to specify the columns.

Due to the parameter driven nature of this particular report the number of columns in the across is dynamic.

Is there a way to use your solution with a dynamic number of across columns?

Thank you!

Hi Egon,
Try this:

  
-* File egon01.fex
SET ASNAMES = ON, HOLDLIST=PRINTONLY
TABLE FILE CAR
BY SEATS
ON TABLE HOLD AS SNUM
END
-RUN 
-SET &S=&LINES;
-SET &O=' ';
TABLE FILE CAR
SUM CNT.BODYTYPE
BY COUNTRY
BY CAR
BY SEATS AS SEATS ROWS 
-REPEAT #GETSEATS FOR &I FROM 1 TO &S;
-READFILE SNUM
&O &SEATS
-SET &O='OVER';
-#GETSEATS
ON TABLE HOLD AS WW MISSING OFF
END
-RUN
TABLE FILE WW
SUM
COMPUTE ACC_CNT/I9 = IF COUNTRY NE LAST COUNTRY THEN BODYTYPE ELSE IF CAR NE LAST CAR THEN BODYTYPE ELSE BODYTYPE + LAST ACC_CNT ;
BY COUNTRY
BY CAR
ACROSS SEATS
ON COUNTRY SUBTOTAL
END

Danny,

This seems to go a little bit over my head, although I'm understanding the basic concepts. I've only been using webfocus for a month now.

When I take this code and run it it's asking for an input for "seats", why is it doing that?

I'm going to work all day Monday on getting this converted into my own data and see if it works.

Egon,
I don't know why you get a prompt fo SEATS. I don't. Could you post the output you get?
As for explaining the concepts behind my code, i'm afraid it is a bit long to write out, but if you want we can have a conversation via Skype.
Good luck for Monday!

If Egon has been using WF for only a month I'm impressed with his progress so far. So I understand his difficulty with Danny's solution, which involves writing to a file and then reading that file back one line at a time via a loop (can you see that Egon?).

I'm not going to suggest any solution, but rather the approach to the problem that I would take: Since the problem is that the columns are dynamic, why not do an initial process that selects the column data, and does an initial sort on the two BY fields.

If a subtotal is required on the first BY field, do a DEFINE that creates a copy field of the number of graduates and use that in a multi-verb request so that it holds the cumulative value required.

eg:

DEFINE FILE CAR
SEATS_2/I4=SEATS;
END

TABLE FILE CAR
SUM SEATS_2
BY CAR
SUM SEATS 
BY CAR 
BY MODEL
END

HOLD that info and then use WAZ's solution for the cumulative values.

We use Waz's technique extensively - but admittedly on static columns.

Egon,
Here is an annotated version:

  
-* File egon01.fex
-* set parameters ASNAMES to change field names; HOLDLIST to hold only explicit fields
SET ASNAMES = ON, HOLDLIST=PRINTONLY
-*
-* Get all values of SEATS and save them in file SNUM
-*
TABLE FILE CAR
BY SEATS
ON TABLE HOLD AS SNUM
END
-RUN
-* save the number of SEATS 
-SET &S=&LINES;
-*
-* initialize variable &O to blank
-SET &O=' ';
-*
-* extract the data. sort by seats. use the ROW option to ensure all values of SEATS are in the WW file. 
TABLE FILE CAR
SUM CNT.BODYTYPE
BY COUNTRY
BY CAR
-* the ROWS option deletes the BY fieldname in the HOLD file - ancient bug. the AS renames the fieldname - ancient workaround.
BY SEATS AS SEATS ROWS 
-* use a REPEAT loop to insert all values of SEATS in the ROWS option
-REPEAT #GETSEATS FOR &I FROM 1 TO &S;
-* the READFILE command reads the SNUM HOLD file and populates the &SEATS variable
-READFILE SNUM
-* between each value of SEATS insert an OVER. the variable &O has an initial value ' ' then it wiil be OVER
&O &SEATS
-SET &O='OVER';
-#GETSEATS
-* use MISSING OFF to replace . with 0 in the HOLD file
ON TABLE HOLD AS WW MISSING OFF
END
-RUN
-* the final report
TABLE FILE WW
SUM
COMPUTE ACC_CNT/I9 = IF COUNTRY NE LAST COUNTRY THEN BODYTYPE ELSE IF CAR NE LAST CAR THEN BODYTYPE ELSE BODYTYPE + LAST ACC_CNT ;
BY COUNTRY
BY CAR
ACROSS SEATS
ON COUNTRY SUBTOTAL
END

Thank you Danny for the annotated version. Very helpful.

George, I will have to take a look at your suggestion later this week.

Danny, I've attached the image below of what I receive when I run the report. Please note that regardless of what I enter for SEATS I get the correct output.

Hi Egon,

I would hazard a guess that the reason you get prompted and Danny does not is that you have amper auto prompting set (either for the BIP procedure or on the server).

The simple solution is to prevent the auto prompt from occurring, so use a defaulted value that will not cause an auto prompt via -

-DEFAULTH &SEATS = 0

The H at the end of DEFAULT is important as otherwise it will still cause a prompt.

Good luck

T

Tony, that solved the prompt issue! Thank you!

Now that I have the sample fully working I will incorporate it into my own report and see if I can get it working, I'll report back soon!

Thank you all!

Egon