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

knausscpa
knausscpa asked
on
Medium Priority
803 Views
Last Modified: 2012-05-06
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

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

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

Ask the Experts

Author

Commented:
What about Anything < Today ?

Author

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.
Mike McCrackenSenior 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

Author

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?
Mike McCrackenSenior 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

Author

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.
Mike McCrackenSenior 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

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

Use that in the summary function.

mlmcc

Author

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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

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

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.