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     [SOLVED] Utilizing specific procedure output as input for other procedures

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Utilizing specific procedure output as input for other procedures
 Login/Join
 
Silver Member
posted
Good Afternoon,

I am making progress in my work and have another question, (reference to previous Q for context on what I am working towards: http://forums.informationbuild...057331/m/5177059096).

In our report we will have a few "sections". These sections represent a set of metrics which have a common selector reducing the initial set of data to a smaller subset. The metrics for that section then each present a count from this reduced data set, selecting on different fields. One of the requirements for each of these sections is that the first line be a count of the number of events in this particular subset of data. Easy enough. But then, for each metric, along with the count, we want to include a percentage which represents the portion that count is of the subset total. Sometimes pictures are better than words...

###########################################
Metric Section A

|-----------------------------------------|
| Metric | Event Count | % of all Events |
|-----------------------------------------|
| Total | 56 | 100.0 |
| A | 3 | 5.4 |
| B | 0 | 0.0 |
| C | 40 | 71.4 |
|-----------------------------------------|
###########################################

If this were all one return set I remember you can do aggregation across rows, (which may not have worked here anyway), but since each metric is it's own procedure I'm wondering how I can achieve this result. I'm thinking some form of a global variable which can be assigned after the initial procedure return but am struggling on finding documentation which can illuminate that solution. I found one post on the forum which suggests a HOLD can be performed on a procedure output and that can be passed to a global variable. Problem is my example may be a little too contrived... Each metric procedure return actually has 2, (possibly eventually 3), counts, not just the one. So it won't be one single value coming back but two.

This is still early in development so I'm definitely up for hearing options which involve reimagining our initial solution where each metric is it's own procedure.

I'm actively researching this right now so if I stumble across something in the docs I have I'll follow up with my findings.

Thanks for any guidance.

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


Windows 8203 All output formats
 
Posts: 47 | Registered: November 30, 2018Report This Post
Virtuoso
posted Hide Post
Sean,

First, please use the code tag, last one on the ribbon when posting sample code or sample result.
That way you will avoid all these dashes, pipes, ... since you will be able to align your text as you wish.
</>


Then using a similar technic may suits your need where you can have the total (C1 = Nb Country Model) at an upper level to then use it in a further calculation (AVG_CNTR_MDL).
If you don't want the C1 and C2 (AS 'Nb Car,Model') value displayed on the report just replace : AS '...' by NOPRINT
TABLE FILE CAR
SUM CNT.MODEL AS 'Nb Country,Model'
BY COUNTRY

SUM CNT.MODEL AS 'Nb Car,Model'
    SALES AS 'Sold'
    COMPUTE AVG_CNTR_MDL /P8.2C =  SALES / C1; AS 'Avg Sold,Per Country'
    COMPUTE AVG_CAR_MDL  /P8.2C =  SALES / C2; AS 'Avg Sold,Per Car'
BY COUNTRY AS 'Country'
BY CAR     AS 'Car'
END


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Silver Member
posted Hide Post
Thanks for the tip!

So it sounds like you are thinking in the same direction I was... Some variable, higher up that stores that count and can be used in further aggregation.

I'm having trouble implementing that though and I think it's due to a lack of experience in the actual code syntax you can see behind the scenes. All the training we have had so far uses the UI to build out things so I'm unfamiliar with the code that is generated. Where does the assignment of C1 occur? I would probably place this in the procedure that creates the hold which I will be counting on so I assume I'll use a global variable.

Should it be something like this:
TABLE FILE CAR
&&C1 = SUM CNT.MODEL AS 'Nb Country,Model'
BY COUNTRY
?

I've tried a similar implementation in my code with my table name and values but no luck yet.

Thanks and if anyone has any other thoughts on how to approach this it is greatly appreciated.


Windows 8203 All output formats
 
Posts: 47 | Registered: November 30, 2018Report This Post
Virtuoso
posted Hide Post
Sean

Try this

SET ASNAMES=ON
-DEFAULTH &COUNT='';
TABLE FILE CAR
SUM CNT.COUNTRY AS 'COUNT'
ON TABLE HOLD AS COUNT FORMAT ALPHA
END
-RUN 
-READFILE COUNT
-SET &&COUNT = &COUNT;
-TYPE &&COUNT
 


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2127 | Location: Customer Support | Registered: April 12, 2005Report This Post
Virtuoso
posted Hide Post
C1 and C2 are column references within the internal matrix. Tehe "C" refer to the fields in the SUM/PRINT section of your TABLE FILE. You can find documentation regarding this.

You cannot assign a variable/global variable within a fex as you did. A variable assignation need a -SET or must be read from a TABLE FILE.
As per example
-* A local variable definition
-SET &LOC1 = 'Local';

-* A global variable definition
-SET &&GLB1 = 'Global';

-* From a TABLE file to read more than one value
TABLE FILE CAR
SUM CNT.MODEL
BY COUNTRY
ON TABLE HOLD AS NBMOD
END
-RUN

-SET &NBREC = &LINES;

-REPEAT SETMODVAR FOR &I FROM 1 TO &NBREC STEP 1
-READFILE NBMOD
-SET &CNTR&I.EVAL = TRIM_(BOTH, ' ', &COUNTRY);
-SET &NBMOD&I.EVAL = &MODEL;
-TYPE &CNTR&I.EVAL : &NBMOD&I.EVAL
-SETMODVAR

-* From a TABLE file to read one value of each variable and can rename the name with AS (AS 'NBCAR') 
SET ASNAMES = ON
TABLE FILE CAR
SUM CNT.MODEL
    CNT.CAR AS 'NBCAR'
BY COUNTRY
WHERE COUNTRY EQ 'ENGLAND';
ON TABLE HOLD AS NBMOD
END
-RUN

-READFILE NBMOD

-* Variable are available as is
-TYPE &COUNTRY, &NBCAR, &MODEL

-* Or you can reassign when you need to perform other manipulation such as below
-SET &CNTR = TRIM_(BOTH, ' ', &COUNTRY);
-SET &NBMOD = &MODEL;
-TYPE &CNTR, &NBCAR, &NBMOD


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Silver Member
posted Hide Post
Thanks for the help so far, I think I'm almost there. How do I apply the following code to a hold though?

SET ASNAMES=ON
-DEFAULTH &COUNT='';
TABLE FILE CAR
SUM CNT.COUNTRY AS 'COUNT'
ON TABLE HOLD AS COUNT FORMAT ALPHA
END
-RUN
-READFILE COUNT
-SET &&COUNT = &COUNT;
-TYPE &&COUNT

Something like:

FILEDEF HOLD_TEMP DISK foccache/temp.ftm
SET ASNAMES=ON
-DEFAULTH &COUNT='';
TABLE FILE HOLD_TEMP
SUM CNT.WHATEVERCOLUMN AS 'COUNT'
ON TABLE HOLD AS COUNT FORMAT ALPHA
END
-RUN
-READFILE COUNT
-SET &&COUNT = &COUNT;
-TYPE &&COUNT

What I'm doing is creating a hold that can be used for all the other metrics. A check occurs which looks to see if the hold already exists, (so it doesn't get re-created for every other metric), and then a count of the hold file is stored in a global variable so it can be used in the aggregations the metrics will do.

So the "ON TABLE HOLD AS COUNT" is the life and scope of this hold limited to the procedure and the user making the request?

Thanks again.


Windows 8203 All output formats
 
Posts: 47 | Registered: November 30, 2018Report This Post
Virtuoso
posted Hide Post
First, using READFILE you don't need to specify FORMAT ALPHA
ON TABLE HOLD AS COUNT is enough.

Second, yes a ON TABLE PCHOLD AS abc exist only for the life of the request BUT may exist for other procedure if they all belong to the same request.
Furthermore if you hold it in FOCCACHE which is a temporary folder.
Such as a main proc calling children and the HOLD as been created in top child or within the main.

But if you call the same fex (the one that hold COUNT) several times from another fex (the one that creates the metric), you SHOULD NOT use a global variable.
Otherwise, each time you perform -SET &&COUNT, the value will be overwritten by the new one.
You should use a local variable &COUNT, that way each metrics will have its own &COUNT value even if at end, it's the same.

I suggest that if the hold file COUNT may exist only once and remain the same, let say for the whole day, that you have this file created with a fex called by RC during night (or when ever it's possible) before any other possible call by report/dashboard and have this file saved (hold) on disk on than in FOCCACHE. A path that can be accessed by any of your fex.
You'll then be able to use it whenever you want with always the same result.

But I may mist some spec to have a proper suggestion.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Silver Member
posted Hide Post
Good point about the re-setting of the global variable across multiple calls, I'll keep that in mind.

The count of the hold file values will not remain the same for the whole day. It would be quite possible for it to change even between requests of the final report.

To go into more about the requirements / process I've used so far in case a better solution is available:

I'm using the foccache hold files to cache the base data, (essentially a series of guids to the relevant rows in our DB), for the various metrics that will display in the report, (upwards of 30 different procedures running on the report). Upon a new report request these should be dumped and re-cached as with what I said before, it COULD be a different set of guids we are analyzing. Feel free to suggest a better option to handling this, certainly open to new ideas.

Cycling back to my previous post, how would I approach pulling that count into a variable, (be it a local variable if I switch to that route)? I tried the code I presented to no avail so not sure what I'm missing.

Thanks!


Windows 8203 All output formats
 
Posts: 47 | Registered: November 30, 2018Report This Post
Virtuoso
posted Hide Post
I think what you're looking for is:
FILEDEF HOLD_TEMP DISK foccache/count.ftm
-RUN
-READFILE HOLD_TEMP
-DEFAULTH &COUNT='';


Note:

  • The addition of -RUN here is important, or the Dialog Manager code will run before the FOCUS FILEDEF command has executed.
  • I prefer to define the -DEFAULTH after calling -READFILE, to prevent a warning that the value of the variable gets overwritten by it.
  • Possibly, you need to add SET HOLDLIST = PRINTONLY where you create your ALPHA file, or the entire matrix will be stored in the ALPHA file - but that should only result in extra variables being set, not in a lack of them.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Silver Member
posted Hide Post
So I don't see an actual assignment so I'm assuming the complete example could be something like this?

FILEDEF HOLD_TEMP DISK foccache/hold_temp.ftm
-RUN
-READFILE HOLD_TEMP
-DEFAULTH &TEMPCOUNT = 0;
-SET &TEMPCOUNT = &LINES;
-TYPE &TEMPCOUNT

I'm thinking it's not though... in a test procedure I run my foccache setup procedures that create two cache files. I then perform the above in another file for each foccache file and end up with the same count twice. The foccache files are definitely different sizes.

What is interesting is if I remove one of the counts and perform the count on the smaller file, I end up with the larger count. Which makes me think the &LINES isn't doing anything in the above code and the value it contains is actually hanging around from the initial foccache creation.

Thoughts?


Windows 8203 All output formats
 
Posts: 47 | Registered: November 30, 2018Report This Post
Virtuoso
posted Hide Post
It seems I forgot to copy the TABLE FILE that actually writes to the file that we're reading into that example.

Full example is here. Tested it too, this time Wink

First create the session file:
SET ASNAMES = ON
SET HOLDLIST = PRINTONLY

TABLE FILE CAR
SUM CNT.COUNTRY AS 'COUNT'
ON TABLE HOLD AS FOCCACHE/HOLD_TEMP FORMAT ALPHA
END

-* Tell us where it went
WHENCE HOLD_TEMP MASTER

-* Verify readability
-READFILE HOLD_TEMP
-DEFAULTH &COUNT=0;

-TYPE Count: &COUNT



Next, use the session file from another procedure (say, a drilldown fex):
FILEDEF HOLD_TEMP DISK FOCCACHE/hold_temp.ftm
-RUN 

-READFILE HOLD_TEMP
-DEFAULTH &COUNT=0;

-TYPE Count: &COUNT


Obviously, both fexes should be run from the same session, or you will be looking at different foccache sub-directories.

I tested the above in two subsequent calls from the Command Console. Both gave the same result for &COUNT.

It does appear, however, that the FILEDEF doesn't add anything at all here. Renaming the FILEDEF file results in an error (TABLE not found), while leaving it out works just as well.
Apparently -READFILE has no qualms with files in the foccache, as long as there's a master file to point it to.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Silver Member
posted Hide Post
Excellent, thank you! Was able to get this working.

Now, the list I'm counting is already in foccache, is there a way to count it without writing out to foccache again? If I FILEDEF the foccache file and do a READFILE I was hoping it would populate &LINES but that doesn't seem to be the case.

Writing to foccache works but I was just hoping to not have to write another piece to disk.


Windows 8203 All output formats
 
Posts: 47 | Registered: November 30, 2018Report This Post
Virtuoso
posted Hide Post
No, -READFILE won't populate &LINES, but it does read the value of &COUNT from the file.

You could read the list in foccache using TABLE FILE, of course. That does populate &LINES. That seems a bit superfluous if you already have that value in &COUNT though?

Perhaps your easiest option is to use a multi-verb for the cache, with the COUNT at the top level and the detail records under it?


TABLE FILE CAR
SUM
    CNT.COUNTRY AS 'COUNT'
PRINT
    CAR
BY COUNTRY
ON TABLE HOLD AS foccache/hold_temp FORMAT FOCUS
END
-RUN

-READFILE hold_temp 
-DEFAULTH &COUNT = 0;
-TYPE Count: &COUNT

TABLE FILE hold_temp
PRINT CAR
BY COUNTRY
HEADING
"COUNT: &COUNT"
END


That gives you both options in a single file.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report 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     [SOLVED] Utilizing specific procedure output as input for other procedures

Copyright © 1996-2020 Information Builders