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] Creating Reporting Objects using SQL Passthrough

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Creating Reporting Objects using SQL Passthrough
 Login/Join
 
BenC
Gold member
posted
Hi,

We recently began creating reporting objects to be used in our dashboard through the Report Assistant tool. However, we began having restrictions when we wanted to create data files using SQL Passthrough statements.

Is there a way we can create either a Master file or a Reporting object using SQL Passthrough?

Thanks in advance.

This message has been edited. Last edited by: Kerry,
 
Posts: 68 | Registered: March 15, 2006Report This Post
GinnyJakes
Expert
posted Hide Post
By definition, SQL Passthru doesn't use a master.

I would think you would have to hold the data and master and write this code in the editor or Other and then do the Report Assist piece on the hold file.

Someone more hip to MRE than I am could probably give you a better answer.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Darin Lee
Virtuoso
posted Hide Post
That is correct Ginny. Reporting objects are based minimally on a master file description. I suppose it could be a currently existing MFD or one that has been created in a preliminary hold file generate from some SQL.

Having said that, I doubt that it would work putting the passthru into the "Other" area. When a user wants to create a new report from a reporting object, the first thing it looks for is the master file specified for the desired object. No code is run first. Thus, if the MFD only exists by executing the SQL passthru, and that code has not yet been executed, you would probably get a Master File not Found error.


Regards,

Darin


In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Francis Mariani
Expert
posted Hide Post
This probably won't help at all here, but you can use SQL on Masters - this happens when you don't specify the engine in the SQL statement or don't set the engine before the SQL statement. I'm not sure if this is called SQL Passthru but it is SQL.

This SQL code uses the Master for the CAR file:

SQL SELECT * FROM CAR
END


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
Darin Lee
Virtuoso
posted Hide Post
When you specify the engine name, that is the key for WF to use passthru and no MFDs are necessary. If you just SQL Select, that is not passthru and you must have an MFD. In either case, that will not help for reporting objects.


Regards,

Darin


In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Don Garland
Guru
posted Hide Post
Are there any updates on Reporting Objects and SQL Passthru?

I'm wondering if anyone has developed a technique or two for this.


WebFOCUS Administrator @ Worldpay FIS
PROD/DEV/TEST: 8204, SANDBOX: 8206 soon - BIP, Reportcaster, Resource Manager, EUM, HyperStage soon, DB: HIVE,Oracle,MSSQL
 
Posts: 291 | Location: Greater Cincinnati  | Registered: May 11, 2005Report This Post
j.gross
Virtuoso
posted Hide Post
If the 'sql passthru' code is static, you can have the dba store it as a sql View; than create a webfocus synonym, and base your reporting object on that.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Don Garland
Guru
posted Hide Post
True, But I need to push some filtering into an in-line view and I'm hoping I can provide that within a Reporting Object.


WebFOCUS Administrator @ Worldpay FIS
PROD/DEV/TEST: 8204, SANDBOX: 8206 soon - BIP, Reportcaster, Resource Manager, EUM, HyperStage soon, DB: HIVE,Oracle,MSSQL
 
Posts: 291 | Location: Greater Cincinnati  | Registered: May 11, 2005Report This Post
j.gross
Virtuoso
posted Hide Post
The filter condition would effectively applied as ...

select * from [ select that defines the view ] where [ logical screening condition ]

so the TABLE's WHERE would in effect be treated as WHERE TOTAL.

If the filtering logically needs to be done at the incoming detail level, that's a problem.

But if WHERE TOTAL (or HAVING) would generate the intended net result for your filters, my suggestion is feasible, and the sql server would probably optimise appropriately.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
WF_IL
Platinum Member
posted Hide Post
Hi

You can create a master file to an SQL script
1. Create .Sql file with your SQL statements
2. Place this script in app path folder
3. When you create the webfocus synonym you can select to create it for SQL script

in this scenario you don't need a DBA to create a view on the database level

As far as I know there are going to be new features which will support webfocus variables passed to the SQL scripts so in the future you could define filters on the master file level or filters in the reporting object which will prompt the user for these type of variables


Yours,
Eran
SRL Products

http://www.srl.co.il

 
Posts: 97 | Location: Tel Aviv, Israel | Registered: November 20, 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] Creating Reporting Objects using SQL Passthrough

Copyright © 1996-2020 Information Builders