Focal Point
[CLOSED] Filtering a WebFOCUS View in the Master File

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/652100022

August 25, 2009, 09:46 AM
IBForum
[CLOSED] Filtering a WebFOCUS View in the Master File
I have an .ACX and .MAS file that points to and defines a Sybase table to WebFOCUS. The Sybase table hold daily prices for a security. The Sybase table also contains the current month's month-end date which holds the most recently updated price for a particular security. In other words, the 8/31/2009 record in the Sybase table would contain the closing price from yesterday, 8/24/2009. My question is...is it possible for me to filter out all the daily prices and only include the month-end prices at the .MAS file level? I know that each report FEX could be easily programmed to only include month-end dates, but I'd like to do it at a level further up...as if we had created a separate database table that only contained month-end dates going back to inception. Hopefully, this makes sense. I played around with defining a FILTER in the .MAS file, but it returned 0 records in my TABLE request.

This message has been edited. Last edited by: Kerry,


WF 8.1.05, Windows Server 2012 R2
August 25, 2009, 10:38 AM
mgrackin
What is your goal? Are you trying to reduce the amount of records returned from the database? It sounds like the following code will do this for you in a FOCEXEC.

TABLE FILE SYBASEDB
SUM MAX.MTHEND_PRICE
BY MTHEND_DATE
END

This should translate into SQL that brings back only one record for each month.


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
August 25, 2009, 12:57 PM
IBForum
The goal was to reduce the number of records returned, but do it thru the .MAS or .ACX file...not in the FOCEXEC. Also, the only date field available in the file changes daily, so I have to create a month-end date to evaluate against for the selection criteria. Leap years may be tricky.


WF 8.1.05, Windows Server 2012 R2
August 25, 2009, 02:26 PM
Doug
1) Perhaps your DBA can create a VIEW for you that accomplishes this. you can use Micky's code as a base for your request.
2) "Leap years may be tricky": Not really tricky... With Smart Dates...
August 26, 2009, 04:18 AM
Gobinath Velusamy
Hi

Instead of using Master file use the Sqlpassthrough to reduce the number of records to webfocus server


WebFOCUS 7610
Windows