CODEWITHSUNDEEP
sqlcrystal-reportscrystal-reports-2008
Hershizer33
Hershizer33

Reputation: 1246

Problems with a Sum in Crystal Reports 2008

my legacy stored procedure is bringing in the data like this:

Person ID | Location ID | Awesome Count (by Location ID)
1         | A           | 2
2         | A           | 2
3         | A           | 2
4         | B           | 3
5         | B           | 3
6         | B           | 3

So, since Awesome count is by Location ID, its repeated for each person in that location (A's actual awesome count is 2 (of the 3 people) and B's is 3 of 3. The problem occurs when I try to Sum the Awesome count for all locations. In this example, Sum(Awesome Count, Location ID) yields 15 instead of 5 because it sums for all person IDs. Is there something like a Distinct Sum?

I also tried a 2 step formula, where first formula was Maximum(Awesome Count, Location ID) and second formula was Sum(1st formula), but the second formula says "This field cannot be summarized when I hit save.

Any thoughts would be appreciated!

Upvotes: 0

Views: 124

Answers (2)

Hershizer33
Hershizer33

Reputation: 1246

I ended up creating 2 running totals for Awesome Count, one that reset every change in the Location ID group (total per location), and one that did not reset (grand total).

Upvotes: 1

Siva
Siva

Reputation: 9101

  1. First option would be check Select Distinct Records option in Database.

  2. Try below. This solution works assuming Awesome Count is always same for Location ID

a. Create a group with Location ID

b. Place the Awesome Count in detail section

c. Now create a formula @Result in group footer of the Location ID

Sum(Awesome Count, Location ID)/count(Awesome Count, Location ID)

Upvotes: 1

Related Questions