Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] SQL pass through and mutiple hold files to multiple excel tabs

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] SQL pass through and mutiple hold files to multiple excel tabs
 Login/Join
 
smahe
Member
posted
I am able to generate excel output from a single pass through sql, when trying to introduce another pass through the second hold file doesn't get recognized. Also, tried using EXL2K OPEN and EXL2K CLOSE commands which still generates single tab only.

 
ENGINE DB2 SET DEFAULT_CONNECTION AIM
SQL DB2
SELECT *
FROM A
TABLE
ON TABLE HOLD AS HOLD1
END
-RUN

SET NODATA = ''
TABLE FILE HOLD1
PRINT *
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET EMPTYREPORT ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,$
ENDSTYLE
END

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


WF 8.0,
MS Windows 7, HTML, PDF
 
Posts: 3 | Registered: August 26, 2013Report This Post
Francis Mariani
Expert
posted Hide Post
Using SQL to create HOLD files which are then used to create a compound Excel document isn't the issue.

Have you run your fex without the PCHOLD format XLSX? Perhaps one of the other SQL commands is failing...

Or perhaps the COMPOUND statements are not in the correct place.

Here's an example with three tabs in an XLSX document:

SET NODATA = ''
SET PAGE-NUM = NOLEAD
SET EMPTYREPORT = ON
SET ASNAMES = ON
-RUN

SET COMPOUND=OPEN

SQL
SELECT
  CAR
, SUM(SALES)
FROM CAR
GROUP BY CAR
ORDER BY CAR
;
TABLE
ON TABLE HOLD AS HOLD_CAR
END
-RUN

TABLE FILE HOLD_CAR
PRINT *
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET STYLE *
-*INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,$
TYPE=REPORT, TITLETEXT='Sales by Car', $
ENDSTYLE
END
-RUN

SQL
SELECT
  COUNTRY
, SUM(SALES)
FROM CAR
GROUP BY COUNTRY
ORDER BY COUNTRY
;
TABLE
ON TABLE HOLD AS HOLD_COUNTRY
END
-RUN

TABLE FILE HOLD_COUNTRY
PRINT *
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET STYLE *
-*INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,$
TYPE=REPORT, TITLETEXT='Sales by Country', $
ENDSTYLE
END
-RUN

SET COMPOUND=CLOSE

SQL
SELECT
  MODEL
, SUM(SALES)
FROM CAR
GROUP BY MODEL
ORDER BY MODEL
;
TABLE
ON TABLE HOLD AS HOLD_MODEL
END
-RUN

TABLE FILE HOLD_MODEL
PRINT *
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET STYLE *
-*INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,$
TYPE=REPORT, TITLETEXT='Sales by Model', $
ENDSTYLE
END
-RUN


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Francis Mariani
Expert
posted Hide Post
SET NODATA = ''
SET PAGE-NUM = NOLEAD
SET EMPTYREPORT = ON
SET ASNAMES = ON
-RUN

SQL
SELECT
  CAR
, SUM(SALES)
FROM CAR
GROUP BY CAR
ORDER BY CAR
;
TABLE
ON TABLE HOLD AS HOLD_CAR
END
-RUN

TABLE FILE HOLD_CAR
PRINT *
ON TABLE NOTOTAL
ON TABLE SET COMPOUND OPEN
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET STYLE *
-*INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,$
TYPE=REPORT, TITLETEXT='Sales by Car', $
ENDSTYLE
END
-RUN

SQL
SELECT
  COUNTRY
, SUM(SALES)
FROM CAR
GROUP BY COUNTRY
ORDER BY COUNTRY
;
TABLE
ON TABLE HOLD AS HOLD_COUNTRY
END
-RUN

TABLE FILE HOLD_COUNTRY
PRINT *
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET STYLE *
-*INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,$
TYPE=REPORT, TITLETEXT='Sales by Country', $
ENDSTYLE
END
-RUN

SQL
SELECT
  MODEL
, SUM(SALES)
FROM CAR
GROUP BY MODEL
ORDER BY MODEL
;
TABLE
ON TABLE HOLD AS HOLD_MODEL
END
-RUN

TABLE FILE HOLD_MODEL
PRINT *
ON TABLE NOTOTAL
ON TABLE SET COMPOUND CLOSE
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET STYLE *
-*INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,$
TYPE=REPORT, TITLETEXT='Sales by Model', $
ENDSTYLE
END
-RUN


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Francis Mariani
Expert
posted Hide Post
SET NODATA = ''
SET PAGE-NUM = NOLEAD
SET EMPTYREPORT = ON
SET ASNAMES = ON
-RUN

SQL
SELECT
  CAR
, SUM(SALES)
FROM CAR
GROUP BY CAR
ORDER BY CAR
;
TABLE
ON TABLE HOLD AS HOLD_CAR
END
-RUN

TABLE FILE HOLD_CAR
PRINT *
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT XLSX OPEN
ON TABLE SET STYLE *
-*INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,$
TYPE=REPORT, TITLETEXT='Sales by Car', $
ENDSTYLE
END
-RUN

SQL
SELECT
  COUNTRY
, SUM(SALES)
FROM CAR
GROUP BY COUNTRY
ORDER BY COUNTRY
;
TABLE
ON TABLE HOLD AS HOLD_COUNTRY
END
-RUN

TABLE FILE HOLD_COUNTRY
PRINT *
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET STYLE *
-*INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,$
TYPE=REPORT, TITLETEXT='Sales by Country', $
ENDSTYLE
END
-RUN

SQL
SELECT
  MODEL
, SUM(SALES)
FROM CAR
GROUP BY MODEL
ORDER BY MODEL
;
TABLE
ON TABLE HOLD AS HOLD_MODEL
END
-RUN

TABLE FILE HOLD_MODEL
PRINT *
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT XLSX CLOSE
ON TABLE SET STYLE *
-*INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,$
TYPE=REPORT, TITLETEXT='Sales by Model', $
ENDSTYLE
END
-RUN


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
smahe
Member
posted Hide Post
It worked. I am able to generate multiple tabs from multiple hold files. Thank you Francis.

On another note -- is there a way to pass parameters in a pass through report? I got an encrypted field which needs to be passed as parm.


WF 8.0,
MS Windows 7, HTML, PDF
 
Posts: 3 | Registered: August 26, 2013Report This Post
Francis Mariani
Expert
posted Hide Post
Please clarify "encrypted field". Is this an encrypted column in your DB2 table that you need to filter on with a value passed from a parameter?


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
smahe
Member
posted Hide Post
It is an DB2 encrypted field which requires encryption key while running the SQL. Here the 'password' field needs to be a parameter.

 
ENGINE DB2 SET DEFAULT_CONNECTION AIM
SQL DB2
SELECT DECRYPT_CHAR(Field,'password') AS Decrypted field
FROM A
TABLE
ON TABLE HOLD AS HOLD1
END
-RUN

SET NODATA = ''
TABLE FILE HOLD1
PRINT *
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET EMPTYREPORT ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,$
ENDSTYLE
END


WF 8.0,
MS Windows 7, HTML, PDF
 
Posts: 3 | Registered: August 26, 2013Report This Post
Francis Mariani
Expert
posted Hide Post
Dialogue Manager can be embedded almost anywhere in the code (except for the GUI world):

ENGINE DB2 SET DEFAULT_CONNECTION AIM
SQL DB2
SELECT DECRYPT_CHAR(Field,'&PASSWORD_PARM') AS Decrypted field
FROM A
TABLE
ON TABLE HOLD AS HOLD1
END
-RUN

SET NODATA = ''
TABLE FILE HOLD1
PRINT *
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET EMPTYREPORT ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,$
ENDSTYLE
END

This should work, unless the parameter has one or more single quotes in it, though I think
DECRYPT_CHAR(Field, &PASSWORD_PARM.QUOTEDSTRING )
will work for all values, including quotes.

This message has been edited. Last edited by: Francis Mariani,


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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] SQL pass through and mutiple hold files to multiple excel tabs

Copyright © 1996-2020 Information Builders