We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

# Crystal Reports Conditional Sum and Highlighting

on
Medium Priority
803 Views
I am trying to create a conditional summation to show the total amount in dollars of shipped but not billed orders.

Basically I need to
Sum Table1.Estimate IF Table2.StationName is between "Shipped: Fedex" and "Shipped: UPS"

Additionally I need some help with the highlighting expert where I want to Highlight...
Color 1: Table.dateexpected IF Date is older than 1 week.
Color 2: Table.dateexpected IF Date is between 1 week and yesterday.
Color 2: Table dateexpected  IF Date is between Tomorrow and Today etc.

With those 3 examples I think i can manipulate enough to get what I want done with various highlighting.
Comment
Watch Question

## View Solutions Only

Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
In the formula button on the highlight

crSilver
else if {YourDateField} IN Last7Days then
crBlue
else if DateAdd('d',1,CurrentDate) >= {YourDateField} then
crRed
else
crWhite

mlmcc

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

Commented:
What about Anything < Today ?

Commented:
Also what about Last 4 days would it be...

crRed

Trying to get the hang of this.
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
If DateAdd('w',-1,CurrentDate) > {YourDateField} then  // More than 1 week ago
crSilver
else if {YourDateField} IN Last7Days then                      // last week
crBlue
else if DateAdd('d',1,CurrentDate) >= {YourDateField} then  Next 2 days
crRed
else
crWhite

Last 4 days
if DateAdd('d',-4,CurrentDate) <= {Newtrack.Date} AND {Newtrack.Date} <= CurrentDate then
crRed

mlmcc

Commented:
Everything so far is working well, only question when using RGB (255, 0, 255) is there a way to code in Lum in there?
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
I don't think so.  What I generally do is to use the color pallette, create the color I want then use the numbers it has for RGB

mlmcc

Commented:
Excellent work so far, last question is for summation formulas. I have 2 problems with my summation.  One, my report is running with everything under group header, and it works great but, when i do the insert summation and try and sum totals they are showing up as double or 2.2x actual values. I don't think the summation is taking into account filtering.  I figure I have to write the summation formula but couldn't really get it working.  Also in my original question i need to sum Table1.FieldA IF table2.field B is between x and x values (word values).. like

Sum(table1.fieldA) IF (Table2.FieldB = "Fedex" OR "UPS" OR "DHL")

Let me know if you think you can tackle this otherwise i can make a new question.
Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Yes, Summary fields include all data that is fed to the report for tha tgroup.  If you are suppressing fields or if records are duplicated.

Try this for what you want to sum

If ({Table2.FieldB} IN ["Fedex" , "UPS" , "DHL") then
{table1.fieldA}
else
0

Use that in the summary function.

mlmcc

Commented:
Ill give that a try may open a 2nd question for that stuff, last formula i think i need is in regards to time.  I was trying to use the

Not sure if it matters but there are 2 fields NewTrack.Date and NewTrack.time Date holds only the date and Time holds only the time (24 hour clock) What i am trying to do is add an additional highlighting to show if there has been activity within the last hour or 2 hours or 6 hours etc. I just need last hour for now but that might change.
##### 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