?
Solved

crystal reports  crosstab incorrect total when summing using distinct.

Posted on 2014-04-18
4
Medium Priority
?
2,010 Views
Last Modified: 2014-04-19
I know there is a known issue with crosstab not totalling  the rows correctly when you sum values using distinct.  Is there any work around for this?  

example.

total---- 5
row1---  4
 row2--   2

if there is  one value in both row1 and row2 "distinct count" causes the value to counted once when sumarrizing the total.

Short of not using a crosstab... any workarounds for this?
0
Comment
Question by:Mike Johnson
  • 2
4 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 40009664
Your example doesn't seem relevant to the question.

What is wrong with using COUNT to count all the elements.

Anytime I use Count or DistinctCount I try to use a field that has distinct values for each row I want counted like the record Id or key field.

mlmcc
0
 
LVL 35

Accepted Solution

by:
James0628 earned 2000 total points
ID: 40010233
mlmcc,

 FWIW, the example makes sense to me.  I think the idea is that the 1st row in the cross-tab gets a distinct count of 4 for that row/group, and the second row gets a distinct count of 2 for that row/group, but there is 1 value that occurs in both rows/groups, so a distinct count for the whole thing is only 5, instead of the total of the distinct counts from each row/group, which would be 6.


 uksub,

 Try this.  Create a formula that combines the field that you're using for the rows in the cross-tab, and the field that you're doing the distinct count on, and do a distinct count on that formula instead.

 For example, let's say that you were looking at city and state information, with one row for each state, and a distinct count on the cities in each state.  The same city name could should up in more than one state, which would throw off your distinct count total.

 You could create a formula like the following and do a distinct count on that formula instead:

{state field} + {city field}


 If both of your fields are strings, you can just concatenate them like that.  If one or both of them are numbers, there are different ways you could combine them.  The simplest would probably be to use CStr to convert them to strings and concatenate the strings.

 James
0
 

Author Closing Comment

by:Mike Johnson
ID: 40010541
Thanks James... that worked.   For future references ex.

Teacher1  bob
Teacher1  sam
Teacher1 Sally

Teacher 2  Sally

I used distinctCount on the formula ex.  techerN + student;   Now  Sally is counted twice. Once for Teacher1 and once for Teacher2.  Before hand distinctCount cause Sally to be counted only once in the final total.
0
 
LVL 35

Expert Comment

by:James0628
ID: 40010626
Yep.  That's the kind of thing I was talking about.

 And you're welcome.  Glad I could help.

 James
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Navigation is an important part of web design from a usability perspective. But it is often a pain when it comes to a developer’s perspective. By navigation, it often means menuing. This is less theory and more practical of how to get a specific gro…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
The viewer will learn how to user default arguments when defining functions. This method of defining functions will be contrasted with the non-default-argument of defining functions.
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.
Suggested Courses
Course of the Month16 days, 5 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question