This is a long post.....sorry...but i needed to explain things so u don't think i just fell off the tomato  truck...

Normal 0 false false false MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;}

Background info over the report and why I need to do what I am doing:

I am trying to make a custom report for Outstanding $$$ for events.

For each participant I would need Total Reg Fees, Total Amount Paid and the Balance.

I must consider the following:

A constituent may have multiple registration fees and multiple gifts.

Therefore, typically, the formula would be sum(regfees) - sum(gifts) = balance

This next criteria is the killer...

I must backdate this report to any date specified by the users. For example...who was outstanding as of Feb 28, 2009.

To find the correct outstanding balance for a previous date, I need to eliminate any transaction greater than the date parameter supplied by the user.

Therefore I must backdate both the registration fees and the gifts.

Although I cannot make it a required field, I am asking all users who enter registration fees to enter the date on each registration line item to know when it happened.

Ex: Someone can register for $40 on Jan 15 and call back Feb 5 and say they will bring a guest for $60. If they never paid anything, they would be outstanding only $40 on Jan 31 compared to $100 on Feb 28.

Because we have multiple gifts, and I need to backdate, I cannot look at the applied amount. I must look at the gift amount and compare it to the date in question. When there are multiple gifts involved, there is no applied amount date since the amount now spans over 2 dates.

This caused another issue where we can no longer have a gift pay for more than one registration because if I look at the gift amount itself, I can get the wrong amount applied. I've fixed this by telling the users they can no longer link a gift to multiple event registration Fees. The registration fees must go under the same constituent who paid gave the gift, otherwise I cannot calculate the balance.

I hope I am clear so far.....

So…after struggling with your queries for a while….it became clear that I could not filter it well in Raisers Edge, so I basically exported all registration fees and gift info for all participants who registered for the event.

I’m doing everything else in crystal reports 11.

My Issue in crystal reports.

I have been able to build a back date-able event outstanding report. My only real problem now is that I cannot figure a way to show outstanding only, or in other words where the balance > 0

The report consists of 2 sub reports. One for registration fees, and one for gifts. Each sub report has a date parameter within in it that the user is prompted for which allows me to compare the dates. I use shared variables to pass the sum total of the values back to the Main report. I am using shared variables in the a group header (sortkey) to show these values…which is basically sum(regFees) and sum(gifts) from the sub-reports. I use a formula to calculate the balance for each constituent using these “shared variables”

I discovered at this point that I cannot have summarized totals for formulas that consist of “while printing records”…., so I created formula variables to kept track of totals and reset formulas and in the end was able to total everything up okay. Many of these variables have to be calculated on the 2^nd pass of crystal reports calculations…and therefore cannot be included in summarization formulas.

My problem is this: I cannot set a filter on the @balance field that is calculating the outstanding balance for each constituent. Again…the calculation for this @balance is made up of 2 variable fields being passed from both sub reports.

Any advise would be great as I’ve spent weeks on this and have almost given up.

What I really need is control in the back end.  I’ve done this same report in our old database, but was able to create 3 queries. One to sum up the backdated RegFees, one to sum up the backdated Gifts, and then JOIN them for the report. In SQL this can easily be done with 3 views but I’m apparently not allowed to create views in Raisers Edge database. Raisers edge query cannot handle this task.

The only other option I have is to do this in access. Export the data to access, create my backdated queries there, and either use a report from access or crystal to report on my new query in access.

However, each time I export I would overwrite the queries. Also…you export in a 1995 access format which is incompatible with office 2003 and needs to be converted each time I open it. I’ve considered making another access file, use a linked table to the exported access file and do it there, but this is getting ridiculous and performance issues.

This is getting very frustrating. I feel that Raisers Edge was a big let down in many ways and I’m battling many issues that I shouldn’t have to.

Do you have any thoughts or ideas on how I could do what I’m trying to accomplish, even if this means rebuilding the report from scratch a different way.

Any advice would be helpful.