Focal Point
[SOLVED] Issues in compound excel report with Graph and Tabular Reports

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/6137012196

April 01, 2019, 06:20 PM
srajeevan
[SOLVED] Issues in compound excel report with Graph and Tabular Reports
I have an creating a compound excel report.I have the below issue when creating it.
I have been researching in the forum as well as using the documentation to tackle the issues.But still it is not behaving as its says.

I have 4 tabular reports in Page 1 of the compound report.
I have used the below trick to display it in the same worksheet.
   
ON TABLE PCHOLD FORMAT XLSX OPEN NOBREAK -- First Tabular Report
ON TABLE PCHOLD FORMAT XLSX NOBREAK       -- Second Tabular Report
ON TABLE PCHOLD FORMAT XLSX NOBREAK       -- Third Tabular Report
ON TABLE PCHOLD FORMAT XLSX               -- Fourth Tabular Report


These 4 reports displays in one worksheet.I am good here.

I have to display two charts too below these 4 reports.But i learned that charts will occupy right top corner if i try to display charts in the same worksheet.
So i made a compromise and decided to display charts in next worksheet.
   
ON TABLE PCHOLD FORMAT XLSX NOBREAK -- First Chart
ON TABLE PCHOLD FORMAT XLSX               -- Second Chart

These two charts displays in second worksheet.

I have problem when i try to display a report in the third worksheet.

When i use the below code :
   
ON TABLE PCHOLD FORMAT XLSX NOBREAK -- First Tabular Report in 3rd worksheet

The report appears in the 2nd worksheet itself and displays underneath the first graph.
I tried to give CLOSE as it is the last report like below
   
ON TABLE PCHOLD FORMAT XLSX CLOSE

But still the same result.

As per the documentation,since i am not using NOBREAK in the the Second chart, the next report shud come in the next worksheet.but it is not happening.
I am not sure why it is happening.
Any help will be appreciated.

This message has been edited. Last edited by: FP Mod Chuck,


WF8206,Windows 7,8,10
HTM,PDF,EXCEL
April 03, 2019, 10:59 AM
Addy
Hi,
I was able to replicate the issue you are facing.

The graphs that you are trying to export to Excel is what is causing the issue. There is not straight forward solution to your problem.

Some of the options you could explore are :

Using Excel Template and create the Graph once the data is exported.
Change the sequence if that is possible. So have you Graphs as the last sheet in you compound report and it will work as expected.

All the best!!


WF 8.2.04
Windows/Unix
All Formats
In Focus since 2006
April 03, 2019, 02:14 PM
srajeevan
Yes.Currently i display all graphs as the last worksheet.But some of the report and graphs go together.I couldn't find any solution to this issue from the forum as well.
I will have to negotiate with client,i guess.

Thanks.

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


WF8206,Windows 7,8,10
HTM,PDF,EXCEL
April 04, 2019, 09:39 AM
Joanna Swiggett
This is not an ideal solution, but thought I would share as it may give you some ideas. When the graph is created, I replace "ON GRAPH PCHOLD FORMAT XLSX" with "ON GRAPH HOLD AS HOLDG01 FORMAT GIF" and then display the GIF image in XLSX.

 
-*-------------- REPORT 1 --------------
TABLE FILE CAR
PRINT
DEALER_COST
BY COUNTRY
WHERE COUNTRY EQ 'ENGLAND' OR 'JAPAN'
FOOTING
" "
ON TABLE PCHOLD FORMAT XLSX OPEN NOBREAK
END
-RUN
-*-------------- REPORT 2 --------------
TABLE FILE CAR
PRINT
RETAIL_COST  SALES
BY COUNTRY
WHERE COUNTRY EQ 'W GERMANY' OR 'FRANCE'
FOOTING
" "
ON TABLE PCHOLD FORMAT XLSX NOBREAK
END
-RUN
-*-------------- REPORT 3 --------------
TABLE FILE CAR
PRINT
MODEL  BODYTYPE  SEATS
BY CAR
WHERE COUNTRY EQ 'ENGLAND'
FOOTING
" "
ON TABLE PCHOLD FORMAT XLSX NOBREAK
END
-RUN
-*----------- CHART 1 (CAPTURE PICTURE OF CHART) -----------
ENGINE INT CACHE SET ON
-*SET PAGE-NUM=NOLEAD
-*SET HTMLENCODE=ON
-*SET ARGRAPHENGINE=JSCHART
-*SET EMBEDHEADING=ON
-*SET GRAPHDEFAULT=OFF
-DEFAULTH &WF_STYLE_UNITS='PIXELS';
-DEFAULTH &WF_STYLE_HEIGHT='405.0';
-DEFAULTH &WF_STYLE_WIDTH='770.0';
-DEFAULTH &WF_TITLE='WebFOCUS Report';
GRAPH FILE car
-* Created by Info Assist for Graph
SUM CNT.CAR.CARREC.MODEL
BY CAR.BODY.SALES
WHERE CAR.COMP.CAR EQ 'BMW';
-*ON GRAPH PCHOLD FORMAT XLSX
ON GRAPH HOLD AS HOLDG01 FORMAT GIF
ON GRAPH SET VZERO OFF
ON GRAPH SET GRWIDTH 1
ON GRAPH SET AUTOFIT ON
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 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=&WF_TITLE.QUOTEDSTRING, $
*GRAPH_SCRIPT
setReportParsingErrors(false);
setSelectionEnableMove(false);
*END
ENDSTYLE
END
-RUN
-*----------------- DISPLAY CHART 1 -----------------
TABLE FILE CAR
PRINT
COUNTRY NOPRINT
WHERE RECORDLIMIT EQ 1

ON TABLE SET STYLE *
TYPE=REPORT, IMAGE=HOLDG01.GIF, $
ENDSTYLE
ON TABLE PCHOLD FORMAT XLSX CLOSE
END
-RUN
 



WebFOCUS 8.2.01M
April 04, 2019, 09:57 AM
Frans
I've opened a case for this last year. The workaround I use at this moment is to make a very large header in the report and put the graphs first

 
GRAPH FILE..
END

TABLE FILE
HEADING
""
""
""
etc.
END
 



Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
April 04, 2019, 10:28 AM
srajeevan
Hi Joanna,

When i ran the code you posted ,i can see that the reports are displaying one below the other and the graph container is placed at the last ,but is see the message "GIF output is not supported by the JVM in headless mode" in the graph container..
Never encountered this error.
But it looks like GIF output format is not supported.
I tried PNG,JPEG,but no luck.

Any idea what it could be?


WF8206,Windows 7,8,10
HTM,PDF,EXCEL
April 04, 2019, 11:03 AM
srajeevan
@Frans, That is a good idea.I can use that in one of my worksheet.

I have another worksheet where i need to put a report and then graphs below report for which i need to find another solution.


WF8206,Windows 7,8,10
HTM,PDF,EXCEL