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     Using display values of WHERE in page heading

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Using display values of WHERE in page heading
 Login/Join
 
Platinum Member
posted
I need to use the display values of the following WHERE statement in the page heading.

WHERE VENDOR_CATEGORY EQ &VENDOR_CAT.(OR(<Blank, >,<.,.>,<A,A>,<C,C>,<Premier,P>,<Tier 2,T)).VENDOR_CATEGORY.;

I need the Select All option that appears in the drop-down list to also appear in the heading, as "All Categories". There are spaces as values and missing values (the "."). in this field. I tried doing the following define and variations of it. It works fine except for when the value is a space, which alsways translates to "All Categories" instead of "Blank".

VENDOR_CATEGORY_2/A14 = IF VENDOR_CATEGORY EQ '' THEN 'All Categories' ELSE
IF VENDOR_CATEGORY EQ ' ' THEN 'Blank' ELSE
IF VENDOR_CATEGORY EQ MISSING THEN '.' ELSE
IF VENDOR_CATEGORY EQ 'A' THEN 'A' ELSE
IF VENDOR_CATEGORY EQ 'C' THEN 'C' ELSE
IF VENDOR_CATEGORY EQ 'P' THEN 'Premier' ELSE
IF VENDOR_CATEGORY EQ 'T' THEN 'Tier 2' ELSE 'ERROR';

Any suggestions?

Thanks,

John

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


WF 7.7.03, Windows 7, HTML, Excel, PDF
 
Posts: 225 | Location: San Francisco Bay Area, California | Registered: October 26, 2006Report This Post
Virtuoso
posted Hide Post
John,

I understand that the field VENDOR_CATEGORY can have the MISSING value so you could try the following DEFINE statement:

VENDOR_CATEGORY_2/A14 MISSING ON =
IF VENDOR_CATEGORY EQ MISSING THEN 'All Categories' ELSE
IF VENDOR_CATEGORY EQ ' ' THEN 'Blank' ELSE
IF VENDOR_CATEGORY EQ 'A' THEN 'A' ELSE
IF VENDOR_CATEGORY EQ 'C' THEN 'C' ELSE
IF VENDOR_CATEGORY EQ 'P' THEN 'Premier' ELSE
IF VENDOR_CATEGORY EQ 'T' THEN 'Tier 2' ELSE 'ERROR';


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Platinum Member
posted Hide Post
Thanks, Daniel. This isn't what I'm looking for.

Only if the user chooses the Select All option do we want to see all records.

If the user chooses "Blank", we want to see only those records where a space was entered into the VENDOR_CATEGORY field.

If the user chooses ".", we only want to show those records where the value for VENDOR_CATEGORY is missing. (This option works okay).

The WHERE statement works correctly. Getting all the display values into the heading is the issue. Both blank and Select All show up as a space in the heading, which is why I tried using a define. Everything else works okay.


WF 7.7.03, Windows 7, HTML, Excel, PDF
 
Posts: 225 | Location: San Francisco Bay Area, California | Registered: October 26, 2006Report This Post
Virtuoso
posted Hide Post
John,
Ok. I misunderstood.
I suppose you are using Auto-Prompting and since you have a static list you also get in your list box "Select All" which returns all the values in &VENDOR_CAT.
So, maybe instead of having in your HEADING the field VENDOR_CATEGORY_2 you could have the parameter &VC2 as follows:
-SET &VC2=IF &VENDOR_CAT CONTAINS 'OR' THEN 'All Categories' ELSE
- DECODE &VENDOR_CAT(' ' Blank '.' 'MISSING' A A C C P Premier T 'Tier 2');


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Virtuoso
posted Hide Post
John,

What happens if 2 or more items are selected? That needs to be allowed for.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Platinum Member
posted Hide Post
Alan,

That's a good point! I'd like the heading to say "multiple vendors". This way we can re-use this technique in other reports that may have many values for the field used in the WHERE statement.

Any thoughts on how to code that?

Thanks,

John


WF 7.7.03, Windows 7, HTML, Excel, PDF
 
Posts: 225 | Location: San Francisco Bay Area, California | Registered: October 26, 2006Report This Post
Virtuoso
posted Hide Post
John,

It's a bit messy, but try this:

-* work out the maximum string length, remember that all values are surrounded by single quotes..
-SET &T_H =IF &VENDOR_CAT.LENGTH GE 37 THEN 'All categories' ELSE 
- IF &VENDOR_CAT CONTAINS 'OR' THEN 'Multiple Vendors' ELSE
- DECODE &VENDOR_CAT(''' ''' Blank, 
-                    '''.''' 'Missing', 
-                    '''P''' 'Premier', 
-                    '''T''' 'Tier 2',
-                    '''A''' 'A',
-                    '''C''' 'C');

TABLE FILE fn
HEADING
" &T_H "
.
.
WHERE CATEGORY EQ &VENDOR_CAT.(OR(<Blank, >,<.,.>,<A,A>,<C,C>,<Premier,P>,<Tier2,T>)).VENDOR_CATEGORY.


See if that works..


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Platinum Member
posted Hide Post
That works!

Thank you, Alan!

Regards,

John


WF 7.7.03, Windows 7, HTML, Excel, PDF
 
Posts: 225 | Location: San Francisco Bay Area, California | Registered: October 26, 2006Report This Post
Platinum Member
posted Hide Post
Hi Alan,

Here's the next twist in this:

These values are hardcoded. This technique needs to be revised so that if new codes are entered for that field, this technique will still work.

So both the &T_H and the WHERE need to be revised.

Any suggestions?

Thanks,

John


WF 7.7.03, Windows 7, HTML, Excel, PDF
 
Posts: 225 | Location: San Francisco Bay Area, California | Registered: October 26, 2006Report This Post
Virtuoso
posted Hide Post
John

If what you are saying is that the datafile holds more codes than these, and you want to get the WHERE clause built up to include them, I think you are on a loser there. Auto prompt parses the code before any work is done and cannot be called afterwards. From a previous topic, just trying to get a default value in was a kluge.

There are a couple more things that could be done, but then it is getting awful messy and you would be better off building your own launch page I believe.

How desperate are you for this?


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Virtuoso
posted Hide Post
John,

I also hink you should use your own launch page, a Self-Service App, so to speak.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Platinum Member
posted Hide Post
For some reason my last post did not go thru. I'll try again. Here's the solution:

-SET &SHOW_VENDOR = IF &VENDOR_CAT EQ 'FOC_NONE' THEN 'All categories' ELSE
- IF &VENDOR_CAT CONTAINS ' OR ' THEN 'Multiple Vendors' ELSE
- IF &VENDOR_CAT EQ '''''' THEN 'Blank' ELSE
- IF &VENDOR_CAT EQ '''.''' THEN 'Missing' ELSE
- IF &VENDOR_CAT EQ '''P''' THEN 'Prestige' ELSE
- IF &VENDOR_CAT EQ '''T''' THEN 'Tier 2' ELSE
- IF &VENDOR_CAT EQ '''A''' THEN 'A' ELSE
- IF &VENDOR_CAT EQ '''C''' THEN 'C' ELSE &VENDOR_CAT;
-*
-*
DEFINE FILE INVOICE_DATA
VENDOR_CATEGORY_2/A16 = IF VENDOR_CATEGORY EQ ' ' THEN 'Blank' ELSE
IF VENDOR_CATEGORY EQ MISSING THEN 'Missing' ELSE
IF VENDOR_CATEGORY EQ 'A' THEN 'A' ELSE
IF VENDOR_CATEGORY EQ 'C' THEN 'C' ELSE
IF VENDOR_CATEGORY EQ 'P' THEN 'Prestige' ELSE
IF VENDOR_CATEGORY EQ 'T' THEN 'Tier 2' ELSE VENDOR_CATEGORY;
END

TABLE FILE INVOICE_DATA

...

HEADING
"Vendor Sales Summary"
"For &SHOW_VENDOR"

WHERE VENDOR_CATEGORY EQ &VENDOR_CAT.(OR(FIND VENDOR_CATEGORY IN INVOICE_DATA)).VENDOR_CATEGORY.;

END

It doesn't matter if new codes are entered into the system and don't get translated like the P and T codes.

Re: launch page suggestion -- All the reports being built here get their own lauch page.

Regards,

John


WF 7.7.03, Windows 7, HTML, Excel, PDF
 
Posts: 225 | Location: San Francisco Bay Area, California | Registered: October 26, 2006Report This Post
Virtuoso
posted Hide Post
Okay,

but what happens in the selection box for the users when the category is blank or missing? They just get a blank line or . in the select?

Which sort of goes against what I thought you were trying ot achieve.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Platinum Member
posted Hide Post
Thanks for pointing that out, Alan. I changed the dot to "Missing" in the WHERE, and now the select box shows "Blank" and "Missing"

WHERE VENDOR_CATEGORY EQ &VENDOR_CAT.(OR(<Blank, >,<Missing,.>,<A,A>,<C,C>,<Premier,P>,<Tier 2,T>)).VENDOR_CATEGORY.;



However, for the launch page, when I changed the control values from static to dynamic in the parameters dialog box of the HTML Layout Painter, both show up as blank lines. Is this what you were referring to?

So, 1) How can I get a define field to be used for the Value field, and 2) how can I get a field in the Display field text box underneath it?


Thanks,

John

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


WF 7.7.03, Windows 7, HTML, Excel, PDF
 
Posts: 225 | Location: San Francisco Bay Area, California | Registered: October 26, 2006Report This Post
Virtuoso
posted Hide Post
This is exactly what I was referring to. What I, and I think Danny, were referring to was a complete self service approach.

Using the WF tool set I cannot think of a way for you to realy achieve what you need.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Platinum Member
posted Hide Post
We wound up putting an explanation on the launch page explaining the 2 blank lines in the drop-down list.

Thanks for all your help.

Regards,

John


WF 7.7.03, Windows 7, HTML, Excel, PDF
 
Posts: 225 | Location: San Francisco Bay Area, California | Registered: October 26, 2006Report This Post
Virtuoso
posted Hide Post
Oh John

Just when I had a thought... An example from the car file, fixed values in one selection, others from file in second selection
-DEFAULTS &SEL_BODY_1=' ';
-DEFAULTS &SEL_BODY_2=' ';

-SET &X=''''|X|'''';

-SET &SEL_BODY_1 = IF &SEL_BODY_1 NE ' ' THEN &SEL_BODY_1 
- ELSE &X;

-SET &SEL_BODY_2 = IF &SEL_BODY_2 EQ ' ' 
- OR &SEL_BODY_2 EQ FOC_NONE THEN &X ELSE &SEL_BODY_2;


TABLE FILE CAR
PRINT COUNTRY CAR MODEL BODYTYPE
WHERE BODYTYPE EQ  
 &SEL_BODY_1.(OR(<SEDAN,SEDAN>,<COUPE,COUPE>)).Selection 1. 
  OR BODYTYPE  EQ 
 &SEL_BODY_2.(OR(FIND BODYTYPE IN CAR IF BODYTYPE NE 'SEDAN' OR 'COUPE')).Selection 2.  
ON TABLE PCHOLD FORMAT HTML
END

Just another workaround.

Put up the wrong version, sorry.

This message has been edited. Last edited by: Alan B,


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report 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     Using display values of WHERE in page heading

Copyright © 1996-2020 Information Builders