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

Crystal Reports Conditional Sum and Highlighting

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.
0
knausscpa
Asked:
knausscpa
  • 5
  • 4
2 Solutions
 
mlmccCommented:
In the formula button on the highlight

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

mlmcc
0
 
knausscpaAuthor Commented:
What about Anything < Today ?
0
 
knausscpaAuthor Commented:
Also what about Last 4 days would it be...

if DateAdd('d',-4,CurrentDate) >= {Newtrack.Date} then
   crRed

Trying to get the hang of this.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

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

0
 
knausscpaAuthor 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?
0
 
mlmccCommented:
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
0
 
knausscpaAuthor 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.
0
 
mlmccCommented:
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

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

Use that in the summary function.

mlmcc
0
 
knausscpaAuthor 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

 DateAdd function like this... If DateAdd('h',-1,CurrentTime) >= (NewTrack.Time) then RGB(255,20,20)

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.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now