Maximum calculations for every record

wykabryan,
Unfortunately, there's no way on the record level to tell which year a check's been received.  I need to do a maximum on the beginning and ending dates, then compare that to the check date.  My other problem is that the dates may not be a year-to-year check - they could be two months comparison.  Is there a way to work with the maximum summary calculation, or other options?
Thank you for your ideas,
TracySHC

I am sure there is a way.  I am drawing a blank, and without actually seeing the data it is very hard to imagine it.  I think you will find yourself using a combination of items to get this information. Such as stored procedures (because there is no set date and you need to compare), with some running totals and formulas in the report.

Sorry.

wykabryan,
Here is a sample of the data:

Total-Package, 1122334, 2233445, 788.34, 7/30/2004, , , ,
Partial-Package, 3344556, 123456, 566.78, 2/24/2005, 6/1/2004, 3/31/2005, 6/1/2005, 3/31/2006
Total-Package, 445562, 67890, 87.90, 12/25/2004, , , ,
Partial-Package, 4455622, 990890, 100.00, 1/30/2005

Does this help?

Thank you,
TracySHC

>>> I also need totals for a customer's checks (last year and this year)

Maybe I'm missing something, but can't you simply create a formula like this:

// @LastYearChecks

if {check_date} >= {last_year_begin_date} and {check_date} <= {last_year_end_date} then
   {check_amount}
else
   0;

Then you simply place the formula in your details and add a sum to subtotal it.  Similar formula for this year's checks and for all of the other items you want to total.  You can suppress these formula so they aren't visible and they'll still calculate your subtotals.

frodoman

frodoman,
My calculation for last_year_begin_date and last_year_end_date is a maximum summary calculation, since last year's beginning and ending date is not on every record.  The same is true for the current year's beginning and ending date.  If I use a maximum summary calculation, I am receiving the "A running total cannot refer to a print time formula" error.  Am I approaching these calculations wrong?  What do I seem to be missing?
Thank you for your response,
TracySHC

Well the problem you're running into is that the maximum keyword is resolved during the final pass of the report engine so it's impossible to base a running total off of any formula that involves it (because the running total would require another pass of the report engine to generate the values).

I understand now that these dates aren't on every record.  My recommendation would be to create a subreport in your report header that selects the maximum to get your dates and updates some shared variables.  Then you can use a formula similar to what I gave you.  I don't know your familiarity with Crystal so below is the basic concept and I can add more details / assistance if you need it.

// @InitVariables - Formula in Report Header a
shared dateVar LastYearStart;
shared dateVar LastYearEnd;
...etc...

// [SubReport] - Place in Report Header b
// Doesn't need to do anything except have the maximum formulas and assign them to shared variables.

// @AssignSharedVars - Formula in SubReport's Report Footer
shared dateVar LastYearStart := Maximum({last_year_begin_field});
shared dateVar LastYearEnd := Maximum({last_year_end_field});
...etc...

// @LastYearChecks - Formula in Details Section of Main Report
shared dateVar LastYearStart;
shared dateVar LastYearEnd;
if {check_date} >= LastYearStart and {check_date} <= LastYearEnd then
   {check_amount}
else
   0;

Now you can add your summary to the @LastYearChecks formula.  

If you don't understand any part of this just let me know.

frodoman

frodoman,
I am a little confused - Do I need to split my Report Header into a and b, or are you using two different reports?  Also, where do I type in the variables and code - the Formula Fields in the Field Object Explorer?  (Sorry, I think I may have hit my comfort levels in Crystal Reports.)
Thank you for your response,
TracySHC

>>> Do I need to split my Report Header into a and b, or are you using two different reports?

Split your report header.  Report -> Section Expert -> Report Header -> Insert.  This gives you 2 report headers to work with which is only important in this case to make sure everything is executed in the proper order.

>>> where do I type in the variables and code  - the Formula Fields in the Field Object Explorer?

Yes.  In the Field Object Explorer hit "Formula" and create a new one.  Type the code as I've shown and then drag to formula to the location indicated.

Basically what you're doing is creating "shared" variables (meaning they can be used by the main report and subreport) at the top of your report.  Then you have a subreport which is going to find the max values that you need and the subreport will put the max values into the shared variables.  Then the remainder of your main report uses the shared variables similar to how you were originally using the MAX function.

>>> I think I may have hit my comfort levels in Crystal Reports

No problem - just let me know if you're still stuck.

frodoman,
I'm having one more problem:  
When I create the formula in the Field Object Explorer, I'm receiving the error message "The summary / running total field could not be created."  This is in the sub-report for Report Header b, and while I'm assigning the maximum date values.  What am I doing wrong?
Thank you again for all of your help and advice,
TracySHC

What do you have in the formula?

frodoman,
This is the formula in the formula field AssignLastYear that I placed in the subreport's report footer:

shared dateVar LastYearBegin := Maximum(DateValue ({lastyearbeginningdate}));
shared dateVar LastYearEnd := Maximum(DateValue ({lastyearendingdate}));

I did not create any links between the report and the subreport.  What did I do wrong?
Thank you,
TracySHC

I played around with it on my end and it looks to me like the DateValue is the problem - I can't get the formula to work with the DateValue keyword in there.  I'm guessing that  you included it because the date isn't already a "Date" data type?

frodoman,
I used DateValue because, without DateValue, I was receiving the error message that a date was needed.  When I browse the field, it's listed as a DateTime type.  Could the problem be that the unpopulated fields are blanks or nulls?
Thank you,
TracySHC

If the database is storing them as DateTime then change your variable type to DateTime.  So instead of:

  shared dateVar ...

Use this:

  shared dateTimeVar ...


I would make this change first and if that doesn't resolve the problem then we can deal with the IsNull checks - I don't believe that they interfere with the Maximum function but it is possible.

frodoman,
In using the dateTimeVar, I was able to insert the formula into the sub-report report footer.  No error messages, no problems.  However, when I entered the formula for the Details section of the Main report, I received the "A running total cannot refer to a print time formula."  The formula I used was:

shared dateVar LastYearStart;
shared dateVar LastYearEnd;
if {check_date} >= LastYearStart and {check_date} <= LastYearEnd then
   {check_amount}
else
   0;

What am I doing wrong?
Thank you again for your help, and so sorry for the inconvenience,
TracySHC

You aren't doing anything - it looks like the maximum in the subreport is causing a similar problem.  I'm thinking that you need to do this the hard way and skip the maximum function completely.  Basically this will just go through each record in the subreport and manually calculate the maximum instead of using the maximum function.  I think this will solve the issue but I don't have anything to test it with right now on my end.

// @InitVariables - Formula in Report Header a   <--- Formula Changed
shared dateTimeVar LastYearStart := Date(1900, 1, 1, 0, 0, 0);
shared dateTimeVar LastYearEnd := Date(1900, 1, 1, 0, 0, 0);
...etc...

// [SubReport] - Place in Report Header b
// Doesn't need to do anything except have the maximum formulas and assign them to shared variables.

// @AssignSharedVars - Formula in SubReport's DETAIL Section <--- Formula and location changed
shared dateVar LastYearStart;
shared dateVar LastYearEnd;
if not( isNull ( {last_year_begin_field} ) ) then
(
   if {last_year_begin_field} > LastYearStart then
      LastYearStart := {last_year_begin_field}
);
if not( isNull ( {last_year_end_field} ) ) then
(
   if {last_year_end_field} > LastYearEnd then
      LastYearEnd := {last_year_end_field}
);

...etc...

// @LastYearChecks - Formula in Details Section of Main Report
shared dateVar LastYearStart;
shared dateVar LastYearEnd;
if {check_date} >= LastYearStart and {check_date} <= LastYearEnd then
   {check_amount}
else
   0;

frodoman,
Thanks to your help, I've created the formulas and moved them into the different sections of the main report and sub-report.  I'm trying to create totals.  I tried the following formula in the Formula Editor, and received the "The summary / running total field could not be created.":

DistinctCount ({@LastYearCustomerr})

I had created LastYearCustomer with the following formula:

// @LastYearCustomer - Formula in Details Section of Main Report
shared dateTimeVar LastYearStart;
shared dateTimeVar LastYearEnd;
if {check_date} >= LastYearStart and {check_date} <= LastYearEnd then
   {customer_id}
else
   0;

Should I be using Running Totals?  I tried, but could not find the LastYearCustomer field.

Thank you for your help,
TracySHC

p.s.  Are you okay?  I noticed the " I don't have anything to test it with right now on my end" statement, and was concerned.

You won't be able to create a distinct count function based on this formula - neither will you be able to create a running total.  The problem is that the report rendering engine uses 3 passes to calculate everything on the report - You are trying to do a formula that can't be resolved until another formula is resolved and that one can't be resolved until another one is resolved, etc.  It's simply a case of too many layers.

Let's take a fresh look at the problem.  Do you have the ability to create a view on your database?  If so, you can probably structure a view that will handle most of this work and remove a lot of the complexity layers from Crystal.  If you had Crystal 9+ we could write some SQL directly that would help but that option isn't available in 8.5 so that type of work needs to be done on the database.

Thanks for your concern - I'm fine, just working on a machine today that doesn't have Crystal Reports so I can't test my syntax, etc. and am working from memory.

frodoman,
Ouch.  I think I'll need to end this support call.  My problem is that I am working with a "crippled" version of Crystal Reports 8.5.  I was planning to use prompts instead of the maximum functions, but the prompts do not work when Crystal is integrated into another piece of software.  I also do not have access to the database, and am working from some information exported from the database.  Finally, I do not have access to the SQL statements - either due to the crippling of the software or from the version.  For now, my best option is to run Crystal Reports without the software integration, and have the users force the prompts.  Thank you for patience and time with this problem (and the use of your memory).  If you can think of other options, I am very willing to consider them.  
As always - thank you,
TracySHC

Any chance of changing the export?  If you could get the date fields added to all of the records that alone would tremendously streamline the report.

I almost hate to even suggest it, but as a last resort you could have an intermediate step if there's no other choice.  Take the exported data and pull it into another database and run a procedure to standardize it.  I cringe at even saying it, but I have one piece of software where I work that uses such a cludged up DB that I have to extract the data and run it through a DTS job to make it into something useful...

Otherwise, I don't have much else I can suggestion - sorry!

frodoman,
Thanks for the idea.  It's not optimal, but we had considered using Access to develop the report initially.  In a worst case scenario, we could use Access to clean up the data.
Thanks again for your help and ideas,
TracySHC

Sorry I couldn't resolve it cleanly for you - Good luck though!

Recommend split wykabryan, mlmcc, frodoman.

With the data available the asker would never be able to satisfactorily resolve this issue but I believe each of us helped to explain the 'why' of it.

If it's possible, I would like for the majority of the points to be awarded to frodoman - say, 60 - 75%.  He worked the most on this problem.  The remaining points can be split between wykabryan and mlmcc.  They were also helpful.
Thank you again for your help and advice,
TracySHC

Glad i could help

mlmcc