The backstory to this is that in v7.6.7 we are having a challenge with images in PDF reports in that the report won't even run without an error. Someone suggested I create a DEFINE that points to the image and put that field in the HEADING of my report. This actually works insofar as the PDF report now runs. I still don't get the image. Instead I get the text of the DEFINE field. I can live with that until we upgrade to v7.6.8 where this is supposedly fixed.

In the meantime I discovered that using this approach causes a noticeable decrease in report performance. Upon closer examination I found that it breaks the SQL Aggregation thus causing large amounts of data to be passed from SQL to WebFOCUS...

Note: I tried to recreate this with the CAR file but this is an issue regarding MS SQL Server data sources and I don't have a SQL verion of any WebFOCUS data sources.

I have created a DEFINE in one of my MS_SQL Server synomyms that looks like
this...

 DEFINE MYLOGO/A87  WITH PROD_HIST_ID='<img
 src="https://servername:port/approot/baseapp/logo.gif" align="absmiddle"
 >';
 TITLE='My Logo', $
 

When I include the MYLOGO field in the report HEADING, the SQL Server no
longer handles the aggregation and I get a FOC2608 error. Consider the
following...

 SET TRACEOFF = ALL
 SET TRACEUSER = CLIENT
 SET TRACEON = SQLAGGR//CLIENT
 SET TRACEON = STMTRACE//CLIENT
 SET TRACESTAMP = OFF
 SET XRETRIEVAL = OFF
 TABLE FILE SQLTABLE
 SUM PROD_QTR_QTY
 BY IND_SECT
 ACROSS PROD_QTR
 HEADING
 "<MYLOGO "
 ON TABLE PCHOLD FORMAT HTML
 END
 

[result]
FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON: FOC2608 - ONLY COUNT
CAN BE AGGREGATED FOR CONSTANT DEFINES SELECT
T1."OE_PROD_HIST_ID",T1."PROD_QTR",T1."PROD_QTR_QTY", T1."BASE_MOD_ID",T2."BASE-
_MOD_ID",T2."IND_SECT" FROM "WF_Dev"."dbo"."OE_PROD_HISTORY"
T1,"WF_Dev"."dbo"."BASE_MODEL" T2 WHERE (T2."BASE_MOD_ID" = T1."BASE_MOD_ID")
ORDER BY T1."OE_PROD_HIST_ID",T2."BASE_MOD_ID"; ...RETRIEVAL KILLED 0 NUMBER OF
RECORDS IN TABLE= 0 LINES= 0
[/result]

Removing the MYLOGO field from the HEADING allows the SQL Server to do the
aggregation successfully.

Is there a different way I can create the define so it doesn't break the SQL
Server aggregation? Or is there a better way to work around the PDF report with image issue?

Thanks,

Dan Pinault

This message has been edited. Last edited by: Dan Pinault,

Almost all my reports are two-phase - one to retrieve the data and one to format the report, hence I wouldn't ever have DEFINE's related to the output created in the data-retrieval step.

I would create a HOLD file and move the DEFINE to the HOLD file. I make sure that my data-retrieval is as efficient as possible by turning on SQL traces to verify aggregation and bad joins, etc.

Francis,

I like your idea. Keeping the data retrieval and output formatting separate is probably a practice I should get in to.

I modified my code as you suggested and it did fix my SQL Aggregation error. I still don't get an image when selecting PDF output but at least it doesn't crash!

Thanks again,

Dan

Dan, that DEFINE to get the image in the HEADING will only work for HTML and Excel 2000. For PDF you need to use the WebFOCUS stylesheet with code similar to this:

TYPE = HEADING, IMAGE=fn_gp.gif, IMAGEALIGN=TOP, IMAGEBREAK=ON, $

Grrr - I can never keep the image format straight between PDF and everything else. I wish it was as simple as selecting the image file you want and letting the server parse the conversion to whatever output type is selected.

Oh well. Thanks for the tip.

Regards,

Dan