January 04, 2005, 10:32 PM
<Derek>Error setting display format when using SQL translator.
I'm using the SQL translator to create a report. The user selects a display format on an html page and that is passed into the .fex. However, the .fex is not creating the report in the proper format or at all. I am receiving this message...
"No HTML Output!
0 NUMBER OF RECORDS IN TABLE= 15 LINES= 15
0 PDF FILE SAVED ..."
It works if I do a simple report using the WebFOCUS language but not when I try to use the SQL translator. I couldn't find anything in the documentation that explains how to set a display format when using sql queries. I found the TABLE FILE SQLOUT PRINT * recommendation somewhere on these boards but it doesn't seem to work. Here is my code...
-* File doc_count.fex
-GOTO &DOCTYPE;
-COPYRIGHTS
SET SQLENGINE=SQLMSS
SQL SQLMSS
SELECT Year(DateAdd("m",3,[Approval_Date])) AS FY,
Count(SANDIA_COPY_RIGHT_NUMBER) AS [Count of Coprights] FROM TBLCO_BASE WHERE (((APPROVAL_DATE) Is Not Null)) GROUP BY Year(DateAdd("m",3,[Approval_Date]))
ORDER BY Year(DateAdd("m",3,[Approval_Date])) DESC; TABLE FILE SQLOUT PRINT * ON TABLE HOLD FORMAT &FMT END -GOTO DONE
-PATENTS
SET SQLENGINE=SQLMSS
SQL SQLMSS
SELECT Year(DateAdd("m",3,[IssDate])) AS FY, Count(PatNumber) AS [Count of
Patents Issued]
FROM TBLDOC_COUNTRY_APPS
WHERE (((IssDate) Is Not Null))
GROUP BY Year(DateAdd("m",3,[IssDate])) ORDER BY Year(DateAdd("m",3,[IssDate])) DESC; TABLE FILE SQLOUT PRINT * ON TABLE HOLD FORMAT &FMT END -GOTO DONE
-DONE
END
January 04, 2005, 10:39 PM
drew billingsleayou might change:
ON TABLE HOLD FORMAT &FMT
to
ON TABLE PCHOLD FORMAT &FMT
the HOLD will hold it in the agent workspace on the webfocus reporting server, PCHOLD sends the output to the client.
hth,
drew
January 04, 2005, 10:51 PM
<Derek>Hi Drew,
I tried that and now I'm receiving a different error. I get the windows cannot open this file box. File: WFServlet[1]. Then it tries to find the right program to open it. I click OK and get HTTP 400 - Bad Request
Internet Explorer
This is what the URL looks like...
http://shell.windows.com/fileassoc/0409/xml/redir.asp?Ext=ƘĀJanuary 07, 2005, 10:06 PM
<Derek>Drew,
I figured it out. I should have been using...
TABLE FILE SQLOUT
PRINT *
ON TABLE SET ONLINE-FMT &FMT
Thanks for the help.