Filter on multiple criteria

[ReportingMil]

My report is grouped by status and sorted by the status column. Through the UI the grouping functionality accepts only one column as the criteria. How can I filter based on a combination of criteria?

Records to display in the first section (active or under renewal) if: Status is Active or (status is Renewal AND the expiration date is more recent than the last 12 months).

Records to display in the second section (Expired) if: Status is Expired or (status is Renewal AND the expiration date is older than 12 months ago from today).

[JWiseman · Accepted solution]

Hi ReportingMil, are you using the full / standalone version of Crystal Reports? If so create a new formula and write out the logic that you mention but use Crystal formula syntax...not sure how that would be written as there is no indication on what fields determine status, expirations, etc. Afterwards, right click on the existing Group Header and choose Change Group and then use your new formula instead. 

if {your status field} in ["your active value", "your renewal value"]
then "Active or Under Renewal"
else if {your status field} = "renewal" and datediff("d",{your experiration date},currentdate) > 365
then "Expired"
else "Your Default Value"

[ReportingMil · Accepted solution]

Deleted

[ReportingMil · Accepted solution]

Hi JWiseman, thanks for your reply. I am using SAP Crystal Reports 2016 Support Pack 6 Version 14.2.6.2839. Product Type: Full.

Formula for the first group: If {Table1.status} startswith 'Renewal' And {Table1.ST_LIC_EXPIRE} in DateAdd("yyyy", -1, CurrentDate) To CurrentDate Then True Else False.

Thanks for the tip on using the Change Group Option. When I select Use a Formula as Group Sort Order, it takes me to the Current Formula Folder X-1 Group Sort Order Formula. However, even though the formula I stated earlier was accepted in the Formula Fields folder, I receive an error pasting it in the Group Sort Order Formula section that {Table1.status} needs to be a constant expression.

The above should return records that meet those criteria. Then I want the next set of records that are expired to show in the next section of the report (the second criteria from my initial posting.

[JWiseman · Accepted solution]

The suggestion was to use a Formula as the Group vs using it in the Group Sort Order.

• Create a single formula that will have an If Then Else
• The new group will output a value of either "Active or Under Renewal" or "Expired"
• The new formula will replace your existing group
• Once the group is changed to the formula, use a Specified Order to sort the group.
• This will give you 2 "sections" as there will be two instances of Group 1, "Active or Under Renewal" or "Expired".
• Afterwards you can add a 2nd group level below this new outer group if you need to group the data in each instance of Group 1.

[ReportingMil · Accepted solution]

This is really helpful. I am getting this, but can't see something wrong with the formula: 

A number, currency amount boolean, date, time, date-time, or string is expected here. I checked the format of the expiration date/time, and it seems to be a Crystal accepted format. Sample: 2022-10-01 00:00:00.000

if {Table1.status} = "ACTIVE" then
"Active or Under Renewal Process"
else if {Table1.status} = "RENEWAL PROCESS" then
if DateDiff("d", {Table1.ST_LIC_EXPIRE}, CurrentDate) < 365 then
"Active or Under Renewal Process"
else
"Expired" else "Unknown Status"

[JWiseman · Accepted solution]

In your formula on the 3rd to 4th line there's a "then if"...I'm guessing that you want an "and" there. Also at the end you've got 2 different default "else" conditions. I've commented out the last one...let me know how you want the logic to work for the grouping formula.

if {Table1.status} = "ACTIVE"
then "Active or Under Renewal Process"
else if (
     {Table1.status} = "RENEWAL PROCESS" and
     DateDiff("d", {Table1.ST_LIC_EXPIRE}, CurrentDate) < 365
) then "Active or Under Renewal Process"
else "Expired"

// else "Unknown Status"

[ReportingMil · Accepted solution]

That makes sense. With the updated snippet I get: A number, currency amount, boolean, date, time, date-time, or string is expected here.

Then it shows this:

if IsNull ({@Filter})
then 0
else if {@Filter} = "Active or Under Renewal Process" or

then -3
else if {@Filter} = "Expired"
then -2
else 0

[JWiseman · Accepted solution]

The extra "or" on line 3 looks like the culprit.

[ReportingMil · Accepted solution]

I see it but I didn't write that. That is what Crystal presented to me. This tells me something in the original formula is not acceptable by Crystal is my guess.

[JWiseman · Accepted solution]

Try putting {@Filter} on your report in the Details section and see what the output is. If it gives you an error right away, then post your current {@Filter} formula plus a screenshot of what's going on in the Formula Editor...i.e. the top left where is shows the values and the point of failure.

[ReportingMil · Accepted solution]

The report is running now. However, the result is showing ST_LIC_EXPIRE greater than 365 days as "Active or Under Renewal Process", instead of Expired.

if {Table1.status} = "ACTIVE" then "Active or Under Renewal Process"
else if {Table1.status} = "RENEWAL PROCESS" then if datediff("d", {Table1.ST_LIC_EXPIRE}, currentdate) < 365 then "Active or Under Renewal Process"
else "Expired"

Sample:

[JWiseman · Accepted solution]

In line 2 are you trying to satisfy 2 conditions? If so try using an "and" instead...i.e.

if {Table1.status} = "ACTIVE"
then "Active or Under Renewal Process"
else if {Table1.status} = "RENEWAL PROCESS" and datediff("d", {Table1.ST_LIC_EXPIRE}, currentdate) < 365
then "Active or Under Renewal Process"
else "Expired"

Also if you get these kinds of issues you might want to consider creating a test formula to test each line...

1. Create a new formula named test
2. In the formula use the syntax {Table1.status} = "ACTIVE" 
3. Put the formula in your Details section and check if the boolean values match your requirement
4. Change the formula to {Table1.status} = "RENEWAL PROCESS" and datediff("d", {Table1.ST_LIC_EXPIRE}, currentdate) < 365
5. Check if the boolean values match your requirement

[ReportingMil · Accepted solution]

Getting really close. The data is now showing in the Preview tab. However, when running in the test environment, I am still getting:

Formula:

if {Table1.status} = "ACTIVE"
then "Active or Under Renewal Process"
else if {Table1.status} = "RENEWAL PROCESS" and datediff("d", {Table1.ST_LIC_EXPIRE}, currentdate) < 365
then "Active or Under Renewal Process"
else "Expired"

Below is what it's showing me. I keep deleting the "or" at the end of the third line and it keeps coming back.

if IsNull ({@Sorting})
then 0
else if {@Sorting} = "ACTIVE or UNDER RENEWAL PROCESS" or

then -3
else if {@Sorting} = "EXPIRED"
then -2
else

[JWiseman · Accepted solution]

Check the specifics that come up in the Formula Workshop debug panel when that error occurs. It should point to the field value that is causing the issue. The error could be due to the expiration date not being a valid date.

[ReportingMil · Accepted solution]

I checked the Table1.ST_LIC_EXPIRE format and it seems to be supported. The app is highlighting the then after the or. I remove the "or" and save every time I open the file.

[ReportingMil · Accepted solution]

This is solved. I deleted the named group on the Change Group Options Specified Order tab and re-added. I am not sure if it is a case sensitive situation or what but the error is now gone. Couldn't have solved this without @JWiseman's help. Thanks much.