Solved

crystal reports  crosstab incorrect total when summing using distinct.

Posted on 2014-04-18
4
1,825 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 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 34

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 34

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
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 tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
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.

772 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