Cal. Field w/ Diff. Query in Report

[hhugh]

I am using Xbase Edition, Version 11, Build 11.1.007. I am trying to use it with a application created in Alpha 5 Version 5.____The application keeps track of our fundraising efforts and the report I am trying to perfect is based on the funds we have raised this year through our one major event. The information regarding this event (number of tickets, amount donated, etc.) is kept in a seperate child table in Alpha and is a related table in R&R. An individual or organization (whose basic information -- address, etc. -- is kept in the master table) can have several records in the related table for each year they were involved in the event (i.e. one to many relationship). There is a field called FMonYear in the related table for the year of the event.____Each individual/organization who buys tickets, donates, or buys ad space that year is included in the report. Therefore, the query for the report is: FMonYear = "2006" and (Paidtix > 0 or DonAmt >0 or AdAmt>0). For each record there is also calculated field with the total amount they spent (on donations, tickets, and ads).____What I am having problems figuring out how to create a calculated field that shows the total amount spend LAST YEAR and put it in the report next to the total amount this year for comparison purposes. Is there a function that can do this? I have looked at the NLOOKUP function but what I need is not found in a separate table, rather in the same related table but with a different filter than the one in the report (i.e. I need FMonYear to equal "2005"). I have seen a function like this in other report writers. If there isn^t a function that can helped me, would I be able to create a user-defined function to do this (I have never done this before)? Or is there some other way to accomplish what I need?____Any help would be very much appreciated. Thanks in advance.

[Alan_Klein]

How about if you extend the query to include 2005 and make two calculated fields, one for each year which includes an IIF that checks the year and only goes on if the record level field it is adding is in that year?

[kfleming]

A query for both years along with conditional totals for each year and a logical calc for the record band line to only show 2006 would work I think.____Kathleen__R&R Support

[hhugh]

Thank you two for your responses.____I implemented your suggestions. For the report query, I included both years. I changed the calculated fields to be conditional for the appropriate year. I put the previous year^s total calculated field in the band line right below the total calculated field for this year. Then, for all the band lines -- except for the one with the previous year^s total in it -- I put in a logical calc. under condition to show only this year^s records. For the band line with last year^s total I put in a logical calc. for last year.____The records for this year come out as they are supposed to. However, the total field for last year does not work properly. As best as I can tell, last year^s total is printing ABOVE the record information (i.e. it is printing above the name of the individual or organization). In addition, it seems like it is printing ALL the totals from last year, in order, whether or not they have a record from this year.____I thought maybe I could modify the logical calc. in the condition of the bandline, but to no avail. Can anyone help me?

[planbe]

Use a group footer and display the Maximum of each year^s field.

[ShaBoub]

I have not been able to figure out what you mean exactly. Could you give me some more help.

[Solaris_(Guest)]

I assume that in the Master file there is a unique code field for the different organisations. In the child table there is that code, (which I an guessing is the link between parent and child) plus in the child table, a year of donation and then fields which represent the donation types.____Create a level 1 group based on the organisation identifier. Create a level 2 group based on the donation year. Create a GF2 on the report, and place the totals there. Within the total formula you can select a reset based on level 2 "YEAR" grouping.__

[Aliciakic]

Thanks for all your help. Unfortunately, I still have not been able to figure it out, probably because I am not good with grouping, etc.____Can anyone offer any other suggestions? Or is there any documentation or books I could get that may be of any help? Does anyone think that the technical support line could be of any assistance? I am getting desperate, so any help would be much appreciated. Thanks in advance.

[kfleming]

If you want to email the report file to me at livesupport@livewarepub.com I will have a look at it.____Kathleen__R&R Support

[kfleming]

I just sent a revised copy of the report back to you.____What I did was query for 2006.____I then created a calc field with the expression RECORDID+"2005" and used it as the linking field in an exact lookup relation back to the same child table using Flexlink and the index expression RECORDID+FMONYEAR.____This lets us finding the matching 2005 record for the 2006 record that was found in the query.____Let me know if you have any questions.____Regards,__Kathleen__R&R Support