Hi everyone,

I am running into an issue involving compound documents that I could use some help with. I would like to create a coordinated compound PDF report that has one page for each value of COUNTRY. If the total sales for that country are greater than zero, I would like to display a bar chart showing the total sales for that country. If the total sales are zero, I would like to display a customized message.

Typically when I want to display a custom message when there is no data, I use something similar to the following code. This works fine for a given value of COUNTRY (run as a standalone FEX):

 
TABLE FILE CAR
SUM
     SALES
BY  LOWEST CAR NOPRINT
WHERE SALES GT 0;
WHERE COUNTRY EQ '&COUNTRY';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD FORMAT ALPHA
END
-RUN

-IF &LINES EQ 0 THEN GOTO EMPTY;

-*IA_GRAPH_BEGIN
-*Do not delete or modify the comments below
*-INTERNAL_COMMENT LINE#0$PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0iVVRGLTgiIHN0YW5kYWxvbmU9Im5vIj8+DQo8IS0tMS4wLS0+DQo8Um9vdCB2ZXJzaW9uPSIxLjAiPg0KICAgIDxPYmplY3Qgb2JqZWN0SWQ9IkNoYXJ0XzEiPg0KICAgICAgICA8UHJvcGVydHkgbmFtZT0iTGlua2VkU29ydHMiIHR5cGU9ImphdmEubGFuZy5TdHJpbmciLz4NCiAgICAgICAgPFByb3BlcnR5IG5hbWU9ImN1c3RvbVdoZXJlcyIgdHlwZT0iTWFwIi8+DQogICAgPC9PYmplY3Q+DQogICAgPE9iamVjdCBvYmplY3RJZD0iR0xPQkFMIj4NCiAgICAgICAgPFByb3BlcnR5IG5hbWU9IlNhbXBsZURhdGEiIHR5cGU9ImphdmEubGFuZy5Cb29sZWFuIj5mYWxzZTwvUHJvcGVydHk+DQogICAgICAgIDxQcm9wZXJ0eSBuYW1lPSJHbG9iYWxSZWNvcmRMaW1pdCIgdHlwZT0iamF2YS5sYW5nLlN0cmluZyI+NTAwPC9Qcm9wZXJ0eT4NCiAgICAgICAgPFByb3BlcnR5IG5hbWU9Ikdsb2JhbFJ1blJlY29yZExpbWl0IiB0eXBlPSJqYXZhLmxhbmcuU3RyaW5nIj4wPC9Qcm9wZXJ0eT4NCiAgICAgICAgPFByb3BlcnR5IG5hbWU9ImZpZWxkRGlzcGxheU1vZGUiIHR5cGU9ImphdmEubGFuZy5TdHJpbmciPmxhYmVsPC9Qcm9wZXJ0eT4NCiAgICAgICAgPFByb3BlcnR5IG5hbWU9InByZWZpeERpc3BsYXlNb2RlIiB0eXBlPSJqYXZhLmxhbmcuU3RyaW5nIi8+DQogICAgICAgIDxQcm9wZXJ0eSBuYW1lPSJBY3RpdmVfU3R5bGVfVXNlcl90eXBlIiB0eXBlPSJqYXZhLmxhbmcuU3RyaW5nIj5wb3dlcjwvUHJvcGVydHk+DQogICAgICAgIDxQcm9wZXJ0eSBuYW1lPSJHbG9iYWxWYWx1ZXNQYWdpbmciIHR5cGU9ImphdmEubGFuZy5TdHJpbmciPjQ8L1Byb3BlcnR5Pg0KICAgICAgICA8UHJvcGVydHkgbmFtZT0iRm9jZXhlY1ByZWZlcmVuY2VzIiB0eXBlPSJNYXAiPg0KICAgICAgICAgICAgPEVudHJ5IGtleT0iZGlzcGxheUVkaXRNb2RlSW5mb01pbmlQcmVmZXJlbmNlIiB0eXBlPSJqYXZhLmxhbmcuU3RyaW5nIj5mYWxzZTwvRW50cnk+DQogICAgICAgICAgICA8RW50cnkga2V5PSJkaXNwbGF5Rm9ybWF0VGFiSW5mb01pbmlQcmVmZXJlbmNlIiB0eXBlPSJqYXZhLmxhbmcuU3RyaW5nIj50cnVlPC9FbnRyeT4NCiAgICAgICAgICAgIDxFbnRyeSBrZXk9ImRpc3BsYXlIb21lVGFiSW5mb01pbmlQcmVmZXJlbmNlIiB0eXBlPSJqYXZhLmxhbmcuU3RyaW5nIj5mYWxzZTwvRW50cnk+DQogICAgICAgICAgICA8RW50cnkga2V5PSJkaXNwbGF5UXVpY2tBY2Nlc3NUb29sYmFyU2F2ZUluZm9NaW5pUHJlZmVyZW5jZSIgdHlwZT0iamF2YS5sYW5nLlN0cmluZyI+dHJ1ZTwvRW50cnk+DQogICAgICAgICAgICA8RW50cnkga2V5PSJtZXRhZGF0YV92aWV3cyIgdHlwZT0iamF2YS5sYW5nLlN0cmluZyI+TWV0YURhdGFUcmVlLlZJRVdfRElNUzwvRW50cnk+DQogICAgICAgICAgICA8RW50cnkga2V5PSJkaXNwbGF5UmVzb3VyY2VzRmllbGRUYWJJbmZvTWluaVByZWZlcmVuY2UiIHR5cGU9ImphdmEubGFuZy5TdHJpbmciPmZhbHNlPC9FbnRyeT4NCiAgICAgICAgICAgIDxFbnRyeSBrZXk9ImRpc3BsYXlJbnNlcnRUYWJJbmZvTWluaVByZWZlcmVuY2UiIHR5cGU9ImphdmEubGFuZy5TdHJpbmciPmZhbHNlPC9FbnRyeT4NCiAgICAgICAgICAgIDxFbnRyeSBrZXk9ImRpc3BsYXlTbGljZXJzVGFiRWRpdEluZm9NaW5pUHJlZmVyZW5jZSIgdHlwZT0iamF2YS5sYW5nLlN0cmluZyI+ZmFsc2U8L0VudHJ5Pg0KICAgICAgICAgICAgPEVudHJ5IGtleT0iZGlzcGxheVNlcmllc1RhYkluZm9NaW5pUHJlZmVyZW5jZSIgdHlwZT0iamF2YS5sYW5nLlN0cmluZyI+ZmFsc2U8L0VudHJ5Pg0KICAgICAgICAgICAgPEVudHJ5IGtleT0iaW5mb0Fzc2lzdE1vZGVBbGxvd2VkSW5mb01pbmlQcmVmZXJlbmNlIiB0eXBlPSJqYXZhLmxhbmcuU3RyaW5nIj5mYWxzZTwvRW50cnk+DQogICAgICAgICAgICA8RW50cnkga2V5PSJkZWZhdWx0X3ByZXZpZXdfcGFnZWxpbWl0X2xheW91dCIgdHlwZT0iamF2YS5sYW5nLlN0cmluZyI+MTwvRW50cnk+DQogICAgICAgICAgICA8RW50cnkga2V5PSJleGNlbF9mb3JtYXRfdmlzaWJsZSIgdHlwZT0iamF2YS5sYW5nLlN0cmluZyI+dHJ1ZTwvRW50cnk+DQogICAgICAgICAgICA8RW50cnkga2V5PSJkZWZhdWx0X3ByZXZpZXdfcGFnZWxpbWl0IiB0eXBlPSJqYXZhLmxhbmcuU3RyaW5nIj41PC9FbnRyeT4NCiAgICAgICAgICAgIDxFbnRyeSBrZXk9ImRlZmF1bHRfY29tcG9zZV9mb3JtYXQiIHR5cGU9ImphdmEubGFuZy5TdHJpbmciPlBERjwvRW50cnk+DQogICAgICAgICAgICA8RW50cnkga2V5PSJkaXNwbGF5SW50ZXJhY3RpdmVNb2RlSW5mb01pbmlQcmVmZXJlbmNlIiB0eXBlPSJqYXZhLmxhbmcuU3RyaW5nIj50cnVlPC9FbnRyeT4NCiAgICAgICAgICAgIDxFbnRyeSBrZXk9InJ1bk9uU3RhcnR1cEluZm9NaW5pUHJlZmVyZW5jZSIgdHlwZT0iamF2YS5sYW5nLlN0cmluZyI+dHJ1ZTwvRW50cnk+DQogICAgICAgICAgICA8RW50cnkga2V5PSJkaXNwbGF5RGF0YVRhYkluZm9NaW5pUHJlZmVyZW5jZSIgdHlwZT0iamF2YS5sYW5nLlN0cmluZyI+ZmFsc2U8L0VudHJ5Pg0KICAgICAgICAgICAgPEVudHJ5IGtleT0iZWRpdG9yVHlwZV92aXNpYmxlIiB0eXBlPSJqYXZhLmxhbmcuU3RyaW5nIj50cnVlPC9FbnRyeT4NCiAgICAgICAgICAgIDxFbnRyeSBrZXk9InRoZW1lc19jb250cm9sX3Zpc2libGUiIHR5cGU9ImphdmEubGFuZy5TdHJpbmciPnRydWU8L0VudHJ5Pg0KICAgICAgICAgICAgPEVudHJ5IGtleT0iZGlzcGxheVNsaWNlcnNUYWJJbnRlcmFjdGl2ZUluZm9NaW5pUHJlZmVyZW5jZSIgdHlwZT0iamF2YS5sYW5nLlN0cmluZyI+dHJ1ZTwvRW50cnk+DQogICAgICAgICAgICA8RW50cnkga2V5PSJkaXNwbGF5TGF5b3V0VGFiSW5mb01pbmlQcmVmZXJlbmNlIiB0eXBlPSJqYXZhLmxhbmcu
*-INTERNAL_COMMENT LINE#1$U3RyaW5nIj5mYWxzZTwvRW50cnk+DQogICAgICAgIDwvUHJvcGVydHk+DQogICAgICAgIDxQcm9wZXJ0eSBuYW1lPSJjYXNjYWRlTmFtZXMiIHR5cGU9Ik1hcCIvPg0KICAgICAgICA8UHJvcGVydHkgbmFtZT0iTWFzdGVyX0ZpbGVzIiB0eXBlPSJTZXQiPg0KICAgICAgICAgICAgPEVudHJ5IHR5cGU9ImphdmEubGFuZy5TdHJpbmciPkNBUjwvRW50cnk+DQogICAgICAgIDwvUHJvcGVydHk+DQogICAgICAgIDxQcm9wZXJ0eSBuYW1lPSJtZXRhZGF0YVZpZXdBcyIgdHlwZT0iTWFwIj4NCiAgICAgICAgICAgIDxFbnRyeSBrZXk9IkNBUiIgdHlwZT0iamF2YS5sYW5nLlN0cmluZyI+TWV0YURhdGFUcmVlLlZJRVdfRElNUzwvRW50cnk+DQogICAgICAgIDwvUHJvcGVydHk+DQogICAgICAgIDxQcm9wZXJ0eSBuYW1lPSJlbmFibGVQcmV2aWV3IiB0eXBlPSJqYXZhLmxhbmcuQm9vbGVhbiI+dHJ1ZTwvUHJvcGVydHk+DQogICAgPC9PYmplY3Q+DQo8L1Jvb3Q+DQo=
-*Do not delete or modify the comments above
ENGINE INT CACHE SET ON
-DEFAULTH &WF_STYLE_UNITS='PIXELS';
-DEFAULTH &WF_STYLE_HEIGHT='405.0';
-DEFAULTH &WF_STYLE_WIDTH='770.0';
GRAPH FILE CAR
-* Created by Info Assist for Graph
SUM CAR.BODY.SALES
BY CAR.COMP.CAR
WHERE CAR.BODY.SALES GT 0;
WHERE COUNTRY EQ '&COUNTRY';
ON GRAPH PCHOLD FORMAT JSCHART
ON GRAPH SET VZERO OFF
ON GRAPH SET HTMLENCODE ON
ON GRAPH SET GRAPHDEFAULT OFF
ON GRAPH SET UNITS &WF_STYLE_UNITS
ON GRAPH SET HAXIS &WF_STYLE_WIDTH
ON GRAPH SET VAXIS &WF_STYLE_HEIGHT
ON GRAPH SET GRMERGE ADVANCED
ON GRAPH SET GRMULTIGRAPH 0
ON GRAPH SET GRLEGEND 0
ON GRAPH SET GRXAXIS 1
ON GRAPH SET LOOKGRAPH VBAR
ON GRAPH SET AUTOFIT ON
ON GRAPH SET STYLE *
*GRAPH_SCRIPT
setPieDepth(0);
setPieTilt(0);
setDepthRadius(0);
setCurveFitEquationDisplay(false);
setPlace(true);
*END
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/javaassist/intl/EN/ENIADefault_combine.sty,$
TYPE=REPORT, TITLETEXT='WebFOCUS Report', $
*GRAPH_SCRIPT
setReportParsingErrors(false);
setSelectionEnableMove(false);
*END
ENDSTYLE
END
-*IA_GRAPH_FINISH
-GOTO THEEND;
-EMPTY

TABLE FILE CAR
PRINT SALES NOPRINT
WHERE COUNTRY EQ '&COUNTRY';
HEADING
"</1 </1 </1  </1 There are no records for this metric."
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
END

-THEEND

I am having trouble figuring out how to adapt this for a coordinated report. I thought that replacing the "WHERE COUNTRY EQ '&COUNTRY'" statements with "BY LOWEST COUNTRY NOPRINT" and changing "ON GRAPH SET GRMULTIGRAPH 0" to "ON GRAPH SET GRMULTIGRAPH 1" would be all I needed. However, this results in a blank page for FRANCE in the coordinated compound document as opposed to the message. Am I missing something obvious here?

Thanks,

Stephen

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

It may be not elegant and I don't know how complex is your compound report, but this does the trick.

-* COMPOUND REPORT
-* *******************************
-SET &COUNTRY = 'ITALY';

TABLE FILE CAR
SUM SALES
BY  LOWEST CAR NOPRINT
WHERE TOTAL SALES GT 0;
WHERE COUNTRY EQ '&COUNTRY';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD FORMAT ALPHA
END
-RUN

-SET &NOGRAPH = IF &LINES EQ 0 THEN '-*' ELSE '';
-SET &NOMSG   = IF &LINES NE 0 THEN '-*' ELSE '';

SET HTMLARCHIVE=ON
COMPOUND LAYOUT PCHOLD FORMAT PDF
UNITS=IN, $
SECTION=section1, LAYOUT=ON, METADATA='0.5^0.5^0.5^0.5^4', MERGE=OFF, ORIENTATION=PORTRAIT, PAGESIZE=Letter, SHOW_GLOBALFILTER=OFF, $
PAGELAYOUT=1, NAME='Page layout 1', text='Page layout 1', TOC-LEVEL=1, BOTTOMMARGIN=0.5, TOPMARGIN=0.5, METADATA='BOTTOMMARGIN=0.5,TOPMARGIN=0.5,LEFTMARGIN=0,RIGHTMARGIN=0,', $
&NOGRAPH.EVAL COMPONENT='graph1', TEXT='graph1', TOC-LEVEL=2, POSITION=(0.833 0.729), DIMENSION=(6.250 2.292), COMPONENT-TYPE=GRAPH, METADATA='HEIGHT: 2.292in; WIDTH: 6.25in; POSITION: absolute; LEFT: 0.833in; Z-INDEX: 100; TOP: 0.729in', $
&NOMSG.EVAL COMPONENT='report1', TEXT='report1', TOC-LEVEL=2, POSITION=(0.833 0.729), DIMENSION=(6.250 2.292), METADATA='HEIGHT: 2.292in; WIDTH: 6.25in; POSITION: absolute; LEFT: 0.833in; Z-INDEX: 100; TOP: 0.729in', $
END
&NOGRAPH.EVAL SET COMPONENT='graph1'
-*component_type graph
&NOGRAPH.EVAL-INCLUDE newfex1.fex
&NOMSG.EVAL SET COMPONENT='report1'
-*component_type report
&NOMSG.EVAL-INCLUDE newfex.fex
COMPOUND END

-* File newfex.fex
-* *******************************
TABLE FILE CAR
PRINT COUNTRY NOPRINT
WHERE READLIMIT EQ 1;
WHERE RECORDLIMIT EQ 1;
HEADING
"</1 </1 </1  </1 There are no records for this metric."
ON TABLE PCHOLD FORMAT PDF
END

-* File newfex1.fex
-* *******************************
-*Do not delete or modify the comments below
-* xxxxxxxxxxx (suppressed because not relevant for example)
-*Do not delete or modify the comments above
GRAPH FILE CAR
-* Created by Advanced Graph Assistant
SUM CAR.BODY.SEATS
BY CAR.COMP.CAR
ON GRAPH PCHOLD FORMAT PNG
ON GRAPH SET HTMLENCODE ON
ON GRAPH SET GRAPHDEFAULT OFF
ON GRAPH SET VZERO OFF
ON GRAPH SET HAXIS 770
ON GRAPH SET VAXIS 405
ON GRAPH SET UNITS PIXELS
ON GRAPH SET LOOKGRAPH VBAR
ON GRAPH SET GRMERGE ADVANCED
ON GRAPH SET GRMULTIGRAPH 0
ON GRAPH SET GRLEGEND 0
ON GRAPH SET GRXAXIS 1
ON GRAPH SET GRAPHSTYLE *
setTemplateFile("/images/tdg/template/IBISouthWestern.txt");
setReportParsingErrors(false);
setSelectionEnableMove(false);
setDepthRadius(5);
setTransparentBorderColor(getChartBackground(),true);
setTransparentBorderColor(getSeries(0),true);
setTransparentBorderColor(getSeries(1),true);
setTransparentBorderColor(getSeries(2),true);
setTransparentBorderColor(getSeries(3),true);
setTransparentBorderColor(getSeries(4),true);
setTransparentBorderColor(getSeries(5),true);
setTransparentBorderColor(getSeries(6),true);
setTransparentBorderColor(getSeries(7),true);
setTransparentBorderColor(getSeries(8),true);
setTransparentBorderColor(getSeries(9),true);
setTransparentBorderColor(getSeries(10),true);
setPlace(true);
ENDSTYLE
END

By changing ITALY to FRANCE in the first SET line, you'll have the "No record" message instead of the graph.
Hope that help

Thanks for your help! There is one element I still don't get though.

My final compound report is coordinated on COUNTRY (MERGE=ON). So instead of filtering on COUNTRY, the final compound document will be displaying all page layouts for each value of COUNTRY. How would I need to alter your code to get the desired result (message for zero sales, chart otherwise) in that case?

Try below code. Base code was taken from a post by Ginny. Refer to http://forums.informationbuild...891004742#5891004742 .
I have made few changes as per your need.

APP PATH IBISAMP
-RUN

-*SET HTMLARCHIVE=ON
COMPOUND LAYOUT PCHOLD FORMAT PDF
UNITS=IN, $
SECTION=section1, LAYOUT=ON, METADATA='0.5^0.5^0.5^0.5^4', MERGE=ON, ORIENTATION=PORTRAIT, PAGESIZE=Letter, SHOW_GLOBALFILTER=OFF, $
PAGELAYOUT=1, NAME='Page layout 1', text='Page layout 1', TOC-LEVEL=1, BOTTOMMARGIN=0.5, TOPMARGIN=0.5, METADATA='BOTTOMMARGIN=0.5,TOPMARGIN=0.5,LEFTMARGIN=0,RIGHTMARGIN=0,', $
-*COMPONENT='graph1', TEXT='graph1', TOC-LEVEL=2, POSITION=(0.833 5.729), DIMENSION=(6.250 2.292), COMPONENT-TYPE=GRAPH, METADATA='HEIGHT: 2.292in; WIDTH: 6.25in; POSITION: absolute; LEFT: 0.833in; Z-INDEX: 100; TOP: 0.729in', $
COMPONENT='report1', TEXT='report1', TOC-LEVEL=2, POSITION=(0.833 0.729), DIMENSION=(6.250 2.292), METADATA='HEIGHT: 2.292in; WIDTH: 6.25in; POSITION: absolute; LEFT: 0.833in; Z-INDEX: 100; TOP: 0.729in', $
END
-*SET COMPONENT='graph1'
SET COMPONENT='report1'
DEFINE FILE CAR
COUNTRYX/A10 = IF COUNTRY EQ 'W GERMANY' THEN 'GERMANY' ELSE COUNTRY;
END
GRAPH FILE CAR
SUM 
SEATS
BY COUNTRYX
BY CAR
-*ON COUNTRYX PAGE-BREAK
ON GRAPH HOLD AS HOLD FORMAT SVG
ON GRAPH SET HTMLENCODE ON
ON GRAPH SET GRAPHDEFAULT OFF
ON GRAPH SET VZERO OFF
ON GRAPH SET HAXIS 770
ON GRAPH SET VAXIS 405
ON GRAPH SET UNITS PIXELS
ON GRAPH SET LOOKGRAPH VBAR
ON GRAPH SET GRMERGE ADVANCED
ON GRAPH SET GRMULTIGRAPH 1
ON GRAPH SET GRLEGEND 0
ON GRAPH SET GRXAXIS 1
ON GRAPH SET GRAPHSTYLE *
setReportParsingErrors(false);
setSelectionEnableMove(false);
setDepthRadius(5);
setTransparentBorderColor(getChartBackground(),true);
setTransparentBorderColor(getSeries(0),true);
setTransparentBorderColor(getSeries(1),true);
setTransparentBorderColor(getSeries(2),true);
setTransparentBorderColor(getSeries(3),true);
setTransparentBorderColor(getSeries(4),true);
setTransparentBorderColor(getSeries(5),true);
setTransparentBorderColor(getSeries(6),true);
setTransparentBorderColor(getSeries(7),true);
setTransparentBorderColor(getSeries(8),true);
setTransparentBorderColor(getSeries(9),true);
setTransparentBorderColor(getSeries(10),true);
setPlace(true);
ENDSTYLE
END
-RUN
DEFINE FILE CAR
COUNTRYX/A10 = IF COUNTRY EQ 'W GERMANY' THEN 'GERMANY' ELSE COUNTRY;
END
TABLE FILE CAR
SUM
SALES NOPRINT
COMPUTE CNTR/I4 = CNTR + 1; NOPRINT
COMPUTE CNTR2/A4 = IF &FOCGRAPHCNT EQ 1 THEN ' ' ELSE FTOA(CNTR-1,'(F4)','A4'); NOPRINT
COMPUTE IMG/A16 = IF SALES EQ 0 THEN '' ELSE 'HOLD'||LJUST(4,CNTR2,'A4')|| '.svg'; NOPRINT
BY COUNTRYX NOPRINT
-*ON COUNTRYX PAGE-BREAK
ON COUNTRYX SUBHEAD
"</1 </1 </1  </1 There are no records for this metric."
WHEN SALES EQ 0;
HEADING 
"Country <COUNTRY Sales <SALES"
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
TYPE=HEADING, IMAGE=(IMG), POSITION=(1.8 1.8), DIMENSION=(5.5 2.6), $
ENDSTYLE
END
COMPOUND END

That is exactly what I was looking for. I am fairly new to WebFOCUS, so it will take me a little time to work through the code and figure out how exactly it works, but at first glance I should be able to adapt this to my current project. Thanks both of you for your help!

Good to hear this. You can always refer to WebFOCUS Documentation, technical support site and this forum. It will have solution for most of your queries.

Thanks,
Ram