Cal. Field w/ Diff. Query in Report

Meeting place for R&R customers and clients to share tips and ideas. Post your questions, messages or problems here.
hhugh
Posts: 4
Joined: Tue Oct 10, 2017 12:44 pm

Cal. Field w/ Diff. Query in Report

Post by hhugh » Wed Aug 30, 2006 6:51 pm

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
Posts: 68
Joined: Tue Oct 10, 2017 12:44 pm

=> RE: Cal. Field w/ Diff. Query

Post by Alan_Klein » Thu Aug 31, 2006 11:25 am

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
Posts: 5795
Joined: Tue Oct 10, 2017 12:44 pm

==> RE: Cal. Field w/ Diff. Query

Post by kfleming » Thu Aug 31, 2006 12:14 pm

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
Posts: 4
Joined: Tue Oct 10, 2017 12:44 pm

===> RE: Cal. Field w/ Diff. Query

Post by hhugh » Thu Aug 31, 2006 6:15 pm

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
Posts: 8
Joined: Tue Oct 10, 2017 12:44 pm

====> RE: Cal. Field w/ Diff. Query

Post by planbe » Fri Sep 01, 2006 10:30 pm

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

ShaBoub
Posts: 0
Joined: Mon Apr 30, 2018 11:32 am

=====> RE: Cal. Field w/ Diff. Query

Post by ShaBoub » Sun Sep 24, 2006 3:59 pm

I have not been able to figure out what you mean exactly. Could you give me some more help.
Will Amoxicillin Heal Tooth Abscess Vet Keflex cialis for sale Antiphospholipid Syndrome Amoxicillin Cialis Soft Tabs De Amoxicillin Clav Side Affects

Solaris_(Guest)
Posts: 7
Joined: Tue Oct 10, 2017 12:44 pm

======> RE: Cal. Field w/ Diff. Query

Post by Solaris_(Guest) » Mon Sep 25, 2006 1:40 am

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
Posts: 0
Joined: Mon Apr 30, 2018 1:55 pm

=======> RE: Cal. Field w/ Diff. Query

Post by Aliciakic » Wed Dec 20, 2006 1:30 pm

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
Posts: 5795
Joined: Tue Oct 10, 2017 12:44 pm

========> RE: Cal. Field w/ Diff. Query

Post by kfleming » Thu Dec 21, 2006 11:09 am

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
Posts: 5795
Joined: Tue Oct 10, 2017 12:44 pm

=========> RE: Cal. Field w/ Diff. Query

Post by kfleming » Thu Dec 21, 2006 3:33 pm

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

Post Reply