Composite Expression

[Former Member]

Hello,

Within a sub-report in CR11 I am trying to produce a report that I need to use the select expert to use two separate date ranges as below

{CnGf_1.CnGf_1_Date} in DateTime (2013, 04, 02, 00, 00, 00) to DateTime (2013, 06, 30, 00, 00, 00) and

{CnGf_1Ins_1.CnGf_1Ins_1_Date} in DateTime (2013, 04, 02, 00, 00, 00) to DateTime (2013, 06, 12, 00, 00, 00)

but what I really need to do is something like this

{CnGf_1.CnGf_1_Amount} >= $5000.00 and

{CnGf_1.CnGf_1_Date} in DateTime (2013, 04, 02, 00, 00, 00) to DateTime (2013, 06, 30, 00, 00, 00) or

{CnGf_1Ins_1.CnGf_1Ins_1_Date} in DateTime (2013, 04, 02, 00, 00, 00) to DateTime (2013, 06, 12, 00, 00, 00)

which is only changing the 'and' operator to an 'or' but when I do this I get the message 'Composite expression'.

Can anyone help me please????

[abhilash_kumar]

Have you tried creating a Record Selection Formula instead?

Go to Report > Select Formulas > Record and paste the above formula in there.

Also, the formula should be:

{CnGf_1.CnGf_1_Amount} >= $5000.00 AND

(

{CnGf_1.CnGf_1_Date} in DateTime (2013, 04, 02, 00, 00, 00) to DateTime (2013, 06, 30, 00, 00, 00) OR

{CnGf_1Ins_1.CnGf_1Ins_1_Date} in DateTime (2013, 04, 02, 00, 00, 00) to DateTime (2013, 06, 12, 00, 00, 00)

)

Notice the round braces.

-Abhilash

[Former Member]

Thanks Abhilash, but I am afraid it didn't work might it have something to do with this being in a sub report?

R

[abhilash_kumar]

When you paste the formula in the formula editor for record selection, do you get an error?

-Abhilash

[Former Member]

there is no error but it doesn't return any results (I have double checked that there should be results).

This is what I am seeing

[abhilash_kumar]

Go to the Database option on the top > Select Show SQL Query and see if you see a where clause with the conditions above.

-Abhilash

[Former Member]

Hi Abhilash, I really appreciate all your help on this. SQL is not really my thing so have copied the text here

SELECT `CnGf_1`.`CnGf_1_Type`, `CnBio`.`CnBio_Name`, `CnGf_1`.`CnGf_1_Amount`, `CnGf_1`.`CnGf_1_Date`, `CnGf_1Ins_1`.`CnGf_1Ins_1_Date`

FROM   ((`Cn` `Cn` INNER JOIN `CnBio` `CnBio` ON `Cn`.`CnBio_LINK`=`CnBio`.`CnBio_LINK`) INNER JOIN `CnGf_1` `CnGf_1` ON `Cn`.`CnGf_1_LINK`=`CnGf_1`.`CnGf_1_LINK`) INNER JOIN `CnGf_1Ins_1` `CnGf_1Ins_1` ON `CnGf_1`.`CnGf_1Ins_1_LINK`=`CnGf_1Ins_1`.`CnGf_1Ins_1_LINK`

WHERE  `CnGf_1`.`CnGf_1_Amount`>=5000 AND ((`CnGf_1`.`CnGf_1_Date`>=#2013-04-02 00:00:00# AND `CnGf_1`.`CnGf_1_Date`<#2013-06-30 00:00:01#) OR (`CnGf_1Ins_1`.`CnGf_1Ins_1_Date`>=#2013-04-02 00:00:00# AND `CnGf_1Ins_1`.`CnGf_1Ins_1_Date`<#2013-06-12 00:00:01#)) AND (`CnGf_1`.`CnGf_1_Type`='Cash' OR `CnGf_1`.`CnGf_1_Type`='Legacy Gift' OR `CnGf_1`.`CnGf_1_Type`='Pay-Cash' OR `CnGf_1`.`CnGf_1_Type`='Recurring Gift Pay-Cash')

[abhilash_kumar]

I see the where clause and the conditions you added in the select expert. Which means CR does ask the database to return records that have:

{CnGf_1.CnGf_1_Amount} >= $5000.00 and

(

{CnGf_1.CnGf_1_Date} in DateTime (2013, 04, 02, 00, 00, 00) to DateTime (2013, 06, 30, 00, 00, 00) or

{CnGf_1Ins_1.CnGf_1Ins_1_Date} in DateTime (2013, 04, 02, 00, 00, 00) to DateTime (2013, 06, 12, 00, 00, 00)

)

If you query the database with the same query you should see the same results.

Could you check the database records again? If you're uncomfortable with SQL, you can export the database records to Excel and add filters with the same condition above.

-Abhilash

[Former Member]

I have double checked the data again and CR is  not showing the results that it should be. The same formula works perfectly fine when you run it as

{CnGf_1.CnGf_1_Amount} >= $5000.00 and

(

{CnGf_1.CnGf_1_Date} in DateTime (2013, 04, 02, 00, 00, 00) to DateTime (2013, 06, 30, 00, 00, 00) and

{CnGf_1Ins_1.CnGf_1Ins_1_Date} in DateTime (2013, 04, 02, 00, 00, 00) to DateTime (2013, 06, 12, 00, 00, 00)

)

but as soon as you change the and to an or it no longer works, could this be something to do with nulls???

[abhilash_kumar]

If it was the NULLs, I'm guessing it should have caused the above formula to fail too.

-Abhilash

[Former Member]

ok thanks for your help.....