• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 533
  • Last Modified:

Maximum calculations for every record

For Crystal Reports version 8.5, I need a report for last year's amounts and this year's amounts.  My raw data looks like:  fund-type, fund-id, customer-id, check amount, check date, last year's beginning date, last year's ending date, this year's beginning date, and this year's ending date.  The beginning and ending dates are subject to change.  Also, out of the 2,000 records in the raw data, about 5 of the records have the beginning and ending dates attached.  I have used the summary calculation of Maximum to find the beginning and ending dates.  My problem is that I cannot use the Maximum function in the formula fields to determine if a check is for last year or this year.  I also need totals for a customer's checks (last year and this year), fund-id amounts (this year and last year), and fund-type (last year and this year).  When I try to create running-totals, I receive the error message that the summary/running totals cannot be created.  Is there a way to create this report?
Thank you for any and all help and advice,
TracySHC
0
TracySHC
Asked:
TracySHC
  • 12
  • 12
  • 2
  • +1
3 Solutions
 
wykabryanCommented:
Is there a way to tell the difference in the records to tell the difference between last year and this year? And I am assuming this is all calendar years, and not fiscal year?

Something like this should work:

GH1 - Customer - Suppress
GF1a - Customer - {Running Total for Check Amounts this year}
GF1b - Customer - {Running Total for Check Amounts last year}

Running Total - for last year <- Place this in GF1b
>Field to Summarize - Check Amount
>Type of Summary - Sum
>Evaluate
   Use a formula - year(Datetime) = year(dateadd("d", -365,currentdate))
>Reset
  On Change of Group - GH (Customer)

Running Total - for this year <- Place this in GF1a
>Field to Summarize - Check Amount
>Type of Summary - Sum
>Evaluate
   Use a formula - year(Datetime) = year(currentdate)
>Reset
  On Change of Group - GH (Customer)



Hope this helps..
0
 
TracySHCAuthor Commented:
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
0
 
wykabryanCommented:
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.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
TracySHCAuthor Commented:
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

0
 
frodomanCommented:
>>> 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
0
 
TracySHCAuthor Commented:
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
0
 
frodomanCommented:
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
0
 
TracySHCAuthor Commented:
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
0
 
frodomanCommented:
>>> 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.

0
 
TracySHCAuthor Commented:
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
0
 
frodomanCommented:
What do you have in the formula?
0
 
TracySHCAuthor Commented:
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
0
 
frodomanCommented:
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?
0
 
TracySHCAuthor Commented:
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
0
 
mlmccCommented:
Yes it could.

You probably need to change your formulas to account for nulls

shared dateVar LastYearBegin ;
shared dateVar  LastYearEnd ;

if (IsNull({lastyearbeginningdate})) then
 LastYearBegin :=  Date(2006,1,1)
else
 LastYearBegin := Maximum(DateValue ({lastyearbeginningdate}));

if  (IsNull({lastyearendingdate})) then
  lastyearendingdate := Date(2006,12,31)
else
   LastYearEnd := Maximum(DateValue ({lastyearendingdate}));

mlmcc
0
 
frodomanCommented:
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.
0
 
TracySHCAuthor Commented:
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
0
 
frodomanCommented:
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;
0
 
TracySHCAuthor Commented:
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.
0
 
frodomanCommented:
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.
0
 
TracySHCAuthor Commented:
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

0
 
frodomanCommented:
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!
0
 
TracySHCAuthor Commented:
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
0
 
frodomanCommented:
Sorry I couldn't resolve it cleanly for you - Good luck though!
0
 
frodomanCommented:
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.
0
 
TracySHCAuthor Commented:
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
0
 
frodomanCommented:
Thanks TracySHC.  You can actually do this yourself and split the points however you wish.  If you need instructions for doing that you can find them here: http://www.experts-exchange.com/help.jsp#hi19

frodoman
0
 
mlmccCommented:
Glad i could help

mlmcc
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 12
  • 12
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now