Finding A String

[Sheldon]

How can I find or filter, say, the string 'NY' in positions 1 & 2 of a field or, for that matter, anywhere in a field using filtering?  I tried Left and Instr but they didn't seem to work.  I wish the Report Studio manual had examples for all the functions, operators, etc. that it contains.

Sheldon

[Nuffsaid]

Hi,

I tried Left and Instr but they didn't seem to work.

What happened? Any error messages?

This works for me;

if(instr([YourStringField],'NY')>0)
then ('Found One')
Else ('Not Present')


Nuffsaid.

[Sheldon]

Tried:
If(instr([Student Enrollment].[District].[District Code],'NY')>0) Then ('Found One') Else ('Not Present') and got:
I can't seem to copy/paste my errors so I'll try and summarize them -
QE-DEF-0459 CCLException
RQP-DEF-0149 The query specification is incorrect
RQP-DEF-0217 Wrong expression type:
If(instr([Student Enrollment].[District].[District Code],'NY')>0) Then ('Found One') Else ('Not Present')
RQP-DEF-0457 Referenced Query 'Query1' is not defined or its query items contain unresolved references.

[Nuffsaid]

Hmmmm.......


This looks suspect;

RQP-DEF-0457 Referenced Query 'Query1' is not defined or its query items contain unresolved references.

What is your reporting database, Oracle, SQL Server ??

What happens if you just drop the [Student Enrollment].[District].[District Code] data item on to the report canvas? Does it return results?


Nuffsaid.

[Sheldon]

Our reporting database is Oracle.

If I change my filter to:
[Student Enrollment].[District].[District Code] contains 'NY'
I get no errors.

And if I drop the [Student Enrollment].[District].[District Code] data item on to the report canvas, it does return (expected) results.

Sheldon

[Nuffsaid]

Hey again,

How can I find or filter, say, the string 'NY'

The if then else expression posted above will "find" the string if you use it on your report canvas.

However if you wish to use it in a filter you have to drop the if then else logic.

Try this expression in your filter;

instr([Student Enrollment].[District].[District Code],'NY')>0


Nuffsaid.

[Sheldon]

Thanks, that solved it! 

Should the Left function work in the report canvas or filter?

[Nuffsaid]

Don't believe there is a Left function. Substring (SUBSTR) would be comparable. You could use it in either.
Downside is "NY" would have to occupy the same position in the string for every record, or you would have to locate it using nested functions.

Sounds like to much work.....    ::)

BTW.....

I wish the Report Studio manual had examples for all the functions, operators, etc. that it contains.

Chapter 10, Using The Expression Editor. This lists most of the functions available but the examples leave a lot to be desired...


Nuffsaid.