I'm using 7.6.4.

I have a report that outputs X number of detail rows, and then in the sub-footer, gives me the total number of rows.

I'm curious if there is a way to make that total a formula in excel rather than a text string, so that if I delete rows manually, the total would update itself.

Is this possible in WebFOCUS?

My code:

DEFINE FILE SOC_CLR_CLEARANCEREQUEST
FOCUS_START/YYMD WITH LAST_NAME=19001231;
RECCOUNTER/I8 = 1;

-*Investigation Opened
INVSTOPEN1/I7=INVESTIGATION_OPEN;
INVSTOPEN2/I7=2415385;
INVEST_OPEN/MDYY=FOCUS_START+(INVSTOPEN1-INVSTOPEN2);
INVEST_OPEN2/YYMD=FOCUS_START+(INVSTOPEN1-INVSTOPEN2);

-*Investigation Closed
INVSTCLSD1/I7=INVESTIGATION_CLOSED;
INVSTCLSD2/I7=2415385;
INVEST_CLSD/MDYY=FOCUS_START+(INVSTCLSD1-INVSTCLSD2);
INVEST_CLSD2/YYMD=FOCUS_START+(INVSTCLSD1-INVSTCLSD2);

-*Cycle Days Calculation
CYCLE_DAYS/I8= IF INVESTIGATION_OPEN EQ '' THEN 0 ELSE DATEDIF(INVEST_OPEN2, INVEST_CLSD2, 'D');

-*Number of Days Closed calculation
DAYSCLSD/I8= DATEDIF(INVEST_CLSD2, &TODAY , 'D');
END

TABLE FILE SOC_CLR_CLEARANCEREQUEST
PRINT
CLEARANCE_REQUEST_ID_ AS 'Clearance Request ID'
REQUESTER_LOGIN_NAME_ AS 'Employee LMPeople ID'
REQUESTER_NAME_ AS 'Employee Name'
CATEGORY AS 'Clearance'
TYPE AS 'Type'
INVESTIGATION_TYPE AS 'Investigation Type'
INVEST_OPEN AS 'Investigation Opened Date'
INVEST_CLSD AS 'Investigation Closed Date'
CYCLE_DAYS AS 'Cycle Days'

RECCOUNTER NOPRINT

BY INVESTIGATION_TYPE NOPRINT
BY CLEARANCE_REQUEST_ID_ NOPRINT

ON TABLE RECAP
DAYS_AVG/D11.2 = CYCLE_DAYS / RECCOUNTER;

ON TABLE SUBFOOT
""
"Total Records = "Average Cycle Time (Days)=
-*Days Closed Criteria
WHERE INVESTIGATION_CLOSED NE ''
WHERE DAYSCLSD LE 30
WHERE INVEST_CLSD LT &TODAY2

ON TABLE SET STYLE *
TYPE=TITLE, COLOR=NAVY, STYLE=BOLD+UNDERLINE, $
TYPE=REPORT, TITLETEXT= &FSTTITLE, $
TYPE=GRANDTOTAL, COLOR=NAVY, SIZE=10, STYLE=BOLD, $
TYPE=SUBTOTAL, COLOR=NAVY, SIZE=9, STYLE=BOLD, $
ENDSTYLE

ON TABLE PCHOLD FORMAT EXL2K
END

Production - 7.6.4
Sandbox - 7.6.4

You need to use FORMAT EXL2K FORMULA

see: http://documentation.informationbuilders.com/masterinde...snf52/rep_lang29.htm

The suggestion to use EXL2K FORMULA is a nice one, but will not solve the problem you have.
It only would bring a formula for (sub)totals and not for text strings.

Maybe I did nto ask it right.

Instead of the subfooter displaying "Total = 100", I want the subfooter to display "Total = " & Count(A1:A101). (Where Count(A1:A101) is the number of rows between row 1 and 100).

I can do this once the data gets to Excel, but I was hoping that I could get WebFOCUS to do that for me.

As Frank correctly states, EXL2K FORMULA will not generate formulas for SUBFOOT, SUBHEAD, etc (which are text strings and not report columns). EXL2K FORMULA will generate formulas for SUBTOTAL, RECOMPUTE, SUMMARIZE, etc. which act on the report columns.

ok, thank you. That makes sense now.

(too much caffiene today....)

it is possible to pass the formula as a text string. In your define set up an alpha field something like:

TTL/A20='=SUM(A1:A'|CTR|')';

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

If you have a login to techsupport, have a look at this, it details how to add custom formulas to the bottom of the report.


Another thing, you may want to try (its a bit wacky) is to produce the exl2k document with a hold and post process the file. Where the formula needs to go, put in some unique text.
Then process the file (perhaps with DM) and replace the text with you formula (have alook at how the formulas are stored in the exl2K file).
Then use

SET HTMLFORMTYPE = XLS
-HTMLFORM {exl2k file}

to return the file to the PC.

That article is interesting, but I'm not sure how to get it to apply to subfoot sections.

Because I am grouping on a field, I am never sure how many rows will have to be navigated through to find the first and last record in a group, and each group may have a different count, therefore the formula would be different for each.

CWM,
See what I have come up with:

  
SET HOLDLIST=PRINTONLY
TABLE FILE CAR
PRINT SALES 
 COMPUTE NUM/I5=IF COUNTRY EQ LAST COUNTRY THEN LAST NUM + 1 ELSE LAST NUM + 2; NOPRINT
 COMPUTE STRT/I5=IF LAST STRT EQ 0 THEN 2 ELSE IF LAST COUNTRY EQ COUNTRY THEN LAST STRT ELSE LAST NUM + 2; NOPRINT
 COMPUTE XL/A20 = '=SUM(B' | EDIT(STRT) | ':B' | EDIT(NUM) | ')'; NOPRINT
BY COUNTRY
ON COUNTRY SUBFOOT
"<COUNTRY<XL"
ON TABLE PCHOLD FORMAT EXL2K FORMULA
ON TABLE SET STYLE *
TYPE=SUBFOOT, HEADALIGN=BODY, $
ENDSTYLE
END

If you run this you will see that the formula appears in the right place, but EXCEL still views it as text. Is there a way to 'convince' excel that this is a formula? Maybe somebody has an idea?

It has been a long time since I have done this, but I know that if you click on the formula it will execute. I think it will if you save the report as .xls and reopen it.

PB,
You are right. If you set the cursor on the cell where the formula is and then click of the 'f' in the bar then Excel will transform the text to a formula. But I am not sure that is what is wanted...

quote:

I am never sure how many rows will have to be navigated

If you use this technique, you will need to calculate the row number from the data. Just create a counter and use it to determine which row it will be on.


Try this.

DEFINE FILE CAR
CNTR/I9 WITH MODEL= LAST CNTR + 1 ;
FLD1/A200 WITH MODEL='<tr><TD>Total Records</TD><TD></TD><TD></TD><TD x:fmla="=ROW()-3">0</TD></tr>';
END
TABLE FILE CAR
PRINT CNTR
BY COUNTRY
BY CAR
BY MODEL
WHERE COUNTRY EQ 'ITALY'
ON TABLE PCHOLD FORMAT EXL2K FORMULA
FOOTING
"<FLD1"
END

You have to take into account the headings, titles, footings.

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

Danny,

Did you try saving the file as .xls and then opening it?

I used a variation of Waz's code and got what I needed. Very cool stuff!

This is working great!

To pass the formula out to Excel I used:

 
ON TABLE RECAP
DAYS_AVG2/D11.2 = CYCLE_DAYS / RECCOUNTER;

-*Record Total Formula
FLD1/A200='<tr><td>Total Requests =</td>';
FLD2/A250='<td align=left x:num x:fmla="=COUNT(I10:I' | EDIT(SUM.RECCOUNTER + 9) | ')">0</td></tr>';

ON TABLE SUBFOOT
""
"<FLD1<FLD2"

 

Now I'm trying to get the same solution to work on a report that is grouped.

The problem with this for grouped reports is that the start and finish cells are always different for each group.

It shouldn't be that difficult, just need to work our where your rows start and end.