?
Solved

Crystal Report XI - Distinct Count Where Table.Field = A

Posted on 2009-04-02
4
Medium Priority
?
1,993 Views
Last Modified: 2012-06-22
I'm trying to find a solution/formula where when the Table.Field1 = A, I can then get a DistinctCount from Table.Field2.  I've tried If/Then statements and While/Do statements.
For instance, I'll create a Formula Field called @Example and put in the formula:  If {Table.Field1} = A Then DistinctCount({Table.Field2}).  It doesn't seem to add up correctly.
Then when I do the following, @Example - While {Table.Field1} = A Do DistinctCount({Table.Field2}), I get a loop error.
I've also created it where I just have @Example to be {Table.Field1} = A and then in the report itself have it do a distinct count; however, it's adding up the wrong field.

I want to total amount of Table.Field2 where Table.Field1 = A.

Thanks.
0
Comment
Question by:kristibigo
[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 17

Assisted Solution

by:MIKE
MIKE earned 375 total points
ID: 24051334
Well to do this very easily in SQL use this syntax:

To find SUM of the field:

select sum(Table.Field2)
from Table
where Table.Field1 = 'A'

To find COUNT of the field:

select count(Table.Field2)
from Table
where Table.Field1 = 'A'

To find DISTINCT COUNT of the field:

select distinct count(Table.Field2)
from Table
where Table.Field1 = 'A'

Will this work.....or do you not have access to the Database..?
0
 

Author Comment

by:kristibigo
ID: 24051713

Yes, however, I also just figured out another way to solve my issue.  Here is what I did:
Created in the Formula Field @Example1 where the formula reads:
If {Table.Field1} = A Then 1 Else 0
Then I went to the report to the Toolbar >Insert >Summary
Chose from the field to summarize: @Example1
Calculate this Summary: 'Sum'
Summary location: Group #?
And then clicked OK
From there, the @Example appeared in my report, and I just dragged it to the place where I wanted that result to be seen.
I was then able to do likewise for the opposite information where in the Formula Field @Example2 was created {Table.Field1} <> A Then 1 Else 0
Thank you for your input above.
0
 
LVL 17

Assisted Solution

by:MIKE
MIKE earned 375 total points
ID: 24051732
Sure,...there are usually more than one way to solve our issues.

I usually go to the datasource SQL since it speeds up the report processing...

Glad you figured it out.
0
 
LVL 35

Accepted Solution

by:
James0628 earned 375 total points
ID: 24056069
FWIW, if you haven't already figured this out, the reason that DistinctCount did not work in your "if" formula is that DistinctCount, and the other summary functions, are not ongoing things that are applied on a record by record basis.  They produce the actual summary values.  For example, every time you use Count ({field}), it gives you the count for {field} for the entire report.

 Also, While/For loops are not stepped through once for each record.  The entire loop is repeated every time the formula is evaluated.  So, ' While {Table.Field1} = "A" ' would not do anything if the field in the current record was not "A", and would be an endless loop if the field in the current record was "A".


 As for the formulas that you said you're using, they may work, but I don't think they'll give you exactly what you said you were looking for.  You said that you wanted a distinct count of Field2 where Field1 is "A".  What you're getting is just a count, not a distinct count.  If Field2 is always different, then the two counts would be the same.  But if Field2 is sometimes the same in different records where Field1 = "A", then a distinct count would be different (smaller).

 For example:

 Field1   Field2
   A           1
   A           2
   A           2
   B           1
   B           2

 Your count would return 3, for the 3 "A" records.  But a distinct count of the values in Field2 in those "A" records would give you 2.

 James
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month11 days, 22 hours left to enroll

752 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