Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]WF 8201M App Studio STRREP function (updated with replies)

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]WF 8201M App Studio STRREP function (updated with replies)
 Login/Join
 
Guru
posted
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,


WF 8205, Windows 10
Oracle DBMS
EXL07/PDF Output
 
Posts: 244 | Registered: August 27, 2012Report This Post
Platinum Member
posted Hide Post
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.


WF Production :- WF:8.0.0.4, 8.1.05 App-studio/Developer Studio(8.1.x) ,
8.2.0.1M , 8.2.0.2 (App-Studio8.2.x),
InfoAssist/+, InfoDiscovery
Output format:-AHTML, PDF, Excel, HTML
Platform:-Windows 7, 8,10
 
Posts: 186 | Location: Infobuild India | Registered: August 28, 2015Report This Post
Guru
posted Hide Post
Right-Click on your variable and see if it's set to multiple.


WebFOCUS Administrator @ Worldpay FIS
PROD/DEV/TEST: 8204, SANDBOX: 8206 soon - BIP, Reportcaster, Resource Manager, EUM, HyperStage soon, DB: HIVE,Oracle,MSSQL
 
Posts: 291 | Location: Greater Cincinnati  | Registered: May 11, 2005Report This Post
Guru
posted Hide Post
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.


WF 8205, Windows 10
Oracle DBMS
EXL07/PDF Output
 
Posts: 244 | Registered: August 27, 2012Report This Post
Guru
posted Hide Post
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.


WF 8205, Windows 10
Oracle DBMS
EXL07/PDF Output
 
Posts: 244 | Registered: August 27, 2012Report This Post
Guru
posted Hide Post
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.


WF 8205, Windows 10
Oracle DBMS
EXL07/PDF Output
 
Posts: 244 | Registered: August 27, 2012Report This Post
Platinum Member
posted Hide Post
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. 


WF Production :- WF:8.0.0.4, 8.1.05 App-studio/Developer Studio(8.1.x) ,
8.2.0.1M , 8.2.0.2 (App-Studio8.2.x),
InfoAssist/+, InfoDiscovery
Output format:-AHTML, PDF, Excel, HTML
Platform:-Windows 7, 8,10
 
Posts: 186 | Location: Infobuild India | Registered: August 28, 2015Report This Post
Guru
posted Hide Post
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,


WF 8205, Windows 10
Oracle DBMS
EXL07/PDF Output
 
Posts: 244 | Registered: August 27, 2012Report This Post
Virtuoso
posted Hide Post
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;


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Guru
posted Hide Post
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;


WF 8205, Windows 10
Oracle DBMS
EXL07/PDF Output
 
Posts: 244 | Registered: August 27, 2012Report This Post
Guru
posted Hide Post
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;


WF 8205, Windows 10
Oracle DBMS
EXL07/PDF Output
 
Posts: 244 | Registered: August 27, 2012Report This Post
Guru
posted Hide Post
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. 


WF 8205, Windows 10
Oracle DBMS
EXL07/PDF Output
 
Posts: 244 | Registered: August 27, 2012Report This Post
Expert
posted Hide Post
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


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Guru
posted Hide Post
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.


WF 8205, Windows 10
Oracle DBMS
EXL07/PDF Output
 
Posts: 244 | Registered: August 27, 2012Report This Post
Guru
posted Hide Post
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.


WF 8205, Windows 10
Oracle DBMS
EXL07/PDF Output
 
Posts: 244 | Registered: August 27, 2012Report This Post
Expert
posted Hide Post
Hi Michele,
Cool Beans!!! Glad it worked for you!
Yes, Focal Point "can be" Very Helpful!
Have a great day!
Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Master
posted Hide Post
One more option is to just use the simplified REPLACE function, beginning with release 7708 WF 8201M / Reporting Server 7707 (Sorry Tom Frowner ).

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,


Hallway

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report This Post
Guru
posted Hide Post
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


WF 8205, Windows 10
Oracle DBMS
EXL07/PDF Output
 
Posts: 244 | Registered: August 27, 2012Report This Post
Expert
posted Hide Post
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!!!


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]WF 8201M App Studio STRREP function (updated with replies)

Copyright © 1996-2020 Information Builders