• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 163
  • Last Modified:

Crystal Formula to "conditionally count/sum"

Gurus,

I created a series of reports that include "conditional count/sum" formulas like this:

if {TBL_HISTORYTYPE.NAME} = "Client Visit Completed" then 1 else 0

Unfortunately, I recently found out that some of the records are repeating because of another table that's tied to the History table.  (CONTACT -> HISTORY -> HISTORYTYPE) (My counts are incorrect if multiple contacts are included).

I'm trying to avoid rebuilding all of these reports by adding subreports for the contact information.

Is there a way to incorporate the HISTORY.HISTORYID field (string) in the formula to only sum if the HISTORYID has changed?

Also, my "totals" have to appear in the Report Header - so I don't think a "running total" will work.

I appreciate any ideas!  Thanks in advance
0
SStroz
Asked:
SStroz
  • 2
1 Solution
 
mlmccCommented:
You are correct that a running total won't work.

Try this

if {TBL_HISTORYTYPE.NAME} = "Client Visit Completed" then 
    {HISTORY.HISTORYID}

Open in new window


Do a distinct count on that field.

mlmcc
0
 
SStrozAuthor Commented:
Shoot - I closed this too soon.

I should have mentioned that I have 3 formulas like this:

if {TBL_HISTORYTYPE.NAME} = "Client Visit Completed" then {HISTORY.HISTORYID}
if {TBL_HISTORYTYPE.NAME} = "Prospect Visit Completed" then {HISTORY.HISTORYID}
if {TBL_HISTORYTYPE.NAME} = "Trial Visit Completed" then {HISTORY.HISTORYID}

It doesn't look like it's counting correctly.  

It looks like Crystal is counting a "1" where there should be a "0".  ??


Any ideas?
0
 
mlmccCommented:
I was afraid that might happen.

It is counting the ones that don't match as 1 since the return is not null

Try it this way

Create a formula
Name - NULL
Formula is nothing just save an empty formula

Change the formula above to

if {TBL_HISTORYTYPE.NAME} = "Client Visit Completed" then 
    {HISTORY.HISTORYID}
Else
    {@NULL}

Open in new window


That will result in NULL for the non-matching and DistinctCount will ignore the NULLs

mlmcc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now