add summary field for a bunch of running totals

[scottaherbst]

I'm working in CR 10, SQL server DB. I've got a report that returns service records for clients. The details look roughly like this

Client Location Date
11111 XXX 9/7/06
11111 XXX 1/2/05
11111 YYY 11/5/05
22222 XXX 1/1/06
22222 YYY 2/5/05
22222 ZZZ 3/11/06

I've then got running totals at the Client group level that count how many services they got at each location. I've suppressed the details so the report looks like this

Client xxx yyy zzz
11111 2 1
22222 1 1 1

What I'm looking to do is to add a column at the end that will tell me at how many locations the client received services. So that it will look like this:

Client xxx yyy zzz Total Locations
11111 2 1 2
22222 1 1 1 3

The obvious and easy answer is to insert a summary at the Client group level and do a distinct count of locations. I've done that and I get the data I need, the only thing is I would ultimately like to order the entire report by the number of different locations where clients have recieved services (there can be up to 16). Any ideas how I could go about that?

 

[scottaherbst]

Nevermind. Just figured it out. Apologies (and thanks) to all who took the time to read my post.

 

[lbass]

Please explain your solution--otherwise the thread is meaningless to other readers.

-LB

 

[scottaherbst]

Sure. I'll get to a full response on Monday. I'm not in the office today and not by crystal reports. In the mean time, there is a button on the toolbar - something along the lines of "order by" or "ordering expert" Once in there, it was just a matter of pulling down the field I wanted to records to be ordered by.

 

[scottaherbst]

The solution was really easy. I added a summary field that gave me a distinct count of the places where services had been delivered and then used the "Group Sort Expert" on the toolbar. Then I sorted the group by client id on the summary field.