[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
Solved

crystal reports  crosstab incorrect total when summing using distinct.

Posted on 2014-04-18
Medium Priority
1,989 Views
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
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
• 2

LVL 101

Expert Comment

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

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

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

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

Question has a verified solution.

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

Having just graduated from college and entered the workforce, I don’t find myself always using the tools and programs I grew accustomed to over the past four years. However, there is one program I continually find myself reverting back to…R.   So …
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.
The goal of this video is to provide viewers with basic examples to understand and use conditional statements in the C programming language.
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…
Suggested Courses
Course of the Month14 days, 11 hours left to enroll