We have a report that page breaks on each of 6 different regional office codes - and this runs in ReportCaster (and bursts on each office code and is emailed to that office). We want the entire report to print - whether there's data or not for each office. An example would be office1 has data and prints normally, office2 has no data but prints a friendly nodata message, office3 has data and prints normally...and so on.

We do have an empty.fex set up for single reports. First, here's the report procedure (the empty_report.fex follows after this):
SET NODATA = ' '
SET EMPTYREPORT = ON
SET BYDISPLAY=ON

-*-------------------------------------------------------------------------------
-* Check for No Data
-*-------------------------------------------------------------------------------
TABLE FILE VWAGED_CLAIMS_REPORT
PRINT DOC_#
WHERE RECORDLIMIT IS 1
WHERE READLIMIT IS 1
WHERE BRANCH EQ 'ABC'
ON TABLE HOLD
END
-RUN
-IF &RECORDS EQ 0 GOTO :NO_DATA ;

TABLE FILE VWAGED_CLAIMS_REPORT
PRINT
VWAGED_CLAIMS_REPORT.VWAGED_CLAIMS_REPORT.DOC_#
VWAGED_CLAIMS_REPORT.VWAGED_CLAIMS_REPORT.TYPE AS 'Doc Type'
VWAGED_CLAIMS_REPORT.VWAGED_CLAIMS_REPORT.PARCEL_#
VWAGED_CLAIMS_REPORT.VWAGED_CLAIMS_REPORT.PARCEL_USER
VWAGED_CLAIMS_REPORT.VWAGED_CLAIMS_REPORT.NODE AS 'Parcel Node'
BY VWAGED_CLAIMS_REPORT.VWAGED_CLAIMS_REPORT.BRANCH NOPRINT
BY LOWEST VWAGED_CLAIMS_REPORT.VWAGED_CLAIMS_REPORT.CLAIM_#
BY HIGHEST VWAGED_CLAIMS_REPORT.VWAGED_CLAIMS_REPORT.DAYS_OPEN

ON VWAGED_CLAIMS_REPORT.VWAGED_CLAIMS_REPORT.BRANCH PAGE-BREAK
HEADING
" "
" "
"Aged Claims Report"
"Branch: FOOTING
"Report Date:<+0>&DATEMDYY<+0> &FOCFEXNAME"
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = mcg_image_pdf,
$
TYPE=DATA,
COLUMN=N6,
JUSTIFY=CENTER,
$
TYPE=DATA,
COLUMN=N3,
JUSTIFY=CENTER,
$
TYPE=FOOTING,
LINE=1,
OBJECT=TEXT,
ITEM=1,
WIDTH=1.000,
$
TYPE=FOOTING,
LINE=1,
OBJECT=TEXT,
ITEM=2,
JUSTIFY=LEFT,
WIDTH=1.000,
$
TYPE=FOOTING,
LINE=1,
OBJECT=TEXT,
ITEM=3,
JUSTIFY=LEFT,
WIDTH=1.000,
$
TYPE=FOOTING,
LINE=1,
OBJECT=TEXT,
ITEM=4,
JUSTIFY=LEFT,
WIDTH=.500,
$
TYPE=FOOTING,
LINE=1,
OBJECT=TEXT,
ITEM=5,
WIDTH=1.000,
$
TYPE=FOOTING,
LINE=1,
OBJECT=FIELD,
ITEM=1,
JUSTIFY=LEFT,
WIDTH=1.000,
$
ENDSTYLE
END


-*--------------------------------------------------------------------------------------------------
-* No Data Section:
-* Display a message in case of no data using an Empty Report.
-*
-*--------------------------------------------------------------------------------------------------
-:NO_DATA
-*
-SET &REPORT_NAME = 'Aged Claims Report' ;
-INCLUDE empty_report.fex



-EXIT

And the empty_report.fex:

-DEFAULT &REPORT_NAME = 'My Report' ;
TABLE FILE CAR
PRINT
COMPUTE DUMMY/A1 = ' '; AS ' '
BY LOWEST MODEL NOPRINT AS ' '
HEADING
" "
"Data not available for Report:"
" &REPORT_NAME"
" "
"<25>Date: <+0>&DATE<+0> - Time:<+0> &TOD<+0>"
WHERE RECORDLIMIT EQ 1
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = mcg_no_image,
$
TYPE=DATA,
COLUMN=N2,
BORDER-TOP=OFF,
BORDER-BOTTOM=OFF,
BORDER-LEFT=OFF,
BORDER-RIGHT=OFF,
BACKCOLOR='NONE',
$
TYPE=TITLE,
COLUMN=N2,
BORDER-TOP=OFF,
BORDER-BOTTOM=OFF,
BORDER-LEFT=OFF,
BORDER-RIGHT=OFF,
BACKCOLOR='NONE',
$
TYPE=HEADING,
BORDER-TOP=LIGHT,
BORDER-BOTTOM=LIGHT,
BORDER-LEFT=LIGHT,
BORDER-RIGHT=LIGHT,
BORDER-TOP-COLOR='RED',
BORDER-BOTTOM-COLOR='RED',
BORDER-LEFT-COLOR='RED',
BORDER-RIGHT-COLOR='RED',
$
TYPE=HEADING,
LINE=2,
OBJECT=TEXT,
ITEM=1,
SIZE=20,
$
ENDSTYLE
END

I have tried several solutions to get this to work for multiple offices, but to no avail. Any ideas? Thanks very much in advance.

PS I'm fairly new to WF Reporting.

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

Hi,
The above requirement can be achieved by triggering the report caster job seperately for each office and passing office value to a variable in report caster.
The above process requires:
1. List of offices for which report needs to be generated.
2. A report caster job with parameter(in order to hold the office value passed.)
3. A fex file containing DSTRUN function of webfocus.
DSTRUN is used to trigger the report caster job on demand.
Details regarding triggering a RC job using DSTRUN can be easily searched on the forum.

Just thinking out loud here. With Bursting in RC, you use the first BY in your TABLE FILE. Instead of checking for no data first and using a GOTO, why not do your table file like normal for Bursting then at the end do this

-IF &RECORDS EQ 0 GOTO :NO_DATA ;  

-:NO_DATA 
-HTMLFORM BEGIN
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>No Data Report</title>
</head>
<BODY bgColor=&BGCOLOR >
**** There is no data ****
</pre>
</pre>
</BODY>
</HTML>
-HTMLFORM END

 

Maybe use an HTMLFORM in your procedure. The only question would be the output of your RC report. Just a thought to look into.

Hi,
@JVB's suggestion is obviously a good "controlled" way to ensure that a report gets produced for every branch.

The other way would be to have a "control file" which you then JOIN to your data file. This "control file" would contain a list of all branches for which a report needs to be created.

There are many ways that you could do this, depending on DB type, number of records etc.

e.g. something along these lines ....

-* *** Produce Control File which holds a count of doc numbers per branch

DEFINE FILE VWAGED_CLAIMS_REPORT
TEMP_COUNT/P12 WITH DOC_# = TEMP_COUNT + 1 ;
END

TABLE FILE VWAGED_CLAIMS_REPORT
SUM TEMP_COUNT
COMPUTE TEMP_MESSAGE/A80 = IF TEMP_COUNT EQ 0 THEN 'Data not available for Report: &REPORT_NAME ' ELSE ' ' ;

-* *** Here you would need to specify each branch code to be included
BY BRANCH ROWS
'ABC' OVER
'DEF' OVER
'XYZ'

ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS CTRLFILE FORMAT ALPHA
END
-RUN
-IF &RECORDS EQ 0 GOTO :NO_DATA ;

-* *** JOIN from the CTRLFILE to _ALL_ records in VWAGED_CLAIMS_REPORT and SET ALL=PASS to retrieve all branches

JOIN CLEAR *
JOIN BRANCH IN CTRLFILE TO ALL BRANCH IN VWAGED_CLAIMS_REPORT AS J1
SET ALL=PASS
-RUN

-* *** Note: TABLE from CTRLFILE
TABLE FILE CTRLFILE
PRINT
 VWAGED_CLAIMS_REPORT.VWAGED_CLAIMS_REPORT.DOC_#
 VWAGED_CLAIMS_REPORT.VWAGED_CLAIMS_REPORT.TYPE AS 'Doc Type'
 VWAGED_CLAIMS_REPORT.VWAGED_CLAIMS_REPORT.PARCEL_#
 VWAGED_CLAIMS_REPORT.VWAGED_CLAIMS_REPORT.PARCEL_USER
 VWAGED_CLAIMS_REPORT.VWAGED_CLAIMS_REPORT.NODE AS 'Parcel Node'

-* *** Note: CTRLFILE branch field
BY CTRLFILE.BRANCH NOPRINT
BY LOWEST VWAGED_CLAIMS_REPORT.VWAGED_CLAIMS_REPORT.CLAIM_#
BY HIGHEST VWAGED_CLAIMS_REPORT.VWAGED_CLAIMS_REPORT.DAYS_OPEN

ON CTRLFILE.BRANCH PAGE-BREAK
HEADING
" "
" "
"Aged Claims Report"
"Branch: <CTRFILE.BRANCH "
-* *** Here we would include our message field which would be blank if there are records
"<CTRFILE.TEMP_MESSAGE "
.
.
.

Thanks so much to all of you for your generous efforts at solving my issue. Here's what I ended up with (jpg of result attached):

1.
I had to change the SQL view that supplies data to the synonym by adding a 'union all' for data that DOES NOT qualify:

ALTER VIEW [dbo].[vwAged_Claims_Report]
AS
SELECT BR.A_BRANCH AS Branch, F.A_CLAIM_NO AS [Claim #], DATEDIFF(DD, DATEADD(DD, DATEDIFF(DD, 0, BP.REC_DATE), 0), DATEADD(DD, DATEDIFF(DD, 0,
GETDATE()), 0)) AS [Days Open], D.ISN AS [Doc #], DT.DOC_TYPE AS Type, BP.ISN AS [Parcel #], U.USER_ID AS [Parcel User], N.TITLE AS Node
FROM dbo.MCG_CLM_FOLDER AS F inner JOIN
dbo.MCG_CLM_BRANCH AS BR ON BR.ISN = F.BRANCH_ISN INNER JOIN
dbo.MCG_CLM_PARCEL AS BP ON BP.FOLDER_ISN = F.ISN INNER JOIN
dbo.MCG_CLM_DOCUMENT AS D ON D.PARCEL_ISN = BP.ISN INNER JOIN
dbo.MCG_CLM_DOCTYPE AS DT ON DT.ISN = D.DOCTYPE_ISN INNER JOIN
dbo.MCG_CLM_NODE AS N ON N.ISN = BP.NODE_ISN INNER JOIN
dbo.MCG_USER AS U ON U.ISN = BP.USER_ISN
WHERE (SUBSTRING(F.A_CLAIM_NO, 1, 3) = '999') AND (DATEDIFF(DD, DATEADD(DD, DATEDIFF(DD, 0, BP.REC_DATE), 0), DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0))
>= 7)

union all

select a_branch as branch, ' ' as [Claim #], 0 as [Days Open], ' ' as [Doc #], ' ' as [Type], ' ' as [Parcel #], ' ' as [Parcel User], ' ' as node from dbo.MCG_CLM_BRANCH where a_branch not in (
SELECT a_branch
FROM dbo.MCG_CLM_FOLDER AS F inner JOIN
dbo.MCG_CLM_BRANCH AS BR ON BR.ISN = F.BRANCH_ISN INNER JOIN
dbo.MCG_CLM_PARCEL AS BP ON BP.FOLDER_ISN = F.ISN INNER JOIN
dbo.MCG_CLM_DOCUMENT AS D ON D.PARCEL_ISN = BP.ISN INNER JOIN
dbo.MCG_CLM_DOCTYPE AS DT ON DT.ISN = D.DOCTYPE_ISN INNER JOIN
dbo.MCG_CLM_NODE AS N ON N.ISN = BP.NODE_ISN INNER JOIN
dbo.MCG_USER AS U ON U.ISN = BP.USER_ISN
WHERE (SUBSTRING(F.A_CLAIM_NO, 1, 3) = '999') AND (DATEDIFF(DD, DATEADD(DD, DATEDIFF(DD, 0, BP.REC_DATE), 0), DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0))
>= 7)
group by a_branch )

2.
Add a define in the WebFocus procedure for the 'no data'[IMG] header (testhdr)
DEFINE FILE VWAGED_CLAIMS_REPORT
TESTHDR/A60 MISSING ON =IF DAYS_OPEN EQ 0 THEN 'NO UN-INDEXED PARCELS OVER 7 DAYS WERE FOUND ' ELSE '';
END

3.
Changed the header slightly by adding the defined 'no data' field to it:
TABLE FILE VWAGED_CLAIMS_REPORT
PRINT
DOC_#
TYPE AS 'Doc Type'
PARCEL_#
PARCEL_USER
NODE AS 'Parcel Node'
BY BRANCH NOPRINT
BY LOWEST CLAIM_#
BY HIGHEST DAYS_OPEN

ON BRANCH PAGE-BREAK
HEADING CENTER
" "
"Aged Claims Report"
"Branch: (less than symbol)BRANCH "
"(less than symbol)TESTHDR"

4.
The result:
[/IMG]

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