Solved

crystal reports  crosstab incorrect total when summing using distinct.

Posted on 2014-04-18
4
1,889 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 100

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 500 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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…
This article will show, step by step, how to integrate R code into a R Sweave document
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.

740 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