Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Utilizing specific procedure output as input for other procedures
Go
New
Search
Notify
Tools
Reply
  
Utilizing specific procedure output as input for other procedures
 Login/Join
 
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.


Windows 8203 All output formats
 
Posts: 7 | Registered: November 30, 2018Reply With QuoteReport 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 7.7.05M, 8.1.05M, 8.2.0.1M gen240 Windows, HTML, Excel, PDF
In Focus since 2007
 
Posts: 1807 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
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: 7 | Registered: November 30, 2018Reply With QuoteReport 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: 1207 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport 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 7.7.05M, 8.1.05M, 8.2.0.1M gen240 Windows, HTML, Excel, PDF
In Focus since 2007
 
Posts: 1807 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
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: 7 | Registered: November 30, 2018Reply With QuoteReport 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 7.7.05M, 8.1.05M, 8.2.0.1M gen240 Windows, HTML, Excel, PDF
In Focus since 2007
 
Posts: 1807 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
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: 7 | Registered: November 30, 2018Reply With QuoteReport 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, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1574 | Location: Enschede, Netherlands | Registered: August 12, 2010Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Utilizing specific procedure output as input for other procedures

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.