Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Issue with "DistinctCount" formula Post Reply Post New Topic
Page  of 2 Next >>
Author Message
steinertractor
Newbie
Newbie
Avatar

Joined: 27 Dec 2010
Location: United States
Online Status: Offline
Posts: 6
Quote steinertractor Replybullet Topic: Issue with "DistinctCount" formula
     Posted: 27 Dec 2010 at 6:57am
Okay, so I've tried a number of things and I've read a bunch of info in this forum, but I can't quite figure this out. I am trying to write a formula to count the number of invoices I have that are flagged for manual review.  Here's the formula I'm starting with:

DistinctCount({SOP30200.SOPNUMBE}, {spxSalesDocument.ufManualReview}, "Yes")


SOP30200.SOPNUMBE is a string.  spxSalesDocument.ufManualReview is a Boolean.  From reading the info in Crystal Reports help, I *thought* I was to format my formula like this: 
  • DistinctCount (fld, condFld, cond)

So, the field I'm counting is the SOP Number, my conditional field is a Boolean, condFld and my Condition is that it is Yes.  However, with this formula I get an error that says:  This group condition is not known. I tried with a "1", I tried as "True", I tried as "every Yes" but it always gives me the same error. 

I found this information this forum about the conditions for Booleans: 

Boolean conditions
  • on any change
  • on change to yes
  • on change to no
  • on every yes
  • on every no
  • on next is yes
  • on next is no


This doesn't tell me the syntax though. I did try typing in "on every yes".  If I don't use quotations, I always get an error message stating that a string is expected. 

Can someone help me with the correct syntax?

Thanks so much! I know this is probably way easier than I'm making it. 
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 28 Dec 2010 at 3:37am
in reading the help entry, it would, at first glance, appear that your code is correct, but I think there is a missing piece.
 
usually, in CR, the condFld is a grouping condition.  Does your report group the data based on the ManualReview field?  If it doesn't, this probably won't work. If it is, then I am not sure why it isn't working.
 
you can probably set up a running total, but that is not my forte, but DBlanks.  I would set up variables and increment them as needed.  something like:
SOP30200.SOPNUMBE}, {spxSalesDocument.ufManualReview}, "Yes")

shared stringvar invoices;
shared numbervar invoiceCount;
if instr(invoices,{SOP30200.SOPNUMBE} + "|") = 0 AND {spxSalesDocument.ufManualReview} THEN
  invoiceCount := InvoiceCount + 1;
 
invoices := invoices + {SOP30200.SOPNUMBE} + "|")
 
"" //hides all the formula results
 
HTH
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 28 Dec 2010 at 4:51am
Running Total Version
Right Click on Running Totals and select New
name=CountOfYes
Field to Summarize=SOP30200.SOPNUMBE
Type of summary = Ditinct Count
Evalaute = Use a formula
{spxSalesDocument.ufManualReview}="Yes"
Reset =Never
Place in report footer (RTs do not work in headers)
IP IP Logged
steinertractor
Newbie
Newbie
Avatar

Joined: 27 Dec 2010
Location: United States
Online Status: Offline
Posts: 6
Quote steinertractor Replybullet Posted: 28 Dec 2010 at 4:57am
Cool, I'll give it a go today and let you know how it works out. 

I didn't consider using the running total feature.  Can I include RT in a group footer or only in a report footer?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 28 Dec 2010 at 5:04am
Works in detail section to show incrimental changes
works in group footers to show the amount up to that point int he report - you can reset at the group level to restart the count per group
you would need two RTs if you want to show group amounts and a report total
 


Edited by DBlank - 28 Dec 2010 at 5:05am
IP IP Logged
steinertractor
Newbie
Newbie
Avatar

Joined: 27 Dec 2010
Location: United States
Online Status: Offline
Posts: 6
Quote steinertractor Replybullet Posted: 28 Dec 2010 at 5:51am
Awesome! This worked perfectly. I actually had multiple groupings where I wanted to show the total and so I set up 2 different running totals.  I actually had forgotten about this functionality (with the formula and the running total) this is going to get me out of another issue I was having with a different report.

Thanks so much for the help!

If anyone else with a similar issue stumbles upon this thread, in this formula, if your field is a Boolean, then the word "Yes" doesn't go in quotations. I only needed it in quotes for the other thing i was trying where it wanted a specific string.

Thanks again, guys!  Worked awesome!
IP IP Logged
VStevens
Newbie
Newbie


Joined: 24 Dec 2008
Online Status: Offline
Posts: 12
Quote VStevens Replybullet Posted: 22 Sep 2011 at 8:43am
I know this is a really old thread, but can someone please answer the original question. 

What is the syntax of the cond?
IP IP Logged
steinertractor
Newbie
Newbie
Avatar

Joined: 27 Dec 2010
Location: United States
Online Status: Offline
Posts: 6
Quote steinertractor Replybullet Posted: 22 Sep 2011 at 8:52am
My original problem was that the formula would only work if I was grouping based on the condfld, which I wasn't.  Probably if I was, it would have worked as typed.  I needed to use the running total method because I didn't want to group by that boolean field.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 22 Sep 2011 at 8:52am
That syntax is not valid and could not be salvaged to do what it was intended to do. Instead 2 alternative options were given to get the value he wanted (variable formulas or a Running Total).


Edited by DBlank - 22 Sep 2011 at 9:24am
IP IP Logged
VStevens
Newbie
Newbie


Joined: 24 Dec 2008
Online Status: Offline
Posts: 12
Quote VStevens Replybullet Posted: 22 Sep 2011 at 9:44am
I would still like to know the correct syntax, as I believe it will solve my problem...
I am grouping an a boolean field. 

distinctcount(,," ")  <--- What do I put inside the " "   I can't find this anywhere.  Thanks.
IP IP Logged
Page  of 2 Next >>
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.