Hi all,
We have written several WebFocus reports that are ready to be added to ReportCaster. Our plan is to have ‘division’ as a burst value. The way we have these reports at the moment, is that if a division has no report, then that division would not get a report for that month. There is a new requirement that we must send a notification report letting the division contact person know that they don’t have a report for that month. What is the best way to handle the no-report situations?

I was thinking to create a division lookup file (perhaps a permanent HOLD file) and write all possible division codes to it. Then do a match on between division lookup file and the report’s input table. If a match is found, continue with running the report; otherwise generate a blank report to let the division know that they don’t have report for the month.

In our first practice run in ReportCaster, the log showed something like the following:

BTP1010         No report for Email-1 with a burst value of Exec.
BTP1010         No report for Email-2 with a burst value of I&R.

But, these would only let the administrator know the outcome of the affected report. The persons for whom no reports were generated would not have any knowledge of the event.

Thank you in advance,


Seyed

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

You might try using

 ON TABLE SET EMPTYREPORT ANSI

This produces a one-line report:

quote:

Produces a single-line report and displays the missing data character or a zero if a COUNT is requested. In each case, &RECORDS will be 0, and &LINES will be 1.

The only problem with this technique is that you cannot also include a message in the HEADING or FOOTING. As far as I remember, all that is shown in the report are the column titles and the one dummy report row. But you may be able to experiment.

Make sure you use the ON TABLE SET EMPTYREPORT ANSI syntax instead of adding SET EMPTYREPORT = ANSI to the top of your program, because this causes empty HOLD files to gain a dummy row! Learned from experience.

Hi Francis,
Thanks as always for your assistance. Here is additional information. Let's say that there are 18 divisions within our organization. If the input file has data for only 15 divisions, the report has no way of knowing about the 3 missing divisions. As a result, I don’t believe setting the EMPTYREPORT to ANSI would work. Moreover, because I know what 18 division codes are, I should be able to store them in a permanent file and make that the division lookup file. I should be then able to do a match between the lookup file and the input file. If a match is found; generate the real report, if not, generate the no-data report.

Thanks again,

Seyed

Yes, that's a good solution.

We use dialog manager and check the &LINES variable. As an example:

-IF &LINES EQ 0 THEN GOTO NO_LETTERS
- ELSE GOTO DAT_FILE;

This way if the report was supposed to have run, data existed , but failed they are informed as well.

Hmmm... this is interesting. I have a sort of similar situation where the burst value is the name of a sales rep. Often one or other of the reps has no report for a given week and ReportCaster shows this as an error in the log. It would be nice to send them something to indicate that there is nothing to report - and also to persuade ReportCaster that there is no error ...

Have you tried first doing a report of all your divisions and hold that as a hold file. Then do your

part 2 - because I hit the post button - is what you wrote. you do the 2 hold file then match and print.

I have this requirement with counties in our state (Dept of Human Services, Oklahoma).

I do it like this:

MATCH FILE CNTYLIST
  SUM MAX.CNTYNM
   BY CNTYID
  RUN
 FILE WHATEVER
  SUM FIELDA FIELDB ...
   BY CNTYID
AFTER MATCH HOLD OLD
END
TABLE FILE HOLD
PRINT ....
   BY CNTYID
....
END

If a county has no data there is still one row for the county with the county id and county name.

This works perfect for me in case if we need a custom message when there are no records.

TABLE FILE CAR
BY COUNTRY AS ALLCTRY
ON TABLE HOLD AS ALLCONTRY
END
SET HOLDLIST = PRINTONLY
SET BYDISPLAY = ON

TABLE FILE CAR
PRINT
CAR
DEALER_COST
RETAIL_COST
BY COUNTRY AS SELCTRY
WHERE COUNTRY IN ('ENGLAND','JAPAN');
ON TABLE HOLD AS SELCONTRY
END

JOIN
LEFT_OUTER ALLCTRY IN ALLCONTRY TO ALL SELCTRY IN SELCONTRY AS J0
END
-RUN

DEFINE FILE ALLCONTRY
FLG/I1 MISSING ON = IF (ALLCTRY NE MISSING AND SELCTRY EQ MISSING) THEN 0 ELSE 1 ;
END
TABLE FILE ALLCONTRY
PRINT
CAR
DEALER_COST
RETAIL_COST
FLG
BY ALLCTRY
BY SELCTRY
ON TABLE HOLD AS FINOUT
END

DEFINE FILE FINOUT
D_CAR/A128V = IF FLG EQ 1 THEN CAR ELSE 'No Records this week';
END
TABLE FILE FINOUT
PRINT
COMPUTE DCOST/D20.2 MISSING ON = IF FLG NE 1 THEN MISSING ELSE DEALER_COST ;
COMPUTE RCOST/D20.2 MISSING ON = IF FLG NE 1 THEN MISSING ELSE RETAIL_COST ;

FLG NOPRINT
BY ALLCTRY
BY SELCTRY NOPRINT
BY D_CAR
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
TYPE=TITLE,
WHEN = FLG NE 1,
SIZE=9,
COLOR='WHITE',
BACKCOLOR= 'WHITE',
STYLE=BOLD,
$
ENDSTYLE
END

Now We can BURST on ALLCTRY

quote:

Originally posted by George Patton:
Hmmm... this is interesting. I have a sort of similar situation where the burst value is the name of a sales rep. Often one or other of the reps has no report for a given week and ReportCaster shows this as an error in the log. It would be nice to send them something to indicate that there is nothing to report - and also to persuade ReportCaster that there is no error ...

Hi George,
I resolved this issue by taking the following steps. Perhaps, what I am showing here helps you too.

1) Created an Excel file containing all the organization’s division codes. Saved the file as an Excel 2013 file, WebFocus Dev Studio 7.6.11 wouldn’t accept any .xlsx file extension.
2) Using the Excel file created in step 1, I created a WebFocus Synonym called TOP50_DIVISION
3) Wrote the following code to build the report

-SET &DATE0 = DATECVT(DATEADD(DATECVT(&YYMD, 'I8YYMD', 'YYMD'), 'M', -1),'YYMD','A8YYMD');
-TYPE &DATE0
-SET &MY_YEAR= EDIT(&DATE0,'9999$$$$');
-TYPE &MY_YEAR
-SET &RPTMONTH = EDIT(&DATE0,'9999$$$$')||'-'||EDIT(&DATE0,'$$$$99$$')||'-01';
-TYPE &RPTMONTH
-*-SET &ECHO=OFF;
SET ASNAMES=ON
-*SET EMPTYREPORT=ON
-* write all division codes that all in the lookup table (TOP50_DIVISION)
-* but not in the input table (EMPLOYEE) to a HOLD file.
MATCH FILE EMPLOYEE
  SUM EMPLOYEE.EMPLOYEE.RPTMONTH
  BY EMPLOYEE.EMPLOYEE.DIVISION AS 'DIV_CD'
  WHERE EMPLOYEE.EMPLOYEE.RPTMONTH EQ DT(&RPTMONTH)
RUN
FILE TOP50_DIVISION
  BY TOP50_DIVISION.TOP50_DIVISION.DIV_CD
AFTER MATCH HOLD NEW-NOT-OLD
END
? HOLD

-SET &MY_WHERE_CLAUSE = ' ';
-SET &MYRECS=&LINES;

-*READ THE DATA in the HOLD file INTO A LOOP
-RUN
-SET &I=0;
-STRT_READ
-SET &I=&I+1;
-READ HOLD &DIV_CD.&I.A10.

-*exit if hold file is empty. Without this check, code will fail with FOC295
-*when the every division code in the OLD is also in the NEW.
-IF &DIV_CD.&I.LENGTH EQ 0 THEN GOTO FINISH1;
-TYPE DIV_CD.&I = &DIV_CD.&I.LENGTH

-*get the length of each division code, would be needed by SUBSTR function
-SET &DIV_CD_LENGTH = ARGLEN(&DIV_CD.&I.LENGTH, &DIV_CD.&I, 'I3');

-SET &DIV_CD_A =
- SUBSTR(&DIV_CD_LENGTH, &DIV_CD.&I,3,&DIV_CD_LENGTH,8,'A9');

-*use the truncate function to remove trailing spaces
-SET &DIV_CD_B = TRUNCATE(&DIV_CD_A);

-* While still in loop, buid the 'Where' clause.
-SET &MY_WHERE_CLAUSE = IF &I EQ 1 THEN ''''||&DIV_CD_B||'''' ELSE &MY_WHERE_CLAUSE ||','||''''||&DIV_CD_B||'''';

-SET &MY_HEADING = 'NO REPORT FOR YOUR DIVISION: ';

-IF &I LT &MYRECS THEN GOTO STRT_READ;

-* change the date format from 'YYYY-MM-DD', to 'Month-YYYY'
-SET &MY_YEAR = GETTOK(&RPTMONTH,10,1,'-',4,'A4');
-SET &MY_MONTH= GETTOK(&RPTMONTH,10,2,'-',2,'A2');
-SET &MONTH_NAME = DECODE &MY_MONTH(01,'January',02,'February',03,'March', 04,'April',05,'May',
-                                   06,'June',07,'July',08,'August',09,'September',10,'October',11,'November',12,'December');
-SET &MY_DATE = &MONTH_NAME |'-'| &MY_YEAR;

-* build the 'no report' page
TABLE FILE TOP50_DIVISION
BY 'TOP50_DIVISION.TOP50_DIVISION.DIV_CD' NOPRINT

ON TOP50_DIVISION.TOP50_DIVISION.DIV_CD PAGE-BREAK
HEADING
" "
" "
"&MY_HEADING  <TOP50_DIVISION.TOP50_DIVISION.DIV_CD "
WHERE DIV_CD IN (&MY_WHERE_CLAUSE);
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='Letter',
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
ENDSTYLE
-FINISH1
END

 

4) Tested this report in the ReportCaster, the test was successful and each person got their report according to the burst values.

Thank you everyone for offering suggestions.

Seyed

Hmmm... That gives me an idea but I think I can do mine in a lot less time with something like:

TABLE FILE ACTIVITIES
COUNT CALLS BY SALESREP
ON TABLE HOLD
END

TABLE FILE HOLD
PRINT SALESREP
WHERE CALLS NE 0
ON TABLE HOLD
END

TABLE FILE ACTIVITIES
PRINT CALLS BY SALESREP 
WHERE SALESREP IN (HOLD)
ON TABLE PCHOLD FORMAT PDF
..etc
END

Probably I don't need two passes to produce the hold list of the people I want. I'm tired - it's been a long 10 days working setting up a new network in Guatemala... home tomorrow thnk god.