We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Maximum calculations for every record

on
Medium Priority
555 Views
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
Comment
Watch Question

View Solutions Only

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

Not the solution you were looking for? Getting a personalized solution is easy.

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?
TracySHC

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

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

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

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?
TracySHC

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

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

frodoman

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.)
TracySHC

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

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?
TracySHC

Commented:
What do you have in the formula?

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

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

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
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
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

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

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

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

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.

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.

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

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

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

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

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

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

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.
TracySHC
Commented:
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
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:

mlmcc
Thanks for using Experts Exchange.

• View three pieces of content (articles, solutions, posts, and videos)
• Ask the experts questions (counted toward content limit)
• Customize your dashboard and profile