Identify Multiple Occurrences of a Value in Crystal Formula

30 pts.
Tags:
Crystal
Crystal Objects
I have a Crystal report that pulls the name and company ID for sponsors of two events. The first event is the oldest in terms of time frame and the second will be more recent. I want to take the listing of companies that sponsored the more recent event and then check to see if they sponsored the older event and then classify them as NEW or REPEAT sponsors in the newer event. I am pulling the list of sponsors for all events from an SQL based Command file and then using two parameters, the event ID for both the older and newer to be my record select and narrow my return set down to the two events. In the older event, they are all classified as BASE YEAR and in the later year, I need to designate them as either NEW or REPEAT based on their being, or not being, listed in the base year. I am trying to create a formula value called Occurrence and I have the following formula that is not getting what I want - This Count syntax does not work. Anyone have any ideas? Thanks in advance for the help!
 Local NumberVar Check :={Command_1.ProfileID};
 IF ({Command_1.EventId}={?ComparisonEventSelect} AND COUNT({Command_1.ProfileID})=1) THEN 'NEW' 
ELSE IF ({Command_1.EventId}={?ComparisonEventSelect} AND COUNT({Command_1.ProfileID})>1) THEN 'REPEAT'
ELSE IF {Command_1.EventId}={?Event Base Select} THEN 'BASE YEAR'
 ELSE ' ' 

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    I would think that "COUNT({Command_1.ProfileID})" is counting any record that has some value (i.e. is not null) in ProfileID.

    If that is true, instead of Command_1.ProfileID you would need a subformula that evaluated to null for any record that doesn't belong to the ProfileID you are currently evaluating.
    68,650 pointsBadges:
    report
  • randybbradley

    You are correct - It counts the total number.

    Thanks for the direction - Can you give me any thoughts on syntax for the sub-formula?

    30 pointsBadges:
    report
  • carlosdl
    It's been many, many years since the last time I used Crystal Reports, and unfortunately I don't remember the syntax and don't have an installation to try.

    But, I believe the same result could be achieved by modifying the SQL query.  Would that be an option for you?
    68,650 pointsBadges:
    report
  • randybbradley
    I could feed the parameters into the TSQL query in the command file, but I lose the functionality of the dynamic pick list of events fed from the DB from which the user picks the two events to be analyzed. To the best of my knowledge, I can only have that functionality in the "upper level" parameter, not in one created at the command file level. May have to drop back to that approach if I cannot figure how to accomplish this.
    30 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following