Formula using multiple records 1

[timc63]

Is there a way to create a formula field using multiple records in the formula. I have tried everything but can not manipulate the data the way that I need to. Here is my situation. I have three records, all with the same project # but with different versions (0, 15, 99). I want to create a formula using the finish date field of each version.

Project Version Finish Date
x 0 1/1/00
x 15 2/1/00
x 99 3/1/00

I want to create a formula field that gives me the difference between project version Finish dates:

Diff between 0 and 99 = 1/1/00 - 3/1/00
Diff between 15 and 99 = 2/1/00 - 3/1/00

I can do this by pulling the data into Excel and then pivoting the data so that I have a project with each versions date

Project 0 15 99
X 1/1/00 2/1/00 3/1/00
y 9/1/00 3/1/00 4/1/00
z 10/1/00 11/1/00 2/1/00

and then pulling the pivot into Access so that I can get to the pivot from crystal, but this is VERY messy and will create a nightmare in keeping the data up to date. I really want to pull the data in from the database directly using an SQL Statement and then create the formula fields above.

Thanks in advance for any help you can give me.

Tim Cason
651-582-5047

 

[alam]

Hello Tim..,
Firstly u haven't written which version of Crystal r u using.., if u have 6.0 and above then try to group it on basis of Version and might solve ur problem.., this might hep u...jus go to INSERT..GROUP and choose version from dropdown list....,
hope it will help u..or i might have gone wrong in understanding ur problem...

 

[timc63]

I am using version 7.0.

Unfortunately the Grouping process does not help me when creating a formula. I want to subtract the Date in one record from the date in another record. It seems that you can only perform formulas within one record.

I was able to do a running total using the version as the condition to set the date to a running total field and then create a formula to subtract one running total from another running total, but, the next step I need to do is to graph the result of this subtraction. When I try to do that the tool comes back and says that I cannot graph a running total with side effects....whatever that means.

Anyone else have any ideas?

Thanks,

tim

 

[MalcolmW]

If you are comfortable with SQL, you could base it on a Crystal Query or even just write a view/stored proc to generate the result set with the project finish date for the final version with each record.
Another way to do it is write another report that gets just the finish date for the final version, then install it as a subreport in the project group header, linking it to the main report on project id, and passing the finish date as a shared variable to a formula in group footer. But for my tastes, that is too slow and quirky, and I would recommend tried and true SQL. Malcolm
Remember, if it wasn't for electricity, we'd be surfing the net by candlelight.