Solved

crystal reports  crosstab incorrect total when summing using distinct.

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

737 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