Conditional Record Selection Formula

[HouDog]

Hi all,

Does anyone know if you can have an If statement inside a record selection formula?

eg. if a = b then
selection formula 1
else if a = c then
selection formula 2
else
selection formula 3

 

[bdreed35]

Yes you can. You should make sure that after your report runs, you check Database -> Show SQL Query to make sure that your selection criteria gets passed to the database in the SQL string. It will work even it doesn't get passed to the database, but it is more efficient it does get passed. Read some of the help files regarding enhancing Selection Criteria.

~Brian

 

[HouDog]

Brian, i keep getting a &quot" missing error with my first "else" highlighted. I checked and double checked all my brackets and they all match up. any ideas ?

 

[bdreed35]

Please post your record selection criteria?

~Brian

 

[bdreed35]

Please post your record selection criteria

~Brian

 

[HouDog]

if ({?DateRange}(1) = Date(1900,1,1)) then
(IsNull ({Orders.Voided}) or {Orders.Voided}<>1) and
{Orders.restid} in {?RestID} and
{Orders.orderdate} = DateAdd (&quot;d&quot;, -1, CurrentDate)
else if ({?DateRange}(1) = Date(1900,1,2)) then
((IsNull ({Orders.Voided}) or {Orders.Voided}<>1) and
{Orders.restid} in {?RestID} and
{Orders.orderdate} = WeekToDateFromSun
else if ({?DateRange}(1) = Date(1900,1,3)) then
((IsNull ({Orders.Voided}) or {Orders.Voided}<>1) and
{Orders.restid} in {?RestID} and
{Orders.orderdate} = Last7Days
else if ({?DateRange}(1) = Date(1900,1,10)) then
((IsNull ({Orders.Voided}) or {Orders.Voided}<>1) and
{Orders.restid} in {?RestID} and
{Orders.orderdate} = MonthToDate
else if ({?DateRange}(1) = Date(1900,1,15)) then
((IsNull ({Orders.Voided}) or {Orders.Voided}<>1) and
{Orders.restid} in {?RestID} and
{Orders.orderdate} = CurrentDate
else if ({?DateRange}(1) = Date(1900,1,20)) then
((IsNull ({Orders.Voided}) or {Orders.Voided}<>1) and
{Orders.restid} in {?RestID} and
{Orders.orderdate} = YearToDate
else
((IsNull ({Orders.Voided}) or {Orders.Voided}<>1) and
{Orders.restid} in {?RestID} and
{Orders.orderdate} in {?DateRange}

 

[bdreed35]

The problem lies in your syntax. It looks like you have some extra open parenthesis in your each of your else if's. Delete one of the &quot;(&quot; in the statement &quot;((IsNull ({Orders.Voided}) or {Orders.Voided}<>1) and&quot; which occurs after every &quot;else if&quot;.



~Brian

 

[HouDog]

ok, i removed the excess open bracket. Then it gave me a missing &quot&quot; on the &quot;1&quot; of my first line.

As it turns out, i needed to use &quot;[]&quot; around the 1 instead of &quot;()&quot;

on a side note, is there a &quot;ThisQuarter&quot; date function ?

Thanks.

 

[bdreed35]

There is no &quot;ThisQuarter&quot; function that I know of. There is an article on CD's site giving you information on dealing with Quarters. (

http://support.crystaldecisions.com/library/kbase/articles/c2000725.asp).

Also, a good FAQ was written by synapsevampire regarding record selection criteria. Give it a read. faq766-3826

~Brian

 

[HouDog]

Brian, Thanks for your help.

 

[dgillz]

There are CalendarFirstQtr, Calendar2ndQtr, etc. functions that you may be able to sue for this.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[synapsevampire]

I have a FAQ in this forum which describes creating conditional record selection formulas. In addition to the examples I give, you can use the switch function with some success.

faq767-3825

The key is to make sure that the SQL is passed to the database using the database->show sql query

The best results are generally obtained using:

(
IsNull ({Orders.Voided})
or
{Orders.Voided}<>1
)
and
(
{Orders.restid} in {?RestID}
)
and
(
if {?DateRange}(1) = Date(1900,1,1) then
{Orders.orderdate} = CurrentDate-1
else
if {?DateRange}(1) = Date(1900,1,2) then
{Orders.orderdate} = WeekToDateFromSun
else
if .... you get the idea
)

I generally handle this by using explicit dates as Crystal functions will sometimes NOT pass the SQL, so I have formulas for every common period start and end dates, here's the last quarter:

@startdate
If {?Predefined Date Range} = &quot;Last Quarter&quot; Then
If (Month(CurrentDate) < 4) Then
DateTime(Year(CurrentDate)-1,10,1,0,0,0)
Else
DateTime(Year(CurrentDate),3*Truncate((Month(CurrentDate)+2)/3)-5,1,0,0,0)

@enddate
If {?Predefined Date Range} = &quot;Last Quarter&quot; Then
If Month (CurrentDate) < 4 Then
DateTime(Year(CurrentDate)-1,12,1,23,59,59)
Else
DateTime(Year(CurrentDate),3*Truncate((Month(CurrentDate)-1)/3)+1,1,23,59,59)-1

Then I use a record selection formula akin to:
(
{Orders.orderdate} >= @startdate
and
{Orders.orderdate} >= @enddate
)

-k

 

[HouDog]

Wow, i didn't know you could do all that. ty for the help.

 

[HouDog]

ok i changed my selection formula to the following but when I select Database | Show SQL Query, it does not reflect my formula. My formula saved ok...does this mean that my SQL Server doesn't like the formula ??

Even the IsNull line is not showing up.

Jae.

numbervar myYear := Year(minimum({?DateRange}[1]));
numbervar myMonth := Month(minimum({?DateRange}[1]));
numbervar myDay := Day(minimum({?DateRange}[1]));
datevar myDate := Date(myYear,myMonth,myDay);

(IsNull ({Orders.Voided}) or {Orders.Voided}<>1) and
{Orders.restid} in {?RestID} and
(
if myDate = Date(1900,1,1) then
{Orders.orderdate} = CurrentDate
else if myDate = Date(1900,1,2) then
{Orders.orderdate} = DateAdd (&quot;d&quot;, -1, CurrentDate)
else if myDate = Date(1900,1,3) then
{Orders.orderdate} = WeekToDateFromSun
else if myDate = Date(1900,1,10) then
{Orders.orderdate} = Last7Days
else if myDate = Date(1900,1,15) then
{Orders.orderdate} = MonthToDate
else if myDate = Date(1900,1,20) then
if Month(CurrentDate) in [1,2,3] then
{Orders.orderdate} = Calendar1stQtr
else if Month(CurrentDate) in [4,5,6] then
{Orders.orderdate} = Calendar2ndQtr
else if Month(CurrentDate) in [7,8,9] then
{Orders.orderdate} = Calendar3rdQtr
else
{Orders.orderdate} = Calendar4thQtr
else if myDate = Date(1900,1,25) then
{Orders.orderdate} = YearToDate
else
{Orders.orderdate} in {?DateRange}
)