(A)
ID	AMTOUNT1	AMOUNT2
Toyota	2	10
Mazda	3	5
Ford	2	10
Toyota	5	20
GM	6	1
BMW	1	5
Toyota	5	6
GM	2	1
			
(B)
ID	  AMTOUNT1	AMOUNT2
Toyota	  12	         36
All Brands 26	         58

I intend to have results as in (B) above from (A) and then generate a graph. The intendtion is to compare one brand against all brands. Please, any idea on how results in (B) above can be gotten.

Thanks.

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

Create a virtual column:

DEFINE FILE file-name
ID1/A10 = IF ID EQ 'Toyota' THEN ID ELSE 'All Brands';

SUM by this virtual column.

The intention is to not rstrict it to only 'Toyota'. It can be 'GM' compared to All Brands, BMW compared to All etc.

I guess I need a variable to handle ID (e.g &id) so that the user can select any one brand with a view to comparing it with All Brands.

Any example code will be appreciated.

Umum, Having already given a similar answer already today on another post, McGyver can provide the solution. Check out McGyver Technique here on Focal Point and in "Tips and Techniques". Here's the sample code using the Car database in one pass and no HOLD files. As you've already said, you'll need to add a variable in place of the hard coded 'ENGLAND':

-* Set up McGyver master and data file.
-*
-* Build McGyver MFD
FILEDEF MCMAS DISK MCGYV.MAS
-RUN
-WRITE MCMAS FILENAME=mcgyv, SUFFIX=FIX     , $
-WRITE MCMAS   SEGMENT=ONE, SEGTYPE=S0, $
-WRITE MCMAS     FIELDNAME=CONTROL, USAGE=A1, ACTUAL=A1, $
-WRITE MCMAS   SEGMENT=TWO, SEGTYPE=S0, PARENT=ONE, OCCURS=VARIABLE, $
-WRITE MCMAS     FIELDNAME=CHAR1, USAGE=A1, ACTUAL=A1, $
-WRITE MCMAS     FIELDNAME=CTR, ALIAS=ORDER, USAGE=I4, ACTUAL=I4, $
-*
-* Build McGyver FTM
FILEDEF MCGYV DISK MCGYV.FTM
-RUN
-* Control=X: used as Join cross reference target field
-* CHAR1=A, CTR=1: display detail
-* CHAR1=B, CTR=2: display dynamic subtotals/subfoots
-WRITE MCGYV XAB

-**************************************************************************************
-RUN
JOIN CONTROL WITH BODYTYPE IN CAR TO UNIQUE CONTROL IN MCGYV AS J2
-* CTR=1 display detail, CTR=2 display subtotals/subfoots.
DEFINE FILE CAR
CONTROL      /A1 WITH BODYTYPE='X';
SUM_COUNTRY  /A10=IF CTR EQ 1 THEN COUNTRY ELSE 'TOTAL'; 
END
-*******
SET DROPBLNKLINE=ON
TABLE FILE CAR
SUM SALES/P12.2CM
    DEALER_COST/P12.2CM 
BY CTR     NOPRINT 
BY SUM_COUNTRY AS COUNTRY 
WHERE (CTR EQ 1 AND COUNTRY EQ 'ENGLAND') OR
      (CTR EQ 2);
ON TABLE NOTOTAL
ON TABLE SET PAGE NOLEAD
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
FONT='ARIAL', SIZE=10,GRID=OFF,$
ENDSTYLE
END

Since it's based on a user-selected variable:

-DEFAULT &ID = 'Bricklin';

DEFINE FILE file-name ID1/A10 = IF ID EQ '&ID' THEN ID ELSE 'All Brands';

And how's this for a non-McGyver solution?

SET ASNAMES = ON 

TABLE FILE CAR
	SUM SALES
		DEALER_COST
	BY TOTAL COMPUTE CAR/A16 = 'OTHERS';
ON TABLE HOLD AS H01
END

TABLE FILE CAR
	SUM SALES AS 'AMOUNT1'
		DEALER_COST AS 'AMOUNT2'
	BY CAR AS 'GROUPING';
WHERE CAR EQ '&USERSELECTION'
ON TABLE HOLD AS H02
MORE
FILE H01
END

TABLE FILE H02
	SUM AMOUNT1 AMOUNT2
	BY GROUPING
ON TABLE PCHOLD FORMAT HTML
END

Greets,Dave
( There is always 'more'.... )

Dave, your code looks great. I just did a simple test and it seems to be ok. I will now apply the same principle to my code and will let you know if it works or not.

Thanks

quote:

Originally posted by Dave:
And how's this for a non-McGyver solution?

SET ASNAMES = ON 

TABLE FILE CAR
	SUM SALES
		DEALER_COST
	BY TOTAL COMPUTE CAR/A16 = 'OTHERS';
ON TABLE HOLD AS H01
END

TABLE FILE CAR
	SUM SALES AS 'AMOUNT1'
		DEALER_COST AS 'AMOUNT2'
	BY CAR AS 'GROUPING';
WHERE CAR EQ '&USERSELECTION'
ON TABLE HOLD AS H02
MORE
FILE H01
END

TABLE FILE H02
	SUM AMOUNT1 AMOUNT2
	BY GROUPING
ON TABLE PCHOLD FORMAT HTML
END

Greets,Dave
( There is always 'more'.... )

One more thing:
If I want to pass the value of this variable (&USERSELECTION) to another TABLE FILE .....END block in the same procedure, please how can it be done.
Thanks.

Since &userselection is unchanged in the in the FEX, it can be used as such anywhere in the FEX.
If it were changed, you would have to be cognizant of the change and write your FEX accordingly but in either case, the amper variable is still alive and available for use in the FEX.
HTH
Here's the text from the manual:

quote:

A local variable retains its values during the execution of one procedure. Values are lost
after the procedure finishes processing. Values are not passed to other procedures that
contain the same variable name.
A local variable is identified by a single ampersand followed by the variable name.
A global variable retains its value for the duration of your connection to the WebFOCUS
Reporting Server and are passed from the execution of one procedure to the next.
Because WebFOCUS creates a new WebFOCUS session on the WebFOCUS Reporting
Server each time it submits a request, values for global variables are not retained between
report requests. This means that you can use the same global variable in more than one
procedure as long as these procedures are called in the same request.
If you want a global value of the variable to be in effect for every procedure, add the
variable to a WebFOCUS Reporting Server profile, such as EDASPROF.PRF.

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

quote:

Originally posted by jimster06:
Since &userselection is unchanged in the in the FEX, it can be used as such anywhere in the FEX.
If it were changed, you would have to be cognizant of the change and write your FEX accordingly but in either case, the amper variable is still alive and available for use in the FEX.
HTH
Here's the text from the manual:

quote:

A local variable retains its values during the execution of one procedure. Values are lost
after the procedure finishes processing. Values are not passed to other procedures that
contain the same variable name.
A local variable is identified by a single ampersand followed by the variable name.
A global variable retains its value for the duration of your connection to the WebFOCUS
Reporting Server and are passed from the execution of one procedure to the next.
Because WebFOCUS creates a new WebFOCUS session on the WebFOCUS Reporting
Server each time it submits a request, values for global variables are not retained between
report requests. This means that you can use the same global variable in more than one
procedure as long as these procedures are called in the same request.
If you want a global value of the variable to be in effect for every procedure, add the
variable to a WebFOCUS Reporting Server profile, such as EDASPROF.PRF.

If I have a WHERE clause as:

WHERE NAME EQ '&NAME1.(FIND NAME1,CAR.NAME1 IN CAR).NAME1.';
WHERE AMOUNT EQ '&AMOUNT1.(FIND AMOUNT1,CAR.AMOUNT1 IN CAR).AMOUNT1.';

How will I store the values of the variables &NAME1 and &AMOUNT1 so that I can use them in a sub procedure within the same procedure.

Yes.

Once set the parameters keeps it's value until the whole procedure is done.
( including includes )
( doesn't work for EX or EXEC... )

This is basic understanding of WebFocus. ( you should know this )

quote:

Originally posted by Dave:
Yes.

Once set the parameters keeps it's value until the whole procedure is done.
( including includes )
( doesn't work for EX or EXEC... )

This is basic understanding of WebFocus. ( you should know this )

My question was how to capture the value of a dynamic variable (in a where clause) so that this same value can be used in another(sub) procedure's where clause.

I think I got it using '&NAME1.EVAL';
'&NAME1.EVAL'
'&AMOUNT1.EVAL'

quote:

BY TOTAL COMPUTE CAR/A16 = 'OTHERS';

Wow, I didn't know you could do this.

umun:

You do not need to 'capture' it. Just use it, it'll work.



Waz:

Unless ... umun actually means drilldown.
If that's the case, then I would advise to read up on the drilldown section of the manual(s) or do a search for drilldown on this forum. You'll find loads of good stuff.

quote:

Originally posted by Dave:
umun:

You do not need to 'capture' it. Just use it, it'll work.



Waz:

how will I use it?

okay...

again... just use it.

WHERE NAME EQ '&NAME1.(FIND NAME1,CAR.NAME1 IN CAR).NAME1.';
WHERE AMOUNT EQ '&AMOUNT1.(FIND AMOUNT1,CAR.AMOUNT1 IN CAR).AMOUNT1.';

Still will fill the paramaters:
&NAME1 and &AMOUNT1 with whatever the user selected.

You use them again:
WHERE NAME EQ &NAME

Just use &NAME1 and &AMOUNT1 in your WHERE clause or anywhere else in the fex. If they should be used in another sub-fex, until it's from the same execution (a main fex including another one and so on) the variable's value will still exist and usable.

Here is a sample of what I have got. It seems to be working but needs minor(flow control) changes and confirmation:

 
SET ASNAMES = ON
SET DMPRECISION = 2

TABLE FILE TEST
SUM
     TEST.CATEGORY.AMOUNT
BY  TEST.CATEGORY.STATE
BY  TEST.CATEGORY.NAME AS 'GNAME'
WHERE TEST.CATEGORY.NAME EQ '&NAME.(FIND NAME,TEST.CATEGORY.NAME IN TEST).NAME.';
WHERE TEST.CATEGORY.SIZE EQ '&SIZE.(FIND SIZE,TEST.CATEGORY.SIZE IN TEST).SIZE.';
WHERE TEST.CATEGORY.LOCATION EQ '&LOCATION.(FIND LOCATION,TEST.CATEGORY.LOCATION IN TEST).LOCATION.';
WHERE TEST.CATEGORY.COLOUR EQ 'Red';
ON TABLE SET BYDISPLAY ON
ON TABLE HOLD AS HO1
END
-RUN

-*YREC
DEFINE FILE TEST
GNAME/A50 = 'ALL NAMES';
END

TABLE FILE TEST
SUM
     TEST.CATEGORY.AMOUNT
BY  TEST.CATEGORY.STATE
BY  GNAME
WHERE TEST.CATEGORY.SIZE EQ '&SIZE.EVAL';
WHERE TEST.CATEGORY.LOCATION EQ '&LOCATION.EVAL';
WHERE TEST.CATEGORY.COLOUR EQ 'Red';
ON TABLE SET BYDISPLAY ON
ON TABLE HOLD AS HO2
END

TABLE FILE HO1
PRINT *
ON TABLE HOLD AS HO3
MORE
FILE HO2
END

-INCLUDE app/test_graph.fex
 

Questions:
1. Is the second procedure the correct/best way of re-using dynamic variables (&Size and &Location) values as selected in procedure 1.
2. If procedure 1 returns no value (ie. &Name varaible value not found), i don't want the second procedure and -INCLUDE app/test_graph.fex to run. Instead, I want no data message to be displayed instead.

Any suggestions will be appreciated

Not a fancy error message but it gives you the idea.


SET ASNAMES = ON
SET DMPRECISION = 2

TABLE FILE TEST
SUM
TEST.CATEGORY.AMOUNT
BY TEST.CATEGORY.STATE
BY TEST.CATEGORY.NAME AS 'GNAME'
WHERE TEST.CATEGORY.NAME EQ '&NAME.(FIND NAME,TEST.CATEGORY.NAME IN TEST).NAME.';
WHERE TEST.CATEGORY.SIZE EQ '&SIZE.(FIND SIZE,TEST.CATEGORY.SIZE IN TEST).SIZE.';
WHERE TEST.CATEGORY.LOCATION EQ '&LOCATION.(FIND LOCATION,TEST.CATEGORY.LOCATION IN TEST).LOCATION.';
WHERE TEST.CATEGORY.COLOUR EQ 'Red';
ON TABLE SET BYDISPLAY ON
ON TABLE HOLD AS HO1
END
-RUN

-IF &LINES EQ 0 THEN GOTO SHOWNODATA;

-*YREC
DEFINE FILE TEST
GNAME/A50 = 'ALL NAMES';
END

TABLE FILE TEST
SUM
TEST.CATEGORY.AMOUNT
BY TEST.CATEGORY.STATE
BY GNAME
WHERE TEST.CATEGORY.SIZE EQ '&SIZE.EVAL';
WHERE TEST.CATEGORY.LOCATION EQ '&LOCATION.EVAL';
WHERE TEST.CATEGORY.COLOUR EQ 'Red';
ON TABLE SET BYDISPLAY ON
ON TABLE HOLD AS HO2
END

TABLE FILE HO1
PRINT *
ON TABLE HOLD AS HO3
MORE
FILE HO2
END

-INCLUDE app/test_graph.fex

-GOTO XEND;

-SHOWNODATA

< !-- Generated by Report Layout Painter -->
<HTML>
<HEAD>
<script id=IBI_RelCallBack type=text/javascript>function AdjustChildrenPosition(){
}
</SCRIPT>
 
<script id=IBI_OptionsScript type=text/javascript>
var rltVersion = "764";
var cgipath = "cgipath";
var ibirls = "ibirls2";
 
var rltdyncalendar = "rltdyncalendar";
var gmap = "ibigmap";
var olap="olap";
var olappanebase="olappanebase";
var olapdrill="olapdrill";
 
var ibixmltree="ibixmltree";
 
var ibiOptions = new Array(cgipath,ibirls);
</SCRIPT>
 
<script id=IBI_nls src="/ibi_html/javaassist/nls.js" type=text/javascript></SCRIPT>
 
<script id=IBI_ibigbl src="/ibi_html/javaassist/ibi/html/js/ibigbl.js" type=text/javascript></SCRIPT>
 
<script id=IBI_ibigblloadCss type=text/javascript>
ibigblloadCss(null);</SCRIPT>
<TITLE>HtmlPage</TITLE>
<script id=clientEventHandlersJS type=text/javascript>
//Begin function window_onload
function window_onload() {
 
UpdateData();
 
// TODO: Add your event handler code here
//add onInitialUpdate() function to make changes before initial run of the reports
}
//End function window_onload
</SCRIPT>
 
<script for=window eventname="onload">window.onload = function() { window_onload(); }</SCRIPT>
</HEAD>
<BODY style="OVERFLOW: auto" edaconnectionrequired="false">
<SPAN class=FormLabel id=text1 style="Z-INDEX: 1; LEFT: 60px; WIDTH: 850px; FONT-FAMILY: 'Arial Narrow'; POSITION: absolute; TOP: 160px; HEIGHT: 260px" tabIndex=1>
<DIV>No Data available</DIV>
<DIV> </DIV></SPAN>
<INPUT id=layoutinfo style="LEFT: -100px; POSITION: absolute; TOP: -100px" type=hidden>
<INPUT id=ibiapp_app style="LEFT: -100px; POSITION: absolute; TOP: -100px" type=hidden value=usual_modules name=ibiapp_app></BODY>
<script id=IBI_loader type=text/javascript>
doBeforeLoad();
</SCRIPT>
</HTML>

-XEND

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

MARTIN, PLEASE PUT YOUR CODE BETWEEN

[CODE]

[/CODE]

TAGS.

THANK YOU.

FRANCIS.

The flow control is working now.

But is there no difference between using '&SIZE.EVAL' and just '&SIZE' in the second procedure?

Thank you.

People have too many "EVAL" cards in their back pockets.

"The .EVAL operator enables you to evaluate a variable's value immediately, making it possible to change a procedure dynamically. The .EVAL operator is particularly useful in modifying code at run time."

This is not the case in a WHERE statement, therefore .EVAL is not required.

Ditto Francis.

It seems that many thing that an Amper variable won't be evaluated in FOCUS code.

The only places it should be used in in Dialog Manager

One example would be:

-DEFAULTS &MYVAL = '&TESTVAL.EVAL' ;

Thanks for the clarification

Warren--

Not entirely true. Adding .EVAL in a line of FOCUS code may also affect the net result.

In the process of scanning the fex line and copying it into FOCSTACK, when the input line contains &var or &var.EVAL ...

* Without .EVAL, the value of &var is inserted into the focstack line, in place of "&var", and then the scan continues on the input line beyond the end of the name.

* With .EVAL, the reference is relaced by its value, in the input line of code, and the scan rewinds to the the point where the name was encountered -- i.e., the beginning of the replacement value -- and continues from there.

When the substituted value contains an ampersand, the presence of .EVAL causes it to be placed in the input fex line, triggering another evaluation and replacement; whereas without .EVAL the ampersand will go into FOCSTACK, where it will be treated as just another character.


For example,

-DEFAULT &att='AT&T'
TABLE ...

HEADING
" ... &att ..."
" ... &att.EVAL ..."

The first heading line works correctly, producing " ... AT&T ..."
The second one gets changed to
" ... AT&T ..."
which fails with

(FOC295) A VALUE IS MISSING FOR: &T

if &T has not been declared.

Interesting....

As usual, there are always exceptions to the general rule.

Thanks for the info.