I created a dashboard with filters that can be either single or multiple selections. Because the filters are not being passed to the dashboard parameters with quotes, I am using the following -SET statements that uses the STRREP function for multiple selection. I have multiple turned on in the dashboard and selected Yes to add quotes in Properties. Pasted below is what's happening when I choose wither a single selection or multiple selection. How can I get the single and multiple selections to be enclosed with single quotes. Thanks.

Testing done in the FEX 
-DEFAULTH &DASH_QUARTER_NAME = '''Q4 2017'' OR ''Q3 2017'''; (when choosing multiple selections)
-DEFAULTH &DASH_QUARTER_NAME = 'Q4 2017'; (when choosing a single selection)
-SET &DASH_QUARTER_NM = STRREP(&DASH_QUARTER_NM.LENGTH, &DASH_QUARTER_NM, 4, ' OR ', 6, ''' OR ''', 2000, 'A2000');
-SET &DASH_QUARTER_NM = IF &DASH_QUARTER_NM EQ '_FOC_NULL' THEN '_FOC_NULL' ELSE '''' | TRUNCATE(&DASH_QUARTER_NM) | '''';


-* WHERE CLAUSE
WHERE QUARTER_NAME EQ &DASH_QUARTER_NAME
   OR DASH_QUARTER_NAME EQ &DASH_QUARTER_NM.(OR(FIND DASH_QUARTER_NAME IN DW_MRSDASH_CLAIMS)).DASH_QUARTER_NM.;

-* Results of how it's being passed when I choose multiple quarters
WHERE DASH_QUARTER_NAME EQ 'Q4 2017' OR 'Q3 2017'
OR DASH_QUARTER_NAME EQ ''Q4 2017' OR 'Q3 2017''; (double quotes instead of single quotes)

-* Results of how it's being passed when I choose a single quarter
WHERE DASH_QUARTER_NAME EQ Q4 2017  (quotes missing)
 OR DASH_QUARTER_NAME EQ 'Q4 2017';

 

This message has been edited. Last edited by: Michele Brooks,

Hi,

If you have created an HTML page and then passing the values from HTML control try below steps,
1. Remove the quote property values from controls make as not set.
2, add QUOTESDTRING with your param in report.
i. e. WHERE QUARTER_NAME EQ &DASH_QUARTER_NAME. QUOTEDSTRING.

hope it helps.

Right-Click on your variable and see if it's set to multiple.

quote:

Originally posted by Chaudhary:
Hi,

If you have created an HTML page and then passing the values from HTML control try below steps,
1. Remove the quote property values from controls make as not set.
2, add QUOTESDTRING with your param in report.
i. e. WHERE QUARTER_NAME EQ &DASH_QUARTER_NAME. QUOTEDSTRING.

hope it helps.

Will try. Thanks.

quote:

Originally posted by Don Garland:
Right-Click on your variable and see if it's set to multiple.

Don, all variables are set for Multiple OR. Thanks for the reply.

Chaudhary, is this what you mean? If so, I'm getting an error message.

WHERE DASH_QUARTER_NAME EQ &DASH_QUARTER_NM.QUOTEDSTRING
OR DASH_QUARTER_NAME EQ &DASH_QUARTER_NM.(OR(FIND DASH_QUARTER_NAME IN DW_MRSDASH_CLAIMS)).DASH_QUARTER_NM.;

quote:

Originally posted by Chaudhary:
Hi,

If you have created an HTML page and then passing the values from HTML control try below steps,
1. Remove the quote property values from controls make as not set.
2, add QUOTESDTRING with your param in report.
i. e. WHERE QUARTER_NAME EQ &DASH_QUARTER_NAME. QUOTEDSTRING.

hope it helps.

Your main parameter is & DASH_QUARTER_NAME. Please make only one where clause on this param.

Try this WHERE DASH_QUARTER_NAME EQ &DASH_QUARTER_NAME. QUOTEDSTRING Instead  of 

WHERE DASH_QUARTER_NAME EQ &DASH_QUARTER_NM.QUOTEDSTRING
OR DASH_QUARTER_NAME EQ &DASH_QUARTER_NM.(OR(FIND DASH_QUARTER_NAME IN DW_MRSDASH_CLAIMS)).DASH_QUARTER_NM.; and committed out STRREP function lines. 

Ok, but I thought I need the multiple OR where clause in order for the multiple selection to work.

quote:

Originally posted by Chaudhary:
Your main parameter is & DASH_QUARTER_NAME. Please make only one where clause on this param.

Try this WHERE DASH_QUARTER_NAME EQ &DASH_QUARTER_NAME. QUOTEDSTRING Instead  of 

WHERE DASH_QUARTER_NAME EQ &DASH_QUARTER_NM.QUOTEDSTRING
OR DASH_QUARTER_NAME EQ &DASH_QUARTER_NM.(OR(FIND DASH_QUARTER_NAME IN DW_MRSDASH_CLAIMS)).DASH_QUARTER_NM.; and committed out STRREP function lines. 

I tried your suggestion and selected one value from the dashboard. I got the following message. The DASH_QUARTER_NAME is defined as an A7 so I don't understand why I'm getting this error message. Thanks.

(FOC015) THE TEST VALUE IS LONGER THAN THE FIELD FORMAT LENGTH: 'Q2 2018'

This message has been edited. Last edited by: Michele Brooks,

Multiple selection will manage the list of value passed to the fex.

If you have Add Quoted set to Yes the received parameters will look like this :

'a' OR 'b' OR 'c'

Multiple without quotes

a OR b OR c

Multiple with quote but only one selection made

'a'

Multiple without quote but only one selection made

a

So you can do

WHERE QUARTER_NAME EQ &DASH_QUARTER_NAME;

When I exclude the STRREP function and have Multiple = Yes and the include quotes = I get this output when I select multiples values from the dashboard.

WHERE DASH_QUARTER_NAME EQ '''Q2 2018'' OR ''Q1 2018''';

I get the report when I select a single value.

When I include the STRREP function and have Multiple = Yes and the include quotes = I get this output when I select multiples values from the dashboard.

WHERE DASH_QUARTER_NAME EQ '''''Q2 2018'''' OR ''''Q1 2018''''';

I get this when I select a single value

WHERE DASH_QUARTER_NAME EQ '''Q1 2018''';

quote:

Originally posted by MartinY:
Multiple selection will manage the list of value passed to the fex.

If you have Add Quoted set to Yes the received parameters will look like this :

'a' OR 'b' OR 'c'

Multiple without quotes

a OR b OR c

Multiple with quote but only one selection made

'a'

Multiple without quote but only one selection made

a

So you can do

WHERE QUARTER_NAME EQ &DASH_QUARTER_NAME;

MartinY
If I use WHERE QUARTER_NAME EQ &DASH_QUARTER_NAME; it adds the quotes for multiple selections (Ex. 'Q1 2017' OR 'Q2 2017'), but if select a single value (Ex. Q1 2017) it does not put quotes around the value. The value is passed as Q1 2017 and errors out. I need a way to code my WHERE clause so that it puts single quotes around both single & multiple selections.

If I use .QUOTEDSTRING this puts single quotes around a single selection but puts triple quotes around multiple selection because I have Quoted Yes in my html page properties for that filter.

quote:

Originally posted by MartinY:
Multiple selection will manage the list of value passed to the fex.

If you have Add Quoted set to Yes the received parameters will look like this :

'a' OR 'b' OR 'c'

Multiple without quotes

a OR b OR c

Multiple with quote but only one selection made

'a'

Multiple without quote but only one selection made

a

So you can do

WHERE QUARTER_NAME EQ &DASH_QUARTER_NAME;

Chaudharry, when I tried your suggestion this is what gets passed.

WHERE DASH_QUARTER_NAME EQ '''Q2 2017'' OR ''Q1 2017'''
OR DASH_QUARTER_NAME EQ 'Q2 2017' OR 'Q1 2017';

quote:

Originally posted by Chaudhary:
Your main parameter is & DASH_QUARTER_NAME. Please make only one where clause on this param.

Try this WHERE DASH_QUARTER_NAME EQ &DASH_QUARTER_NAME. QUOTEDSTRING Instead  of 

WHERE DASH_QUARTER_NAME EQ &DASH_QUARTER_NM.QUOTEDSTRING
OR DASH_QUARTER_NAME EQ &DASH_QUARTER_NM.(OR(FIND DASH_QUARTER_NAME IN DW_MRSDASH_CLAIMS)).DASH_QUARTER_NM.; and committed out STRREP function lines. 

Michelle,
Remove the adding of quotes from your HTML selection, allow multiple WITHOUT quotes.

Then, try this(Comment out the Multiple for testing single selection):

  
SET &ECHO=ALL;
-SET &DASH_QUARTER_NM = 'Q2 2017 OR Q1 2017';
-*-SET &DASH_QUARTER_NM = 'Q2 2017';

-SET &QT_OR = '''' | ' OR ' | ''''; ;
-SET &DASH_QUARTER_NM = IF &DASH_QUARTER_NM CONTAINS ' OR ' THEN STRREP(&DASH_QUARTER_NM.LENGTH, &DASH_QUARTER_NM, 4, ' OR ', 6, &QT_OR, 2000, 'A2000') ELSE &DASH_QUARTER_NM; 
-SET &DASH_QUARTER_NM = '''' | &DASH_QUARTER_NM || '''';
-TYPE WHERE QUARTER_NAME EQ &DASH_QUARTER_NM;
-EXIT

Will do. May not be able to try this until tomorrow morning because I'll be in meetings for the remainder of the afternoon. As soon as I try it I will let you know the outcome. Thanks.

Tom Flynn, thank you so much for the resolution to my problem. Your solution worked. The code you provided is exactly what was needed. Thanks again. I love Focal Point and the support the users provide. I will be closing this post, hopefully after you've read my reply.

Hi Michele,
Cool Beans!!! Glad it worked for you!
Yes, Focal Point "can be" Very Helpful!
Have a great day!
Tom

One more option is to just use the simplified REPLACE function, beginning with release 7708 WF 8201M / Reporting Server 7707 (Sorry Tom ).

Check out the docs here:REPLACE: Replacing a String

  
-SET &DASH_QUARTER_NM = 'Q2 2017 OR Q1 2017';
-TYPE BEFORE: &DASH_QUARTER_NM

-SET &QT_OR = '''' | ' OR ' | ''''; ;
-SET &DASH_QUARTER_NM = '''' || REPLACE(&DASH_QUARTER_NM, ' OR ', &QT_OR) || '''';
-TYPE AFTER: &DASH_QUARTER_NM
-EXIT

Just adding my 2¢, for what it's worth.

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

Thanks Hallway, your 2¢ was worth it. I tried it and it works. Thanks again to all for the replies.

quote:

-SET &DASH_QUARTER_NM = 'Q2 2017 OR Q1 2017';
-TYPE BEFORE: &DASH_QUARTER_NM

-SET &QT_OR = '''' | ' OR ' | ''''; ;
-SET &DASH_QUARTER_NM = '''' || REPLACE(&DASH_QUARTER_NM, ' OR ', &QT_OR) || '''';
-TYPE AFTER: &DASH_QUARTER_NM
-EXIT

Yes, REPLACE looks like it is much easier to use, well worth the 2¢ !
I have stopped trying to remember/catalogue all the new Functions as I now work for a Bank, and, it doesn't upgrade every quarter with a new/maintenance release.
Being on 7.7.05 feels like the stone age, BUT, still have Dev Studio AND get to wear shorts to work - Bonus!!!