Add Dynamic Parameter for first 2 characters of Field

[GCL2007]

I want to set up dynamic parameter in CR XI. Field is 4 or 5 characters, however I want to only use left 2 characters as dynamic parameter as well as for use as grouping on the report. Any thoughts on how best to do this? Thanks

 

[lbass]

For the dynamic parameter, you should use a command, using a left function--depends upon your database though, which you haven't identified.

Select left(table.field,2)
from table

Or maybe it would be:

select {fn left(table.field,2)}
from table

Use this to populate the dynamic parameter picklist. Do NOT, however, reference the field in the main report, and do not link the command to any tables in the main report. There you should just use a formula for your group:

left({table.field},2)

-LB

 

[GCL2007]

Thanks LB.,.
Stil having problems with syntax with Left. Am using Oracle.
If I just insert this in Add Command in Crystal, it works

Select code from pcode

but if I try something like this

Select left(pcode.code,2) from pcode

I get ORA-00904 error "LEFT": invalid identifier error

Sorry, know this is basic stuff but don't do a lot with SQL. Any thoughts?

 

[Turkbear]

Hi,
In Oracle, the way to get a subset of characters is to use the
SUBSTR({Data},start,length)
function, so:

Select SUBSTR(pcode.code,1,2) from pcode





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

I think you could also use:

select {fn left("table"."field",2)} "Two Chars"
from "table"

...but I think you have to be using an ODBC driver for this to work. For a native driver (Oracle Server), I think you do have to use the substring function.

-LB