[SOLVED] Multiselect does not work on the drilldown-report

Hello, Marcel.

First, can you update your Profile Signature with your products, releases, and platforms please? That makes it easier to help you.

Secondly, I don't understand the part about doing a multi-select from a parent to a chile report. Please explain further what you are trying to do.

And third, could you provide a simpler example of your error using one of the demo files?

Thanks.

Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google

Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006

Ignored post by GinnyJakes posted

<JG>

posted

quote:
-DEFAULT &AFDELING = '''A1'' OR ''A2'' OR ''A3''';

is then passed as

quote:
AFDELING=&AFDELING.QUOTEDSTRING

This doubles the quotes making the string invalid

Francis Mariani

Expert

posted

Removing QUOTEDSTRING makes sense to me.

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Virtuoso

posted

quote:
TYPE=DATA,
COLUMN=N1,
DRILLMENUITEM='Afdelingen Totaal',
FOCEXEC=MustscorevragenlijstPeriodiekDTZHBAFD(LOCATIE=N1 ... AFDELING=&AFDELING.QUOTEDSTRING),
TARGET='_blank',
...
$

&AFDELING.QUOTEDSTRING looks right to me.

If this were a simple call
EX fex2 X=&X
where the value of &X includes a leading quote, .QUOTEDSTRING would be essential:
without it, the generated code is ...
EX fex2 X='A1' OR 'A2' OR 'A3'
... and the leading quote is taken to be a value-delimiter, rather than part of the value, and a FOC293 results:

 EX fex2 Y1='A1' OR 'A2' OR 'A3'
 -RUN
 0 ERROR AT OR NEAR LINE      8  IN PROCEDURE FEX1    FOCEXEC *
 (FOC293) MISSING OR MISPLACED QUOTE IN THE FOCEXEC ARGUMENT LINE:

I expect the same considerations apply to arguments in a drilldown. If so, the posted code was right, just IBI's handling is at fault.

The simplest work-around is to transform the quotes to something else (maybe ~ or æ or ") using CTRAN, for the drilldown call, and reverse the transformation on the receiving side. (This is akin to Susannah's advice in a post some weeks ago.)

- Jack Gross
WF through 8.1.05

Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005

Ignored post by j.gross posted

<JG>

posted

Jack,

If you use QUOTEDSTRING on a string that allready contains the required quotes then it screws up.

QUOTEDSTRING is designed to

1. Wrap the string in quotes
2. Convert ' to '' to allow WebFOCUS to read values such as it's corrcetly in DM.

If the string already has the correct quotes then it breaks.

This is not an EX this is a drilldown.

Virtuoso

posted

Beg to differ. The value of &AFDELING in the parent fex does not include the delimiting quotes, and does not have doubled quotes. (They are dropped in execution of the -DEFAULT, same as in -SET --- Marcel added the -DEFAULT to simulates a multi-value call to the parent fex from its launch-page, and the simulation is accurate.)

As the query noted, the parent fex, which receives the &var with the OR'ed list of quoted values, works correctly, but the child fex (when invoked by the drilldown declared in the parent) errors out.

I'll use var to represent &AFDELING. A -? & in the parent fex will show it has

&var: 'A1' OR 'A2' OR 'A3'

(otherwise the parent report would bomb) and the developer needs to arrange things so that the same value is propogated to &var in the child fex.

I pointed out that the drilldown declaration in the parent cannot be

... var=&var ...

(sans .QUOT), which expands to

... var='A1' OR 'A2' OR 'A3' ...

It needs to be (and was)

... var=&var.QUOTEDSTRING ...

which expands to

... var='''A1'' OR ''A2'' OR ''A3''' ...

That should in turn establish the variable as

&var: 'A1' OR 'A2' OR 'A3'

in the child
--- and it would if IBI's server left the string '''A1'' OR ''A2'' OR ''A3''' as-is, as the RHS of either "EX fexname var=RHS" or "-SET &var=RHS;.

But it doesn't, which is a bug in IBI's handling of the drilldown declared with

... var='''A1'' OR ''A2'' OR ''A3''' ...

--- apparently some link in the chain (probably the standard drilldown JavaScript in the parent report) transforms the value, stripping out the outermost pair of quotes, but leaving the doubled quotes as is.

This message has been edited. Last edited by: j.gross,

- Jack Gross
WF through 8.1.05

Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005

Ignored post by j.gross posted

<JG>

posted

I understand what you are saying Jack but quoted string is not meant to handle
'A' OR 'B' OR 'C', BUT having generated a simple example IT is the only way to pass it.

Solution is to replace the '' with ' in the called fex.

Calling fex
-DEFAULTH &VAR = '''ENGLAND'' OR ''FRANCE'' OR ''W GERMANY'''
TABLE FILE CAR
PRINT COUNTRY
ON TABLE SET STYLE *
TYPE=DATA,
COLUMN=N1,
DRILLMENUITEM='drill',
FOCEXEC=test02(COUNTRY=&VAR.QUOTEDSTRING),
TARGET='_blank',
$
END

Called fex
-SET &COUNTRYX = STRREP(&COUNTRY.LENGTH, &COUNTRY, 2, '''''''', 1, '''', &COUNTRY.LENGTH, 'A&COUNTRY.LENGTH') ;
TABLE FILE CAR
PRINT COUNTRY
WHERE COUNTRY EQ &COUNTRYX
END

Virtuoso

posted

JG -

Either workaround (ctran, twice; or strrep, once) should work now -- and continue to work unimpaired if and when IBI corrects the behavior.

- j.g.

Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005

Ignored post by j.gross posted

Hua

Guru

posted

quote:
just IBI's handling is at fault.

Yes, I believe everyone at one time or another stumbled on automprompt vs drill-down vars.

Thanks for the work around solutions.

Hua

Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS

Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008

Ignored post by Hua posted

Virtuoso

posted

Autoprompt performs properly. Simple drilldowns perform properly, handling quotes within the &var value gracefully. The bug is in the drill-menu feature.

JG's example

-DEFAULTH &VAR = '''ENGLAND'' OR ''FRANCE'' OR ''W GERMANY'''
TABLE FILE CAR
PRINT COUNTRY
ON TABLE SET STYLE *
TYPE=DATA,
COLUMN=N1,
DRILLMENUITEM='drill',
FOCEXEC=fex2(X=&VAR.QUOTEDSTRING),
TARGET='_blank',
$
END

generates an href like the following:

javascript:MDitemClick(0,'X=\'\'ENGLAND\'\'%20OR%20\'\'FRANCE\'\'%20OR%20\'\'W%20GERMANY\'\'','');

When you unescape arg[1] it boils down

X=''ENGLAND'' OR ''FRANCE'' OR ''W GERMANY''

Those doubled quotes seep into the child fex:

&X            = ''ENGLAND'' OR ''FRANCE'' OR ''W GERMANY''

and cause the WHERE clause in the child fex to fail on syntax.

The generated HREF needs to preserve all the quotes that appear in the stylesheet entry...

javascript:MDitemClick(0,'X=\'\'\'ENGLAND\'\'%20OR%20\'\'FRANCE\'\'%20OR%20\'\'W%20GERMANY\'\'\'','');
                            ^^                                                                ^^

to yield

X='''ENGLAND'' OR ''FRANCE'' OR ''W GERMANY'''

which would lead to

&X            = 'ENGLAND' OR 'FRANCE' OR 'W GERMANY'

and

WHERE COUNTRY EQ 'ENGLAND' OR 'FRANCE' OR 'W GERMANY'

as required.

- Jack Gross
WF through 8.1.05

Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005

Ignored post by j.gross posted

posted

Thanks all for your respons. We found the reason why it went wrong. The solutions you suggested are right, but in this case I agree with J. Gross: it fails when you pass multiple values of a variable IN COMBINATION with the use of a multiple drill down.

This is not the case when I strip the procedure or replace it by a single drill down. It seems that the multiple drill downn window starts a script which places quotes around the multiple values you try to pass. Of course I could strip the extra quotes, but an adjustment of this script would be very helpful in the next release :-)

For now we decided to work with the plain old good working single drill down. Thanks again all!

Marcel

Virtuoso

posted

Marcel:

I don't think the js functions per se are implicated; the library files are fine. It's the Agent software that generates javascript expressions with incorrect sequences of (escaped) quotes in the function arguments.

- Jack Gross
WF through 8.1.05

Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005

Ignored post by j.gross posted

Doug

Expert

posted

Thanks All. Another point for the FocalPointers!

I had a fex bomb when performing a drilldown from it with a multiselected variable. I forgot aout the ".QUOTEDSTRING". It bombed quite nicely without the it.

I posted the following code as a recursive drilldown to itself just for instructional purposes.

-* MultiSelectDrillDownTest.fex
-DEFAULT &OUTFORMAT = 'HTML' ;
-DEFAULT &COUNTRY   = FOC_NONE ;
-DEFAULT &CAR       = FOC_NONE ;
-DEFAULT &MODEL     = FOC_NONE ;
-DEFAULT &RANDOM    = '&TOD.EVAL' ;
-SET &ECHO = ALL ; 
TABLE FILE CAR
SUM  'CAR.BODY.DEALER_COST' AS 'DEALER'
     'CAR.BODY.RETAIL_COST' AS 'RETAIL'
BY 'CAR.ORIGIN.COUNTRY'
BY 'CAR.COMP.CAR'
BY 'CAR.CARREC.MODEL'
HEADING
-* Skip the Drilldown heading for PDF.
-IF &OUTFORMAT EQ 'PDF' THEN GOTO NODRILLDOWN1 ;
"Download to PDF"
-NODRILLDOWN1
"CARS, As of: &DATEMTDYY @ &TOD (&OUTFORMAT &RANDOM)"
"COUNTRY = &COUNTRY"
"CAR = &CAR"
"MODEL = &MODEL"
WHERE COUNTRY EQ &COUNTRY.(OR(FIND COUNTRY,COUNTRY IN CAR)).Enter a COUNTRY.;
WHERE CAR     EQ &CAR.(OR(FIND CAR,CAR IN CAR)).Enter a CAR.;
WHERE MODEL   EQ &MODEL.(OR(FIND MODEL,MODEL IN CAR)).Enter a MODEL.;
ON TABLE PCHOLD FORMAT &OUTFORMAT.EVAL
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET STYLE *
     UNITS=IN, LEFTMARGIN=0.5, RIGHTMARGIN=0.5, TOPMARGIN=0.5, BOTTOMMARGIN=0.5, SQUEEZE=ON, ORIENTATION=PORTRAIT,$
TYPE=REPORT, GRID=OFF, FONT='ARIAL', SIZE=9, COLOR='NAVY', STYLE=NORMAL,$
TYPE=DATA, BACKCOLOR=( RGB(202 202 230) 'WHITE' ),$
TYPE=TITLE, STYLE=BOLD,$
-* Skip the Drilldown, we've already done that.
-IF &OUTFORMAT EQ 'PDF' THEN GOTO NODRILLDOWN2 ;
TYPE=HEADING, LINE=1, 
     FOCEXEC=app/MultiSelectDrillDownTest.fex( \
     OUTFORMAT='PDF' \
     COUNTRY= &COUNTRY.QUOTEDSTRING \
     CAR= &CAR.QUOTEDSTRING \
     MODEL= &MODEL.QUOTEDSTRING \
     RANDOM= '&TOD' ),$
-NODRILLDOWN2
ENDSTYLE
END

Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005

Ignored post by Doug posted