I have a report that has two parameters - The user selects an event that was held at a point in time and then an event that is being held at a later date. The TSQL query then chooses the companies that have been sponsors at either event and returns the information for that sponsorship. The sponsors at the older event should all be designated as Base Year and then the companies for the more recent event should be examined and then categorized based on whether they are a repeat sponsor from the initial event or whether they sponsored only the later event. I have attempted a formula to make that designation, but the count function counts the total number of companies rather than just the company I am trying to assess. The companies are identified by the ProfileID data item.


Local NumberVar CheckID :={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 ' '

Can anyone help me over this hump? Thanks in advance for the help!

Thanks for the reply -
I want to group by event and not the company unless I have to do so in order to get the results I want. Below is the TSQL command file that selects the initial data set for the report. I then have two parameters to identify the two events I want to analyze and then use them in a record select to narrow the results. I do not have the parameters at the command file level as I want to present the user with a dynamic list of events form the DB to use in making their selection -
SELECT

InvoiceLineItem.InvoiceNum
,InvoiceLineItem.Item
,InvoiceLineItem.ItemNum
,InvoiceLineItem.Amount
,InvoiceLineItem.EventSignUpID
,EventSignUps.ProfileID
,Profile.ReportName
,EventList.EventName
,EventList.EventId
,InvoiceLineItem.Descr
,(CASE
WHEN Adjustments.AdjustmentAmount IS NULL
THEN 0
ELSE Adjustments.AdjustmentAmount
END) AS Adjustment


FROM InvoiceLineItem

LEFT JOIN EventSignUps ON InvoiceLineItem.EventSignUpID=EventSignUps.EventSignUpID
LEFT JOIN EventList ON EventSignUps.EventID=EventList.EventId
LEFT JOIN Profile on EventSignUps.ProfileID=Profile.ProfileId
LEFT JOIN Adjustments ON InvoiceLineItem.InvoiceNum+InvoiceLineItem.LineItemID=Adjustments.InvoiceNum+Adjustments.InvoiceLineItemID

WHERE

InvoiceLineItem.Item LIKE 'EV_%'
AND (InvoiceLineItem.Descr LIKE '%sponsor%'
OR InvoiceLineItem.Descr LIKE '%exhibit%' OR
InvoiceLineItem.Descr LIKE '%contribut%')
AND EventList.EventDate>='01/01/2010'

ORDER BY EventList.EventID