Fields:

CustomerID

CustomerLocation (US or Canada)

 

Table: Transactions

Fields:

TransactionID

TransactionDate

CustomerID

TransactionAmount

ProductCategory

 

Sales department wants to set up a report that runs each morning and produces a report of total sales of the previous day by product category and customer location.  (It's an assignment, not my specs.)

 

Fields:

TransactionDate (group by)

ProductCategory (group by)

Sum of TransactionAmount where CustomerLocation = 'US'

Sum of TransactionAmount where CustomerLocation = 'Canada'

 

I could write this in SQL in a minute, but I am fairly new to Crystal.  I tried adding a Command to via Database Expert for each location - wrote the SQL and dragged the fields, but, despite having where statements for CustomerLocation in my Commands, both fields are coming up with the total Sum of TransactionAmount by ProductCategory and Date. 

 

I've been flipping through Crystal Reports 2008: The Complete Reference to try to figure out how to do this.  I'm sure there is an easier way with Crystal formulas.

 

Thanks for any help.  I am going to a couple Crystal training session in the next month - hopefull I'll be able to start answering questions someday.