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,

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';

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.

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');

John,

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

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

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..

That works!

Thank you, Alan!

Regards,

John

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

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?

John,

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

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

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.

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,

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.

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

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,