A deceptive difficult report

[Fonz]

Hi__I am using R & R SQL V8.1 connected to Pervasive-Btrieve data.__When I received a request from a User for this report, I believed it to be as "easy as pie". Many hours later, I haven^t got the solution!____The report draws data from only one table which has the following fields: __CompanyCode(4 chars), Effective Date(dd/mm/yyyy), AgentCode (6 chars). ____Each Company can have one or more Effective Dates with the same or more than one Agent Code. Sample data as follows:____A374, 13/12/2001, 30658__A374,15/01/2002, 30658__A374,22/01/2003,70650__Z999,12/12/2002,30658__X112,01/01/2003,66995__Y555,12/08/2002,70650__Y555,13/09/2003,66995__Y555,13/10/2003,30658____Key of this file is Company Code & Effective Date____The data represents the date a particular Agent has an agreement with the company. Only the LAST EFFECTIVE DATE for a Company, is the Current Agreement.. in other words Company A374 has one current agreement and that is with agent 70650.. all the other agreements for company A374, are NON current but they are not marked as such in the data. (The data is from a proprietory software in the Financial Industry)____The required solution:__I want to be able to prompt the User with a Filter box to enter the required Agent, the data returned MUST be all company codes where the agent has a current agreement. This means that if I select ____Agent 30658 Agent 70650__CORRECT Report should show __Company Code Effective Date Company Code Effective Date__Z999 12/12/2002 A374 22/01/2003 __Y555 13/10/2003____INCORRECT REPORT shows:__A374 15/01/2002 ** Y555 12/08/2002__Z999 12/12/2002 A374 22/01/2003 Y555 13/10/2003__** A374 & Y555 are wrong because they are NOT the current record.____Please help Kathleen__Thanks a million__Mark Fonseca

[kfleming]

Hi Mark-__Hope that all is well down under!____The simplest way to get there is to sort the report by AgentCode, then CompanyCode and then the EffectiveDate. Then your fields for the report would be placed on a CompanyCode group footer band. What will then happen is that the most recent EffectiveDate for each company will display because of the third level sort.____Hope this helps.____Kathleen____

[Fonz]

Hi Kathleen__You must remember me from the ^Concentric^ days. Yes everything is great down under. You are not planning any trips down here, are you?____Thanks for your response, however it didn^t work as the solution (which is sorted by Agent)diregarded that I only want to show a company record if and only if it is the last record (not for that agent but overall). The solution showed :__Agent: 30658 __Company A374 15/1/2002____Agent : 70650 __Company A374 22/1/2003__This is incorrect as Company A374 has only ONE current agreement and that is with Agent 70650 dated 22/1/2003____Besides when you set a filter for A374, the recordet contains ALL records for say Agent 30658, and from that, it will display company records irrespective of whether they are current or not.____A Business Associate of mine showed me a solution: __1 He created a calculated field called ^Key^ which was CompanCode+DTOC(Date)+AgentCode. __2 Next he created a Totals field called MaxAgent which was the Maximum of ^Key^ reset for each Fund / Pre processed. __3. Created a Calc field = Agt which was Right(MaxAgent,5)__4. Set a filter as follows:__Key equal to MaxAgent__and (Agt equal to ^70650^__or Agt equal to ^30658^)____This provided the EXACT solution!__Agent 30658__ Company Z999__ Y555____Agent: 70650__ Company: A374____If you have a simpler solution I would be pleased to hear it.__Thanks a million for your help to date.__Cheers__ __

[kfleming]

The pre-processed total route is indeed clever. I may not have looked at your question in enough detail to give you a good answer the first time around.____Australia is indeed on my agenda for 2004 if all goes well.____Kathleen__R&R Support