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.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
Saving Report Data
 Login/Join
 
gweller
Platinum Member
posted
I have a daily report that I run which reports daily transactions by individuals on various tables. I would like to be able to schedule the report to run, gather the data and store it within a separate SQL database. How is this best done?

Thanks in advance


WebFOCUS 8201M/Windows Platform
 
Posts: 109 | Registered: October 31, 2006Report This Post
Darin Lee
Virtuoso
posted Hide Post
I haven't looked into it much, but I know there is a relatively new feature that allows for hold files to be stored in databases such as DB2, Oracle (I assume SQL Server also).
Your scheduled procedures could just create (or Modify) hold files which would be stored in separate databases.


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
Trav
Platinum Member
posted Hide Post
I've had reasonable success with simply doing a hold file in a SQL format. There are a couple of caveats to be aware of:
  • The connection that you are using needs to have rights to create a table and write to it.
  • The table is created every time you do the HOLD. In other words, you need to have some mechanism to make sure the table doesn't exist beforehand because it will produce an error.


What I would recommend is creating a stored procedure or something that you can run before you do the hold that would drop the table (if it exists). If you need to get the data from somewhere else, then hold it to a focus file first, then connect to your destination database with a different connection/adapter, run the stored procedure, and then hold the focus file out as SQL. There are probably other ways to do this, but when I was playing around with it, that's what I formulated in my head. Your environment may have other constraints that warrant a different solution. I would definitely encourage you to play with those output types though.


Production: 7.6.6 WF Server  <=>  7.6.6 WF Client  <=>  7.6.6 Dev Studio
Testing: <none>
Using MRE & BID.  Connected to MS SQL Server 2005
Output Types: HTML, Excel, PDF
 
Posts: 230 | Location: Wichita, KS | Registered: May 27, 2005Report This Post
gweller
Platinum Member
posted Hide Post
quote:
or something t


As it is daily statistics I would like to be able to store the information without removing what info is already within the SQL table, so I can do historical trending etc...but that may come in handy down the road...


WebFOCUS 8201M/Windows Platform
 
Posts: 109 | Registered: October 31, 2006Report This Post
Trav
Platinum Member
posted Hide Post
Well, I think what you'd do then is treat the table that gets created as a "work" table.

So as an alternative, don't run the proc before you create the table. Instead hold your file out to sql and then run a stored procedure afterwards that will move the data from the work table into your permanent table -- and then simply drop the work table when it's done.

Same basic principle, just slightly modified for keeping your data long-term.


Production: 7.6.6 WF Server  <=>  7.6.6 WF Client  <=>  7.6.6 Dev Studio
Testing: <none>
Using MRE & BID.  Connected to MS SQL Server 2005
Output Types: HTML, Excel, PDF
 
Posts: 230 | Location: Wichita, KS | Registered: May 27, 2005Report This Post
Tony A
Expert
posted Hide Post
Why not use a combination of TABLE and SQL passthru?
TABLE FILE filename
other stuff
ON TABLE HOLD AS TEMPSTATS FORMAT SQLMSS (or what ever it is)
END
-RUN
-IF &LINES EQ 0 THEN :End_It;

SQL SET ENGINE etc...
SQL
  UPDATE STATSFILE
  SELECT * FROM webfocus.TEMPSTATS
;
END
-RUN
-:End_It

You will have to correct the code above as it is not meant to be totally syntactically correct (I'm at home now and have no access to prove it) but it should give you an idea to begin with.

In fact exactly as Trav suggests above Smiler

T


In FOCUS
since 1986		WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders