如何在Crystal Reports中检测会员续期状态并生成未续期会员列表

Alright, let's walk through exactly how to build this in Crystal Reports without touching Excel—since you already know the Excel logic, we'll map that directly to Crystal's tools in a single workflow.

1. First, Get Your Tables Linked Correctly #

First up, make sure your data sources are connected properly so Crystal can check for renewals and qualifications:

• Link your main member table (let's call it Members) to your renewal records table (Renewals) using the Individual Reference field with a left outer join—this ensures we don't drop members who have no renewal entries (which is exactly who we need to flag).
• If your qualifications are stored in a separate table (Qualifications), link it too: use Individual Reference to tie it to the member/renewal tables, and make sure you can filter by Qualification Name for your required checks.

2. Create a Calculated Field to Determine Member Status #

Next, we'll build a calculated field (name it something like @Member Status) that replicates the logic you used with Excel's COUNTIFS and IF functions. This will handle all the status checks in one place:

Here's the Crystal syntax code for the field (adjust the table/field names and qualification list to match your data):

// Grab the current date (or use a parameter if you want to specify a custom date)
Local DateTimeVar checkDate := CurrentDateTime;

// Check if there's a valid renewal: matching individual reference, correct qualification, and renewal isn't expired
Local BooleanVar hasValidRenewal := 
    Not IsNull({Renewals.Individual Reference}) 
    And {Renewals.Qualification Name} In ["First Aid", "CPR"] // Replace with your target qualifications
    And {Renewals.End Date} >= checkDate;

// Determine the final status
If hasValidRenewal Then
    "Current"
Else If {Members.End Date} < checkDate Then
    "Expired (Not Renewed)"
Else
    "Current (Active)"

3. Filter for Unrenewed Members & Build Your List #

Now we'll narrow down the report to only show the members we care about:

• Open the Select Expert (right-click the report background > Select Expert > Record).
• Add a condition: {@Member Status} = "Expired (Not Renewed)".
• Hit OK to apply the filter.

Finally, arrange your report layout to display the relevant member details (like Individual Reference, full name, original end date, qualifications, etc.)—that's your unrenewed member list, generated in one single Crystal Reports workflow.

Quick Pro Tips #

• Double-check that all date fields are formatted as date/time types in Crystal—mismatched types can break the expiration checks.
• If you need to let users pick the check date instead of using today's date, replace CurrentDateTime with a date parameter (go to Field Explorer > Parameters > New).
• Use the In operator for multiple qualifications instead of stacking Or conditions—it's cleaner and easier to update later.

内容的提问来源于stack exchange，提问作者Sparkind