I have a fex that creates a holdfile used by several external fexes. Lets say I prompt in this initial fex for Dept, Report Type and Period, all multi-selects.

How do I pass these values onto subsequent report fexes referencing the hold file - for example to place in a report header so the user knows what was selected...? Should they somehow be included as items in the hold file..I did not have much luck with that.

Ideas?

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

I tend to solve that using an FORMAT ALPHA hold-file for each value-list and then loop over the rows in each file to concatenate them into several dialog manager variables.

For example:

TABLE FILE INITIAL
SUM Dept
ON TABLE HOLD AS DEPTS FORMAT ALPHA
END
-RUN
-SET &DEPTSTR = '';
-SET &COMMA = ' ';
-SET &Dept = '';
-REPEAT :DEPTLOOP FOR &ROW FROM 1 TO &LINES;
-READFILE DEPTS
-SET &Dept = TRUNCATE(&Dept);
-TYPE Row &ROW: Dept = &Dept

-SET &DEPTSTR = &DEPTSTR || &COMMA | &Dept.QUOTEDSTRING;
-SET &COMMA = ',';
-:DEPTLOOP

-TYPE Departments: &DEPTSTR

quote:

TABLE FILE INITIAL
SUM Dept
ON TABLE HOLD AS DEPTS FORMAT ALPHA
END

...would generate only one row; it needs some modification (BY Dept; or PRINT DST.).

Indeed it does! Seems I mixed up the two lines that were supposed to be there while deciding what to name the table and columns. That should have been:

SET HOLDLIST = PRINTONLY

TABLE FILE INITIAL
SUM FST.Dept
BY DEPT NOPRINT
ON TABLE HOLD AS DEPTS FORMAT ALPHA
END

If you use a -INCLUDE in the initial program, the variables come along for the ride.

-DEFAULTS &DEPT=;
-DEFAULTS &RPTNAME=;
-DEFAULTS &YEAR=;

TABLE FILE INITIAL
PRINT FIELD1 FIELD2 FIELD3
BY DEPT
IF DEPT EQ &DEPT
IF YEAR EQ &YEAR
ON TABLE HOLD AS INITTAB
END
-RUN

-IF &RPTNAME EQ 'RPT1' GOTO INC1;
-IF &RPTNAME EQ 'RPT2' GOTO INC2;
-EXIT

-INC1
-INCLUDE RPT1
-EXIT

-INC2
-INCLUDE RPT2
-EXIT

I am glad this is possible but I am not quite following what needs to be done.

To start I have code like this, it evaluates what will be prompted for in a future where clause, if the user selects ALL, (FOC_NULL) then it puts the literal: 'ALL' in there, otherwise, it will conatin something like: PEDIATRICS OR MEDICINE...should the user choose two specialties for examlpe.


-SET &SPECIALTYPROMPT = IF &IDXSPECIALTY EQ '_FOC_NULL' THEN 'All' ELSE &IDXSPECIALTY;


Down a few lines, the user is prompted (MultiSelect) for Specialty in the WHERE Clause. Here is that code...sorry about the long table names!


WHERE ( TBLMSTR_ACTUALBUDGETUNITWRVUCOMBINEDDETAIL.TBLMSTR_ACTUALBUDGETUNITWRVUCOMBINEDDETAIL.IDXSPECIALTY EQ &IDXSPECIALTY.(OR(FIND TBLMSTR_ACTUALBUDGETUNITWRVUCOMBINEDDETAIL.TBLMSTR_ACTUALBUDGETUNITWRVUCOMBINEDDETAIL.IDXSPECIALTY,TBLMSTR_ACTUALBUDGETUNITWRVUCOMBINEDDETAIL.TBLMSTR_ACTUALBUDGETUNITWRVUCOMBINEDDETAIL.IDXSPECIALTY IN TBLMSTR_ACTUALBUDGETUNITWRVUCOMBINEDDETAIL)).enter specialty. )


All this gets called via an HTML Page. The data is stored in a HOLD file available to external fexes.

Subsequent HTML Push buttons pointing to various oher fexes yeild various reports off this hold file. Id like to imbed whatever the user selects for: Specialty in a page header for reference....but it exists in the original fex and not the subsequent fexes that use the hold file.

I am not sure if I need to make these fields part of the hold file....or what to do.

Robert,
Have you thought about creating two separate hold tables - one to hold the data and another to hold the selection criteria, or perhaps a multi-path Focus hold file? Essentially by building an initial Hold file you are creating a mini datamart, so there should be a way to store those initial selection criteria as normal, separate, variables.

i hope you're naming your extract something other than HOLD...
just make the parameters selected fields in that extract file
EG if COUNTRY and CAR are selected, then
APP HOLD someapp
TABLE FILE IBISAMP/CAR
SUM SALES DCOST BY COUNTRY BY CAR BY MODEL BY SEATS
IF COUNTRY IS &MYCOUNTRY.QUOTEDSTRING
IF CAR IS &MYCAR.QUOTEDSTRING
ON TABLE HOLD AS MYEXTRACT FORMAT [alpha or focus or xfocus even}
END
...
then in subsequent fexes, count the number of distinct values in COUNTRY, if just 1, then use that value in your HEADING
if>1, then probably not picked as a parm
same with CAR,
lather, rinse,repeat

i like George's idea better, i'm thinking

no, hold files are not gernerically named: HOLD.

I guess I could make another HOLD file holding these values...not sure to what end though. A future fex has to point to one HOLD file or another unless you are suggesting I join them some how....?

I did try throwing them into a DEFINE with the thought of then throwing the newly defined item into my BY clause. The thought was that essentially every row would have a column containing the values the user selected when prompted, and that would then be in the HOLD file and available to any future fex. It seems a little inefficient but I thought it should work. It did not.

When I tried to SET say:
SET &SPECIALTYPROMPT = IF &IDXSPECIALTY EQ '_FOC_NULL' THEN 'All' ELSE &IDXSPECIALTY;


Then added a DEFINE, say:
SPECIALTYPAGEHEADER/A200 = &SPECIALYPROMPT

with echo on, you can see it trying to set SPECIALTYPAGEHEADER to what ever the user has chosen...say: 'CLINIC' OR 'MEDICINE'...but it errors out. I really want the value to be the literal 'CLINIC OR MEDICINE' I guess...

I tried wrapping quotes around &SPECIALTYPROMPT...still error'd out.

I thought I was onto something but now can not get the user prompted selction into a defined variable....

There's also the option of using WHERE IN FILE.

If you arrange to have a HOLD-file for each property, you can add such a WHERE-clause for each file/property.

For example:

TABLE FILE INITIAL
SUM FST.FIELD1
BY FIELD1 NOPRINT
ON TABLE HOLD AS FOCCACHE/FIELD1_HOLD
END

TABLE FILE INITIAL
SUM FST.FIELD2
BY FIELD2 NOPRINT
ON TABLE HOLD AS FOCCACHE/FIELD2_HOLD
END

TABLE FILE INITIAL
PRINT *
WHERE FIELD1 IN FILE FIELD1_HOLD;
WHERE FIELD2 IN FILE FIELD2_HOLD;
WHERE FOO EQ BAR;
HEADING
"The actual report"
END

Supposedly you're creating those files in the top-level report?
We do something like that and store those HOLD-files in the FOCCACHE, so that it stays available to the user in their entire session. No need to pass those values on with the URL/drilldown that way and no need to interpret the contents quite so often.


More advanced implementation:

We went a bit further even and (in some reports) allow a user to have several such HOLD-files from multiple top-level report runs, so that windows or tabs they kept open still update correctly (normally a new report-run overwrites the HOLD-files that the contents of the drilldown report were based on, so a refresh would show the "new" results).

Basically, we increment a number each time a user runs our top-level report (using a sequence generator in one of our RDBMSes) and append that to the name of the HOLD-file. The sequence number is passed on with our drilldowns so that we can construct the correct HOLD-file name to query (in reality we pass on the HOLD-file name with the sequence number appended, I don't recall why we chose to go that route).

To manage all that, we created a number of -INCLUDEs that automate much of that process.

quote:

A future fex has to point to one HOLD file or another unless you are suggesting I join them some how....?

...ah, but a future fex can read as many files as you want...so you read the little hold file first, extract the parameters that you've parked there, use a -READ to do that. and then use those &vars you create with your -READ to populate the heading of your final report...that final report reading your second hold file.
see?

Maybe I've just missed something in this whole conversation... But why are we even using a hold file to begin with? In order to create the hold file for each fex it seems that your passing variables x/y/z anyways. So why not skip creating the hold file/reading the same variables out, and just post the initial variables in the Header?

I took a few more reads of the question, and I think I understand what you are asking.... I think if this were me, I would use -write to write the Header with the variables to a text file and then use a -include as the first lines of the header. Just my two cents. Why bother re-reading the hold file or making another. Just write the header once and include it everywhere.

quote:

...ah, but a future fex can read as many files as you want...so you read the little hold file first, extract the parameters that you've parked there, use a -READ to do that. and then use those &vars you create with your -READ to populate the heading of your final report...that final report reading your second hold file.
see?

Thanks All. These approaches have promise but are outside the bounds of what I have done in the past:

...ah, but a future fex can read as many files as you want...so you read the little hold file first, extract the parameters that you've parked there, use a -READ to do that. and then use those &vars you create with your -READ to populate the heading of your final report...that final report reading your second hold file.
see?



I took a few more reads of the question, and I think I understand what you are asking.... I think if this were me, I would use -write to write the Header with the variables to a text file and then use a -include as the first lines of the header. Just my two cents. Why bother re-reading the hold file or making another. Just write the header once and include it everywhere


First, I can't even tinker right now because ther server has been down to two days...serves me right for trying to create a new report over the weekend I guess.

Second, The initial hold file provides data for a variety of subsequent reports. Any or all of the data therein might be selected on future reports. I would not want to requery the data set each time the user wants a new flavor of something, or if they wanted the same report but then to include or exclude something. The hold file is available for the user's entire session. Initial reports off this file are more summary level with drill downs available. This hold file also acts as the source for both.

The user has quite a bit of flexsibility in selectng what their initial data set is (hold file). Date ranges, Depts, Employees etc. etc. can all be chosen On the initial report off the hold file I want to be able to show them what they selected. Yes, if this were a report instead of a hold file it works fine but thats not what we need in this case.


The -write solution descibed above, to make a text file and then include it in the header sounds promising...Have you done this? I'll have to find the syntax for that...never knew you could do that.

The idea of sticking the parameters in a little hold file and using that file to populate variables in a future fex sounds doable as well...again, I have never tried that.

I hope I can figure out the syntax for each of these methods and try them...when I get my server back!

Thanks!

Server is back but no luck. Lets tak a simple example.

Lets say I want to make the model/s chosen avaiable in an external fex referencing the HOLD file created. Ex: if I choose Camry and Sienna I would want to have this in my page header in an external fex referencing the hold file created:

Car Model Report
Select Model: Camry or Sienna


How do I get the model over to the external fex?


TABLE FILE CAR
SUM
CAR.BODY.SALES
BY LOWEST CAR.COMP.CAR
BY LOWEST CAR.CARREC.MODEL
WHERE CAR.CARREC.MODEL EQ &MODEL.(OR(FIND CAR.CARREC.MODEL,CAR.CARREC.MODEL IN CAR)).enter model/s.;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS CARMODELS FORMAT FOCUS
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END

anyone?...I left a simple car example and what I was trying to do...surely there is a simple solution?

how about someone from IBI...surely there is a way to do this...****, the Cognos guys on the epic team are beginning to chuickle!

What you want is QUOTEDSTRING

From you car example:

car_one.fex

TABLE FILE CAR
SUM 
     CAR.BODY.SALES
BY  LOWEST CAR.COMP.CAR
BY  LOWEST CAR.CARREC.MODEL
WHERE CAR.CARREC.MODEL EQ &MODEL.(OR(FIND CAR.CARREC.MODEL,CAR.CARREC.MODEL IN CAR)).enter model/s.;
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE HOLD AS CARMODELS FORMAT FOCUS
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END


EX /WFC/Repository/Public/car_two.fex MODEL=&MODEL.QUOTEDSTRING  

car_two.fex

-SET &MODELIN = IF &MODEL EQ _FOC_NULL THEN 'All' ELSE &MODEL.QUOTEDSTRING;

TABLE FILE CARMODELS
SUM
SALES
BY LOWEST CAR
BY LOWEST MODEL
HEADING
"You chose the following Models: &MODELIN"
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
$
ENDSTYLE
END
  

Example output:







Obviously you would want to pretty up the output for the heading.

Robert,

A couple things. 1) This is a user forum in which people volunteer there time. We have full time jobs and will not always be able to provide you with answers right away. 2) For the same reason as 1, weekends tend to not get a lot of responses because people are spending their time with their families, not trolling a user forum on their time off. Please be patient as people here are generally helping you out of a desire to be helpful.

As for your "Cognos" buddies, I guarentee you I could do plenty of things in WF that they can't in Cognos and vice versa. Each tool has their strong points, so please don't insult the people that are trying to offer you advice by making comments about things you don't understand.

The help files have a lot of information about how to construct -write commands. I suggest you open the help files and do some reading about the multiple ways this can be done if the already provided explanations of -READ doesn't work for you.

Stu...thanks...I am on vacation with (believe it not in this day and age...erractic wireless connectivity)...look forward to trying the QUOTEDSTRING.

Eric,
Yes I understand weekends etc...I was hoping for an answer because I had a deliverable I wanted to to put to rest before vacation. All was for not because the server ended up going down for 3 days.

I know this thing can run circles around Cognos but, at times, the thing is frustrating in trying to the basics...this was one of those times for me.

There are many help sources but I was unable to get the answer I needed.

Thanks All.

Stu,

One thing I thought of....how will this solution work if Cat two fex is a second stand alone procedure? How will it know the value od &MODEL...?

Looking forward to trying this out though when I get back. Thanks!

Robert,

In my example I'm explicitly calling the second procedure and passing the value of &MODEL via the EX command.

If the second (third or forth) procedures were being called external to the first FEX then you could either use global variables or write to the hold file via a DEFINE or COMPUTE.

-*New CAR_ONE.FEX - NOTE: you don't want to both &&PARAMETER and DEFINE. Choose one

-SET &&MODEL = IF &MODEL EQ _FOC_NULL THEN 'All' ELSE &MODEL.QUOTEDSTRING;
DEFINE FILE CAR
MODEL_PARAM/A200V = &&MODEL.QUOTEDSTRING;
END
TABLE FILE CAR
PRINT
     CAR.BODY.DEALER_COST
     CAR.BODY.RETAIL_COST
     CAR.BODY.SALES
	 MODEL_PARAM
BY  LOWEST CAR.CARREC.MODEL
WHERE CAR.CARREC.MODEL EQ &MODEL.(OR(FIND CAR.CARREC.MODEL IN CAR)).MODEL:.;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS FOCCACHE/CARMODELS FORMAT FOCUS
END

-*New CAR_TWO.FEX

TABLE FILE CARMODELS
PRINT DEALER_COST RETAIL_COST SALES
BY MODEL
HEADING
"&&MODEL"
END

-*CAR_THREE.FEX

TABLE FILE CARMODELS
PRINT MODEL_PARAM
WHERE RECORDLIMIT EQ 1
ON TABLE HOLD AS DEPTS FORMAT ALPHA
END
-RUN
-READFILE DEPTS

TABLE FILE CARMODELS
PRINT DEALER_COST RETAIL_COST SALES
BY MODEL
HEADING
"&MODEL_PARAM"
END

-* CAR_FOUR.FEX

TABLE FILE CARMODELS
PRINT DEALER_COST RETAIL_COST SALES
BY MODEL
HEADING
"<MODEL_PARAM"
END

Hey Stu...it worked....with the car file. For some reason I get an error with my table though...the thing is taunting me!

When I run the car fex I indeed get a hold file with user selection criteria store in a variable.

I get an error on my file. Since I have no experience with the double &, any tips on where I may read up on it...or tips in general are appreciated. Here is the code. Like I said, if uncomment out the car portion, and comment out the rest, it creates a hold file with the user selected models as a variable. If I comment the car code out and run my code, I get this error:



0 ERROR AT OR NEAR LINE 22 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC295) A VALUE IS MISSING FOR: &&IDXSPECIALTY


-* CAR EXAMPLE
-*DEFINE FILE CAR
-*MODEL_PARAM/A200V = &&MODEL.QUOTEDSTRING;
-*END
-*TABLE FILE CAR
-*PRINT
-* CAR.BODY.DEALER_COST
-* CAR.BODY.RETAIL_COST
-* CAR.BODY.SALES
-* MODEL_PARAM
-*BY LOWEST CAR.CARREC.MODEL
-*WHERE CAR.CARREC.MODEL EQ &MODEL.(OR(FIND CAR.CARREC.MODEL IN CAR)).MODEL:.;
-*ON TABLE SET PAGE-NUM NOLEAD
-*ON TABLE NOTOTAL
-*ON TABLE HOLD AS FOCCACHE/CARMODELS FORMAT FOCUS
-*END

-* ACTUAL DATA SAMPLE
DEFINE FILE TBLMSTR_ACTUALBUDGETUNITWRVUCOMBINEDDETAIL
SPECIALTY_PARAM/A200V = &&IDXSPECIALTY.QUOTEDSTRING;
END
TABLE FILE TBLMSTR_ACTUALBUDGETUNITWRVUCOMBINEDDETAIL
PRINT
TBLMSTR_ACTUALBUDGETUNITWRVUCOMBINEDDETAIL.TBLMSTR_ACTUALBUDGETUNITWRVUCOMBINEDDETAIL.IDXSPECIALTY
SPECIALTY_PARAM
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS FOCCACHE/TESTDATARF FORMAT FOCUS
END


Thanks again...so close!

Where did you -SET &&IDXSPECIALTY? That should be in the fex where you drilled down from.

The error says the variable has no value, so either you followed a route to this fex where it didn't get set, or it got lost somewhere (that happens here, haven't figured out yet why).

Another possible cause of your problem:
Allegedly, a -EXIT can cause the loss of your &&-variables. Apparently, that's somehow not a bug, but intended behaviour. At least, that's what IBI support claims.

So, instead of using -EXIT, we're supposed to use -GOTO and a label at the end of the procedure to exit a procedure gracefully.

quote:

-*New CAR_ONE.FEX - NOTE: you don't want to both &&PARAMETER and DEFINE. Choose one

The code present in my latest post is currently the code run. I figured I's simplify it and get it working at it's simple level first. The Car Code that works (commented out), to my eye, looks just like my production code....the one that errors out.

To your questions:
I did not SET &&IDXSPECIALTY. Per STU:
-*New CAR_ONE.FEX - NOTE: you don't want to both &&PARAMETER and DEFINE. Choose one

I do not see him setting the &&Model and the car code works. Again, the code I am running is commented out above. To be honest, I do not know exacly how the &&MODEL variable is egtting its value. I assume from the regular &Model in the WHERE clause...?

I don't have a EXIT or GOTO in the latest code but I will keep that in mind since at some point I may.

Thanks all.

RobertF,
It's been awhile since I used the following notation but I'm pretty sure this is why &&MODEL works and &&IDXSPECIALTY does not.

WHERE CAR.CARREC.MODEL EQ &MODEL.(OR(FIND CAR.CARREC.MODEL IN CAR)).MODEL:.;

The 'OR' condition will find MODEL in the file CAR if it does not equal &MODEL.

You have no 'OR' condition in your production code.

...lost my WHERE clause I gess...still does not seem to work...same error...

DEFINE FILE TBLMSTR_ACTUALBUDGETUNITWRVUCOMBINEDDETAIL
SPECIALTY_PARAM/A200V = &&IDXSPECIALTY.QUOTEDSTRING;
END
TABLE FILE TBLMSTR_ACTUALBUDGETUNITWRVUCOMBINEDDETAIL
PRINT
TBLMSTR_ACTUALBUDGETUNITWRVUCOMBINEDDETAIL.TBLMSTR_ACTUALBUDGETUNITWRVUCOMBINEDDETAIL.IDXSPECIALTY
SPECIALTY_PARAM
BY LOWEST TBLMSTR_ACTUALBUDGETUNITWRVUCOMBINEDDETAIL.TBLMSTR_ACTUALBUDGETUNITWRVUCOMBINEDDETAIL.IDXSPECIALTY
WHERE TBLMSTR_ACTUALBUDGETUNITWRVUCOMBINEDDETAIL.TBLMSTR_ACTUALBUDGETUNITWRVUCOMBINEDDETAIL.IDXSPECIALTY EQ &IDXSPECIALTY.(OR(FIND TBLMSTR_ACTUALBUDGETUNITWRVUCOMBINEDDETAIL.TBLMSTR_ACTUALBUDGETUNITWRVUCOMBINEDDETAIL.IDXSPECIALTY,TBLMSTR_ACTUALBUDGETUNITWRVUCOMBINEDDETAIL.TBLMSTR_ACTUALBUDGETUNITWRVUCOMBINEDDETAIL.IDXSPECIALTY IN TBLMSTR_ACTUALBUDGETUNITWRVUCOMBINEDDETAIL)).select specialty.;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS FOCCACHE/TESTDATARF FORMAT FOCUS
END

On the very first line of this prgram, do -SET &IDXSPECIALTY={a valid value};
Make sure you have the ending semi-colon.
Then run the code.
Also, in the CAR example there is no '.select' at the end of the WHERE clause but you have one. This could make a difference but try the suggestion above first.

GOT IT! I must have misunderstood Stu. Thanks Wep and everyone. You do need the -SET statement. This code now runs:


-SET &&IDXSPECIALTY = IF &IDXSPECIALTY EQ _FOC_NULL THEN 'All' ELSE &IDXSPECIALTY.QUOTEDSTRING;
DEFINE FILE TBLMSTR_ACTUALBUDGETUNITWRVUCOMBINEDDETAIL
SPECIALTY_PARAM/A200V = &&IDXSPECIALTY.QUOTEDSTRING;
END
TABLE FILE TBLMSTR_ACTUALBUDGETUNITWRVUCOMBINEDDETAIL
BY LOWEST TBLMSTR_ACTUALBUDGETUNITWRVUCOMBINEDDETAIL.TBLMSTR_ACTUALBUDGETUNITWRVUCOMBINEDDETAIL.IDXSPECIALTY
BY LOWEST SPECIALTY_PARAM
WHERE TBLMSTR_ACTUALBUDGETUNITWRVUCOMBINEDDETAIL.TBLMSTR_ACTUALBUDGETUNITWRVUCOMBINEDDETAIL.IDXSPECIALTY EQ &IDXSPECIALTY.(OR(FIND TBLMSTR_ACTUALBUDGETUNITWRVUCOMBINEDDETAIL.TBLMSTR_ACTUALBUDGETUNITWRVUCOMBINEDDETAIL.IDXSPECIALTY,TBLMSTR_ACTUALBUDGETUNITWRVUCOMBINEDDETAIL.TBLMSTR_ACTUALBUDGETUNITWRVUCOMBINEDDETAIL.IDXSPECIALTY IN TBLMSTR_ACTUALBUDGETUNITWRVUCOMBINEDDETAIL)).select specialty.;

ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS FOCCACHE/TESTDATARF FORMAT FOCUS
END


Of course I now have to incorporate into my real production code and then hope it runs via my html page but its looking better. Thanks again.