CODEWITHSUNDEEP
sqlcrystal-reportsdistinct
Proviance
Proviance

Reputation: 59

Is there a way to distinct more than 1 field

I need a report that has office, date and order count. I need the total count of orders per month, but only 1 order count per day.

e.g.

West 1/1/2009 1 order

West 1/1/2009 1 order

West 1/2/2009 1 order

on my report I would see

West 1/1/2009 1 order

West 1/2/2009 1 order

and my total orders would be 2.

This would be really easy with SQL, I know, but I do not have access.

Upvotes: 1

Views: 1274

Answers (2)

SqlRyan
SqlRyan

Reputation: 33914

Are you just looking for this?

SELECT DISTINCT Office, Date, OrderCount FROM YourTable

This would duplicate your results, but the data set is too small to know for sure if this is what you're trying to accomplish. Using the DISTINCT clause would return only unique combinations of Office, Date, and OrderCount - in this case, one line per day/office.

UPDATE: Ah - I didn't read the part where you don't have SQL access. You still have two choices:

  1. In Crystal Reports Designer, in the "Database" menu, check the "Select Distinct Records" option at the bottom of the menu.
  2. Edit the SQL query directly - Database menu -> Database Expert -> Under "Current Connections", click "Add new command" and type your SQL command. Modify the one I provided above to meet your needs, and it should do the trick.

Upvotes: 2

Dusty
Dusty

Reputation: 4697

You can create three groups, one for office, one for date, and one for order. Then put the fields in the day group footer and suppress the other sections. This will cause the report to show a new section for each day, but only show one row for each order. Then you can add your running total to the section. Set the running total up to sum the field you want, evaluate on change of day group and then reset on change of month (you'll need to set a formula up for this one to evaluate the month).

This should group and order the report like you are looking for and will have a running total that will run along side which will reset per month. Hope this helps.

Upvotes: 1

Related Questions