I am building a launch page that will allow the user to manually select which "BY" columns will appear on the report. the procedure is pretty basic with 3 report variables (1 with all the available measures, 1 with time periods to go ACROSS, and 1 all the possible dimensions) I have defined the later one as multi-select. My launch page has 2 dropboxes for the 1st 2 report varaibles and 1 listbox for the column selections. This is working correctly if I only select a single dimension from the listbox but if I seelct multiple dimensions, I get an error pointing to the 2nd selected item. I am sure this is because the procedure only has one instance of the report variable for the dimensions and therefore it's probably trying to add both columns to the same position in the report. Is there a way to have additional selections get added as dditional columns dynamically? My preference would be to have the user select the fields they want via checkboxes which I have also tried but with similar results. Just wondering if anyone else has had any luck doing something similar?

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

Try this in the FEX ...
BY &SORTS.( BY (field1, field2, field3)).

The join word of BY should connect all of the selections.

Very cool. It seems to be only single select.
Is there syntax for multi-select?
Where is the syntax "BY(field1,field2,field3)" documented?
Is there a way to get this to work with Auto-Prompting?

  
-SET &ECHO=ALL;

TABLE FILE CAR
SUM
     DEALER_COST
     RETAIL_COST
BY &SORT.(BY(COUNTRY,CAR,MODEL)).
END

This seems to work well for a single select but I want to be able to select multiple columns. If I try selecting more then one value from the list I get the following error message.

TABLE FILE CAR
SUM
'CAR.BODY.DEALER_COST'
'CAR.BODY.RETAIL_COST'
BY 'COUNTRY' OR 'CAR'
ON TABLE NOTOTAL
END
0 ERROR AT OR NEAR LINE 9 IN PROCEDURE TEST FOCEXEC *
(FOC002) A WORD IS NOT RECOGNIZED: OR
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT

You have the incorrect syntax no OR in a BY statement and no tick marks ' ' around a field.

My code is identical to what Mighty Max posted earlier and contains no ' ' around fields or any OR statement. These are only showing up in the error message when running the procedure with 2 of values are selected in a listbox.

-SET &ECHO=ALL;

TABLE FILE CAR
SUM
DEALER_COST
RETAIL_COST
BY &SORT.(BY(COUNTRY,CAR,MODEL)).
END

I didn't get any errors. Only issue is that multiselect does not work.
How did you build your launch page?

Open HTML Composer.
Draw a listbox. Name the listbox SORT.
Click the parameters tab. Create three static values COUNTRY,CAR,MODEL
Draw a button.
Right click the button and Create Hyperlink
Action -> External Procedure
Source -> The saved fex
Target Type -> Window
Target -> New Window

The code that is generated when you do a multiselect is

"'value1' OR 'value2' OR 'value3' ..."

WF ASSUMES that you are using this in a selection criteria (because you are multiselecting), so you will not be able to do a multiselect for a BY phrase using this syntax.

In order to select multiple fields for inclusiion in your report, you'll probably need to use an indexed &variable and create a dialogue manager loop to get all the values for the field names. If you use the search function, there are many examples of this on the forum.

Here is a solution that is working.

  
-SET &ECHO=ALL;

-SET &TMP_SORT = &SORT.QUOTEDSTRING;
-SET &SORT = STRREP(&TMP_SORT.LENGTH, &TMP_SORT, 2, 'OR', 2, 'BY', 250, &SORT);

TABLE FILE CAR
SUM
   DEALER_COST
   RETAIL_COST
BY &SORT.(OR(COUNTRY,CAR,MODEL)).;
END

Basically I am replacing all ORs with 'BY's.
Dropped the BY(COUNTRY,CAR,MODEL)) syntax and used OR(COUNTRY,CAR,MODEL)).
Multiselect OR returns a quoted string so QUOTEDSTRING is needed to handle this.
STRREP is used to replace the ORs with BYs

Echo

  
-SET &TMP_SORT = '''COUNTRY'' OR ''CAR'' OR ''MODEL''';
-SET &SORT = STRREP(29, 'COUNTRY' OR 'CAR' OR 'MODEL', 2, 'OR', 2, 'BY', 250, 'COUNTRY' OR 'CAR' OR 'MODEL');
TABLE FILE CAR
SUM
DEALER_COST
RETAIL_COST
BY 'COUNTRY' BY 'CAR' BY 'MODEL';
END
0 NUMBER OF RECORDS IN TABLE=       18  LINES=     18

Mighty Max,

You are definitely on the right track. I've used the same STRREP method and it works well.

Here is something else you can do. Instead of populating your field list (on the html page)with static values you can populate it with a procedure...

CHECK FILE CAR HOLD AS CARHOLD
TABLE FILE CARHOLD
SUM FST.FIELDNAME
BY FIELDNAME
ON TABLE PCHOLD FORMAT XML
END

Just be sure to select FIELDNAME for the Data Field. You could also do...

SUM FST.FIELDNAME
BY TITLE

Then you can use TITLE for the Display Field and FIELDNAME for the Data Field.

Cheers,

Dan

And then there is still this funny little command LET.
Taking Mighty Max's code and modifying it slightly:

-SET &ECHO=ALL;

LET OR=BY

TABLE FILE CAR
SUM
   DEALER_COST
   RETAIL_COST
BY &SORT.(OR(COUNTRY,CAR,MODEL)).;
END

This works like a charm for me.
There is one tiny little caveat with this - which is when you also have a where statement that contains an 'or'...

Thanks for all the suggestions. This is working great. One final question. The formatting for my first &sort column is appearing as I have defined in the report painter (Arial 10) but if I add additional columns via my list box they are all taking on what appears to be the default styling (Times New Roman, 10). Is there a way to ensure each additional column being added maintains consistent styling? Again...thanks for all the assistance.

Bob

Regarding MM's code:

1. In real life, "OR" might appear within some of the option values; it's safer to expand the From and To text to include the surrounding blanks:

STRREP(..., 4,' OR ', 4,' BY ', ...)

2. You should be able to directly assign
-SET &TMP_SORT=&SORT;
without resorting to .QUOTEDLITERAL; if the right hand expression in a -SET is simply an amper variable, no evaluation takes place and the current value of the RHS variable (regardless of the nature of that content) gets cloned as the new value of the LHS variable. -- But there's really no need for a temp variable here; you can transform &SORT directcly.

3. The ";" appearing in

BY &SORT.(OR(COUNTRY,CAR,MODEL)).;

doesn't belong -- it's not part of the "implied prompt" syntax, so it becomes part of the resolved TABLE code, as is evident in the echoed TABLE code.
-- Why that is accepted by TABLE beats me, but it should be ditched.

-SET &ECHO=ON;
-DEFAULT &SORT='''COUNTRY'' OR ''CAR'' OR ''MODEL''';

* &SORT.(OR(COUNTRY,CAR,MODEL)).
-SET &LEN=&SORT.LENGTH;
-SET &SORT = STRREP(&LEN,&SORT, 4,' OR ', 4,' BY ', &LEN,'A&LEN.EVAL');

TABLE FILE CAR
SUM
   DEALER_COST
   RETAIL_COST
BY &SORT
END

Note: The "* &SORT ..." line at the top is a Focus comment line, to move prompting for &SORT to the top before referencing &SORT.LENGTH. In legacy interactive Focus, the prompting takes place as dialog manager scans the focexec, and &SORT cannot be referenced before it is assigned a value. In the brave new WebFocus world the order does not matter -- there is a separate pre-scan to detect and perform prompts, and then the fex is re-executed with the prompted-for values pre-SET -- but I think it's clearer to the hiuman reader when the prompt appears at the top.