Formula Syntax Error

[f0rg3tfu1]

Hello everyone,

Im having a problem with the syntax of this statement:
CR 10
Connected to SQL server.

The error states "The ) is missing" at the first comma (indicated by the red line.

Its only in the expressions where I have multiple values in the IN statement.

Also, do I have the wildcard syntax correct? (?)


If {?Choose Clins} = "FFP" then
{adq_vw_rpt_ts_sum_craft_by_emplid.Project ID} IN
("02009.(?)01.00.0000", "02009.(?)02.00.0000",
"02009.(?)03.00.0000", "02009.(?)04.00.0000", "02009.(?)05.00.0000", "02009.(?)06.00.0000",
"02009.(?)07.00.0000", "02009.(?)08.00.0000", "02009.(?)09.00.0000", "02009.(?)10.00.0000",
"02009.(?)11.00.0000", "02009.(?)12.00.0000", "02009.(?)13.00.0000", "02009.(?)14.00.0000",
"02009.(?)15.00.0000", "02009.(?)19.00.0000", "02009.(?)20.00.0000") else

 

[synapsevampire]

I'll assume that you want a record selection formula here to filter rows being returned, use Report-Selection Formula->Record:

(
If {?Choose Clins} = "FFP" then
substr({adq_vw_rpt_ts_sum_craft_by_emplid.Project ID},8,2) in ["01","02","03",etc...]
else If {?Choose Clins} <> "FFP" then
true
)

Try posting technical information if you need additional assistance:

Example data
Expected output

What is this formula intended to do and where?

Since the data is all in the same place with the same prefix and suffix, you don't need a LILE

Like would be (uses an asterisk):

(
If {?Choose Clins} = "FFP" then
{adq_vw_rpt_ts_sum_craft_by_emplid.Project ID} like
["02009."+"*"+"01.00.0000","02009."+"*"+"02.00.0000",etc...])

-k

 

[f0rg3tfu1]

This may work... thanks.

Your correct that yes, this is to filter down the records returned.

Here is the whole formula that I am using.

If {?Choose Clins} = "FFP" then
{adq_vw_rpt_ts_sum_craft_by_emplid.Project ID} IN ("02009.(?)01.00.0000", "02009.(?)02.00.0000",
"02009.(?)03.00.0000", "02009.(?)04.00.0000", "02009.(?)05.00.0000", "02009.(?)06.00.0000",
"02009.(?)07.00.0000", "02009.(?)08.00.0000", "02009.(?)09.00.0000", "02009.(?)10.00.0000",
"02009.(?)11.00.0000", "02009.(?)12.00.0000", "02009.(?)13.00.0000", "02009.(?)14.00.0000",
"02009.(?)15.00.0000", "02009.(?)19.00.0000", "02009.(?)20.00.0000") else

If {?Choose Clins} = "L3" then
{adq_vw_rpt_ts_sum_craft_by_emplid.Project ID} IN ("02009.(?)16.00.0000") else

If {?Choose Clins} = "Com/Cor" then
{adq_vw_rpt_ts_sum_craft_by_emplid.Project ID} IN ("02009.(?)17.00.0000") else

If {?Choose Clins} = "MRI" then
{adq_vw_rpt_ts_sum_craft_by_emplid.Project ID} IN ("02009.(?)18.00.0000") else

If {?Choose Clins} = "NP" then
{adq_vw_rpt_ts_sum_craft_by_emplid.Project ID} NOT IN ("02009.(?)01.00.0000", "02009.(?)02.00.0000",
"02009.(?)03.00.0000", "02009.(?)04.00.0000", "02009.(?)05.00.0000", "02009.(?)06.00.0000",
"02009.(?)07.00.0000", "02009.(?)08.00.0000", "02009.(?)09.00.0000", "02009.(?)10.00.0000",
"02009.(?)11.00.0000", "02009.(?)12.00.0000", "02009.(?)13.00.0000", "02009.(?)14.00.0000",
"02009.(?)15.00.0000", "02009.(?)19.00.0000", "02009.(?)20.00.0000", "02009.(?)16.00.0000",
"02009.(?)17.00.0000", "02009.(?)18.00.0000")

I want to select one of five different groups … each group contains 1-15 different “CLINS”, which is the selection criteria.

Example (Expected Output):

Group A – 1,3,5,7
Group B – 2,4,6,8
Group C – 9,10
Group D – 1,2,8,3

So when you select group A, you only get the results where Project ID = 1,3,5,7.

Hope this makes sense

 

[lbass]

I think you would have to use the mid function instead of substr in SV's example. Not sure what position to use for the wildcard--are you using (?) to stand for one character, or is the character enclosed in parens?

Also, please note that you should be using brackets [] not parens () to enclose the arrays. Try:

(
If {?Choose Clins} = "FFP" then
mid({adq_vw_rpt_ts_sum_craft_by_emplid.Project ID},7,2) in ["01","02","03",etc...]
else If {?Choose Clins} <> "FFP" then
true
)

-LB

 

[synapsevampire]

Ooops, LB is right, I was coding real SQL and Javascript all day, hard to remember the differences sometimes, use MID.

-k

 

[f0rg3tfu1]

Got... i think its working with no errors now.

Now my question is:

How do I set the report so that crystal only returns the selected results? I dragged the formula into the details field, and even when I select the parameters it returns all values. Do i need to set the field equal to the formula?

Also, if I wanted to make this code snippet NOT equal to the array do I just use NOT IN???

If {?Choose Clins} = "MRI" then
{adq_vw_rpt_ts_sum_craft_by_emplid.Project ID} in ["02009." + "*" + "18.00.0000"]

You guys are doing awesome... I am almost finished with this report!!!

 

[lbass]

Your formula belongs in the record selection statement. Go to report->edit selection formula->record and enter it there.

The syntax for "not in" is:

(
If {?Choose Clins} = "FFP" then
not (
mid({adq_vw_rpt_ts_sum_craft_by_emplid.Project ID},7,2) in ["01","02","03",etc...]
)
else If {?Choose Clins} <> "FFP" then
true
)

Note the parentheses enclosing the phrase.

-LB

 

[f0rg3tfu1]

Nice job guys, I appreciate your help!