How to create a file picklist

[MitraMIS]

Hi All;

I'm using "File Picklist" type in my "Prompt Definitin".

I have created a file in notepad as below.
All
0 Open
1 Issued
2 Closed
3 Cancelled

Valid values are Null,0,1,2,3. I need to put a description infront of it.

With description infront of it, no data gets returned. If I remove the description it works perfect.
Is there any kind of delimeter that I need to enter between value and description or ...???

TIA;
Mitra

 

[griffindm]

The File picklist does not support multiple columns per row. What I suggest is that you make a single row, as in

0-Open
1-Issued
2-Closed
3-Cancelled

Then just use a substring of the selection in your filter.

Dave Griffin



The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[MitraMIS]

Thanks Dave. That worked perfectly.
I have another question realted to substring.
I have a record id that looks like
20*12536*C/V10589
OR
20*35428*45678
How can I get the string that apperas after second "*",meaning C/V10589 AND 45678.
If I use POSITION function that gives me the position of the first occur of "*".
If I use substring function I need to specify how many chracters. As you see in above two examples it's not a fix nbr.
Thanks for your help;
Mitra

 

[griffindm]

Mitra,

Look for the Impromptu function instr. This supports declaring the nth occurance, which should help you find the position after the second '*' in the field. It may not be available on some databases.

Dave Griffin



The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[MitraMIS]

Dave;

I don't have it.

That's a bummer!

Thanks for your help;

Mitra

 

[DoubleD]

MitraMIS,
You could also do a nested POSITION function.
Get the position of the first occurence, then get a substring based on that value, then do a POSITION function on the text string you have left.

I am what I am based on the decisions I have made.

DoubleD

 

[MitraMIS]

Thanks it worked.
I got it after using substring, position and char_length functions 300 times.
Mitra