Formula with sum of running total as a variable?

[staciann]

Hi. I am creating this report in CR10.

I do not have anything grouped. I have two details sections: (a) includes info that will be supressed and (b) includes charts and graphs. I will be using the info from (a) in the report header.

The statement in the header reads "There are a total of ## ({Count of tblReplyCards.Date}) responses. Of these responses, ## are negative and ## are positive."

Normally, I just do a grand total of tblReplyCards.complaints - however, this time I only want to count them if they are one of 3 out of 5 types (communication, errors, timeliness). I created a formula to do a running total of this field only if the entry is equivalent to 1 of the 3 types I want to include - but I know I can't create a sum of a running total.

Is there a way to get the total of this count to appear in the header section where it says "## are negative"?

Then I would like to subtract that number from the {Count of tblReplyCards.Date} to get the number of positive responses and put the other 2 of 5 (suggestions and misc.)options under positive as suggestions.

ej.

A total of 200 responses.
50 Negative
- 10 because of Communication Problems
- 20 because of Written Errors
- 20 because of Timeliness

150 Positive including 15 suggestions and 12 micelaneous responses.

Thank you in advance for you help,
Staci

 

[staciann]

OK, I tried grouping it by positive or negative and am able to get them grouped correctly by using a formula:

if isnull ({tblReplyCards.ComplaintType})
then "Positive"

else if {tblReplyCards.ComplaintType} = "" or
{tblReplyCards.ComplaintType} = "Suggestion" or
then "Positive"

else if {tblReplyCards.ComplaintType} = "Communication" or
{tblReplyCards.ComplaintType} = "Error" or
{tblReplyCards.ComplaintType} = "Miscellaneous" or
{tblReplyCards.ComplaintType} = "Timeliness"
then "Negative"


Is there a way to do a total count of each and then put it in the header?

 

[lbass]

Create conditional formulas like:

//{@Pos}:
if {@yourformula} = "Positive" then 1

Then write a formula like the following to use in the report header:

sum({@Pos})

You can build this into a formula with your text, or you can use a text box with the text and then drop the formulas in.

-LB

 

[staciann]

Perfect! Thank you...I can't believe I didn't think of that!

 

[staciann]

Ok, I got everything set up with the counts all correct. I wrote a formula field that says:

if {tblReplyCards.ComplaintType} = "Communication"
then "Communication"
else if {tblReplyCards.ComplaintType} = "Error"
then "Error"
else if {tblReplyCards.ComplaintType} = "Miscellaneous"
then "Miscellaneous"
else if {tblReplyCards.ComplaintType} = "Timeliness"
then "Timeliness"

but when I use this to create a chart it still keeps adding in the null records and the "Suggestion" records as pie chart sections. Is there something I could put in the formula to exclude those sections?

 

[lbass]

In the chart expert, highlight your formula in the on change of area->order->specified order->add the four formula options->others tab->discard all others.

-LB