I have a report that I am trying to schedule in ReportCaster. The fex has 4 reports that it runs after many, many Matches and Defines. When I run the report from MRE, it runs all 4 reports. However, when I run the schedule from ReportCaster, it only runs the first report only, then appears to fail. Looking at the RC log,I see 4 sets of the following errors: (FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON: FOC2610) CANNOT AGGREGATE BY CONSTANT SORT KEY : BLANK
I am not real familiar with the report, as I did not write it.
Can anyone offer any advice?
Thanks,This message has been edited. Last edited by: Kerry,
Kevin ______________________ Production: WebFocus 7.6.11 on Win2K3 Server Test: WebFocus 7.6.11 on Win2K3 Server Formats: Excel2K, PDF, HTML
August 19, 2009, 03:38 PM
Francis Mariani
ReportCaster only outputs the first report in a multi-report fex. That's the way it's designed and, as far as I know, this hasn't changed in the newer releases. You could convert the four reports into one, compound report comprising the four reports.
The aggregation message is not related to the above problem. The reports most likely have a BY BLANK on a SUM verb where BLANK is a DEFINEd field. If the BLANK field is really a blank field (e.g. BLANK/A1 = ' ' I would recommend removing the definition of this field and the BY on this field - it would allow WebFOCUS to generate SQL that aggregates properly and thereby making the program more efficient.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
August 19, 2009, 03:45 PM
Doug
MO Admin, There's a lot of posts about COMPOUND reports, including this one. - Check 'em out and Enjoy...
In FOCUS Since 1983 ~ from FOCUS to WebFOCUS. Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
August 19, 2009, 03:53 PM
MO Admin
Francis,
I understand what you are saying, I'm just not sure how to complete the task.
I would like to combine the 4 reports into 1 compound report.
Here is my code for the 4 reports (with styling removed to condense it).
What do I need to do to make them into one?
Thanks,
Kevin ______________________ Production: WebFocus 7.6.11 on Win2K3 Server Test: WebFocus 7.6.11 on Win2K3 Server Formats: Excel2K, PDF, HTML
August 19, 2009, 03:54 PM
MO Admin
TABLE FILE REPORTREADY1 SUM MONEY21/D15.2C!D AS '' BY RANK NOPRINT BY BLANK AS '&REPDATE1 TOTALS' ACROSS HIGHEST FISCAL AS '' HEADING "DEPARTMENT OF REVENUE" "TAXATION DIVISION" "RECOVERY TRACKING REPORT" "DATE OF REPORT: <+0>&DATETRMDYY <+0> " " " " " WHERE MONTH1 EQ '&REPDATE'; WHERE BLANK EQ 'JUDGMENT PRACTICE' OR 'AGING PROCESS' OR 'NEXUS/DISCOVERY (PRE-AGING)' OR 'FIELD COMPLIANCE (PRE-AGING)'; WHERE FISCAL EQ 'FY08' OR 'FY09' OR 'FY10'; ON TABLE SET PAGE-NUM OFF ON TABLE COLUMN-TOTAL AS 'GENERAL REVENUE COLLECTIONS' ON TABLE SET STYLE * END
TABLE FILE REPORTREADY1 SUM MONEY21/D15.2C!D AS '' BY RANK NOPRINT BY BLANK AS 'ATTRIBUTABLE REVENUES' ACROSS HIGHEST FISCAL NOPRINT WHERE MONTH1 EQ '&REPDATE'; WHERE BLANK EQ 'NEXUS/DISCOVERY' OR 'FIELD COMPLIANCE' OR 'ESTATE RECOVERY' OR 'OUTBOUND CALL CENTER' OR 'INBOUND CALL CENTER' OR 'LEVYING TILLS'; WHERE FISCAL EQ 'FY08' OR 'FY09' OR 'FY10'; ON TABLE SET PAGE-NUM OFF ON TABLE NOTOTAL ON TABLE SET STYLE * END
TABLE FILE REPORTREADY1 SUM MONEY21/D15.2C!D AS '' BY RANK NOPRINT BY BLANK AS 'FISCAL YEAR TO DATE TOTALS' ACROSS HIGHEST FISCAL AS '' &AUGUST_COMM.EVAL WHERE MONTH1 EQ 'July'; &SEPTEMBER_COMM.EVAL WHERE MONTH1 EQ 'July' OR 'August'; &OCTOBER_COMM.EVAL WHERE MONTH1 EQ 'July' OR 'August' OR 'September'; &NOVEMBER_COMM.EVAL WHERE MONTH1 EQ 'July' OR 'August' OR 'September' OR 'October'; &DECEMBER_COMM.EVAL WHERE MONTH1 EQ 'July' OR 'August' OR 'September' OR 'October' OR 'November'; &JANUARY_COMM.EVAL WHERE MONTH1 EQ 'July' OR 'August' OR 'September' OR 'October' OR 'November' OR 'December'; &FEBRUARY_COMM.EVAL WHERE MONTH1 EQ 'July' OR 'August' OR 'September' OR 'October' OR 'November' OR 'December' OR 'January'; &MARCH_COMM.EVAL WHERE MONTH1 EQ 'July' OR 'August' OR 'September' OR 'October' OR 'November' OR 'December' OR 'January' OR 'February'; &APRIL_COMM.EVAL WHERE MONTH1 EQ 'July' OR 'August' OR 'September' OR 'October' OR 'November' OR 'December' OR 'January' OR 'February' OR 'March'; &MAY_COMM.EVAL WHERE MONTH1 EQ 'July' OR 'August' OR 'September' OR 'October' OR 'November' OR 'December' OR 'January' OR 'February' OR 'March' OR 'April'; &JUNE_COMM.EVAL WHERE MONTH1 EQ 'July' OR 'August' OR 'September' OR 'October' OR 'November' OR 'December' OR 'January' OR 'February' OR 'March' OR 'April' OR 'May'; &JULY_COMM.EVAL WHERE MONTH1 EQ 'July' OR 'August' OR 'September' OR 'October' OR 'November' OR 'December' OR 'January' OR 'February' OR 'March' OR 'April' OR 'May' OR 'June'; WHERE BLANK EQ 'JUDGMENT PRACTICE' OR 'AGING PROCESS' OR 'NEXUS/DISCOVERY (PRE-AGING)' OR 'FIELD COMPLIANCE (PRE-AGING)'; WHERE FISCAL EQ 'FY08' OR 'FY09' OR 'FY10'; HEADING "" ON TABLE SET PAGE-NUM OFF ON TABLE COLUMN-TOTAL AS 'GENERAL REVENUE COLLECTIONS' ON TABLE SET STYLE * END
TABLE FILE REPORTREADY1 SUM MONEY21/D15.2C!D AS '' BY RANK NOPRINT BY BLANK AS 'ATTRIBUTABLE REVENUES' ACROSS HIGHEST FISCAL NOPRINT &AUGUST_COMM.EVAL WHERE MONTH1 EQ 'July'; &SEPTEMBER_COMM.EVAL WHERE MONTH1 EQ 'July' OR 'August'; &OCTOBER_COMM.EVAL WHERE MONTH1 EQ 'July' OR 'August' OR 'September'; &NOVEMBER_COMM.EVAL WHERE MONTH1 EQ 'July' OR 'August' OR 'September' OR 'October'; &DECEMBER_COMM.EVAL WHERE MONTH1 EQ 'July' OR 'August' OR 'September' OR 'October' OR 'November'; &JANUARY_COMM.EVAL WHERE MONTH1 EQ 'July' OR 'August' OR 'September' OR 'October' OR 'November' OR 'December'; &FEBRUARY_COMM.EVAL WHERE MONTH1 EQ 'July' OR 'August' OR 'September' OR 'October' OR 'November' OR 'December' OR 'January'; &MARCH_COMM.EVAL WHERE MONTH1 EQ 'July' OR 'August' OR 'September' OR 'October' OR 'November' OR 'December' OR 'January' OR 'February'; &APRIL_COMM.EVAL WHERE MONTH1 EQ 'July' OR 'August' OR 'September' OR 'October' OR 'November' OR 'December' OR 'January' OR 'February' OR 'March'; &MAY_COMM.EVAL WHERE MONTH1 EQ 'July' OR 'August' OR 'September' OR 'October' OR 'November' OR 'December' OR 'January' OR 'February' OR 'March' OR 'April'; &JUNE_COMM.EVAL WHERE MONTH1 EQ 'July' OR 'August' OR 'September' OR 'October' OR 'November' OR 'December' OR 'January' OR 'February' OR 'March' OR 'April' OR 'May'; &JULY_COMM.EVAL WHERE MONTH1 EQ 'July' OR 'August' OR 'September' OR 'October' OR 'November' OR 'December' OR 'January' OR 'February' OR 'March' OR 'April' OR 'May' OR 'June'; WHERE BLANK EQ 'NEXUS/DISCOVERY' OR 'FIELD COMPLIANCE' OR 'ESTATE RECOVERY' OR 'OUTBOUND CALL CENTER' OR 'INBOUND CALL CENTER' OR 'LEVYING TILLS' OR '* EDITS'; WHERE FISCAL EQ 'FY08' OR 'FY09' OR 'FY10'; FOOTING "* Reported on an annual basis because the data is only available when tax season is complete." ON TABLE SET PAGE-NUM OFF ON TABLE NOTOTAL ON TABLE SET STYLE * END
Kevin ______________________ Production: WebFocus 7.6.11 on Win2K3 Server Test: WebFocus 7.6.11 on Win2K3 Server Formats: Excel2K, PDF, HTML
August 19, 2009, 04:14 PM
Francis Mariani
Read up on compound reports, meanwhile, here's a simple example that works for PDF and EXL2K:
SET PAGE-NUM = OFF
SET COMPOUND=OPEN
TABLE FILE CAR
SUM
SALES
BY COUNTRY
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
TYPE=REPORT, COLOR=RED, $
ENDSTYLE
END
TABLE FILE CAR
SUM
SEATS
BY CAR
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
TYPE=REPORT, COLOR=BLUE, $
ENDSTYLE
END
TABLE FILE CAR
SUM
WEIGHT
BY MODEL
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
TYPE=REPORT, COLOR=ORANGE, $
ENDSTYLE
END
SET COMPOUND=CLOSE
TABLE FILE CAR
SUM
RETAIL_COST
BY COUNTRY
BY CAR
BY MODEL
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
TYPE=REPORT, COLOR=GREEN, $
ENDSTYLE
END
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
August 19, 2009, 04:58 PM
MO Admin
I have been researching compound reports since you suggested it. I tried adding the set commands before each report. Ran it through RC, and still came back with only the first. Does it have anything to do with my output? I do not have a ON TABLE PCHOLD FORMAT line.
Kevin ______________________ Production: WebFocus 7.6.11 on Win2K3 Server Test: WebFocus 7.6.11 on Win2K3 Server Formats: Excel2K, PDF, HTML
August 25, 2009, 04:06 PM
Kerry
Hi Kevin,
Have you been able to run with the code Francis provided? Is that working for you? From that code I would try to work with 2 reports first and see if you can get that to work. Please let us know how you make out.
Cheers,
Kerry
Kerry Zhan Focal Point Moderator Information Builders, Inc.