Need Sum of Records that occur in a parameter field

[ProALC]

I need to get a sum on the number of records that occur in a parameter field.

The parameter field asks the user to pick a range of dates to indicate the reporting period to be used to run the report ({?This Reporting Period}).

For whatever reason, this formula works logically, but won't calculate all the records in the range:
If {NCR_37_ttx.Notices~Date} >= ({?This Reporting Period}) and {NCR_37_ttx.Notices~Date} <= ({?This Reporting Period})then Count ({NCR_37_ttx.Notices~Date}) else 0. (I've also tried Distinct Count with the same result).

Let me know if you need any more info.
Thanks in advance for the help and guidance!

Amy

 

[pbateman]

try:

If {NCR_37_ttx.Notices~Date} in {?This Reporting Period}then Count ({NCR_37_ttx.Notices~Date}) else 0

(although I'm sure about the count bit)

 

[ProALC]

Ok. That formula works, in a sense. It calculates this way: if that date occurs in that range, it counts all of them. So instead of getting those that occur in that range, I get all that occur (most of the time - for some reason it won't count the dates towards the beginning of the month.)

The records that I have in the database for {NCR_37_ttx.Notices~Date} are as follows:
12/8/2004, 12/28/2004, 12/29/2004, 12/29/2004 and 12/30/2004.

Here are some of the results I've gotten when I've run the report:

Parameter Result(# of records)
12/6/2004-12/9/2004 0
12/6/2004-12/30/2004 5
12/28/2004-12/30/2004 5
12/6/2004-12/28/2004 0

What do I do now?

 

[lbass]

Place the following in your detail section:

If {NCR_37_ttx.Notices~Date} in {?This Reporting Period}then 1

Then right click on the formula and insert a summary (SUM, not count) on this.

-LB

 

[ProALC]

It works PERFECTLY!!

A thousand thanks to you, lbass.

I feel like a moron now because the answer was so simple!

 

[ProALC]

Ok, the report works great, but now when you run the report, the zeros don't show up. Do I need to change my formula or is it a formatting issue?

Thanks!

 

[rosemaryl]

Dear ProALC,

Nope just add else 0 to the end of each formula.

If {NCR_37_ttx.Notices~Date} in {?This Reporting Period}then 1
else 0

Regards,
ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[ProALC]

Thanks. I've already tried that and it doesn't work.

 

[ProALC]

Also, that isn't the field I am showing on the report. I am showing the summary of:

If {NCR_37_ttx.Notices~Date} in {?This Reporting Period} then 1 else 0

and if the summary = 0, the 0 doesn't show.

 

[rosemaryl]

Dear ProAlc,

Hmm, exactly what did you try?

You must modify the formula in the detail section ... and if the field you are checking could possibly be null, you should check for that also.

If isnull({NCR_37_ttx.Notices~Date})
then 0
else if {NCR_37_ttx.Notices~Date}
in {?This Reporting Period}
then 1 else 0

Once you modify the field in the detail section, it will correctly evaluate to 0 and show 0 if that is the sum.

Regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[ProALC]

Ro,
I've tried the isnull formula and the formula doesn't work. This information is all in a subreport. Do you think it could be a formatting issue between the main and sub reports? I am stumped as to why it would clearly print every other number but "0"!
I think I am correct when I say this, but the
{NCR_37_ttx.Notices~Date} could never be null because the record won't save unless there is a date in the proper format in that field.

Thanks.

 

[rosemaryl]

Dear ProALC,

Well, it does help to have information. You state the information is all in a subreport.

When no records are returned; the field would indeed be null.

When you want the value to be 0, is this because there are no records?

------
Try this, create a shared variable in the main report in a formula:

//main report formula
//Mysum Initiate
While Printing Records
Shared numbervar mysym := 0;
mysum
//end formula for main report

Place in main report prior to the subreport.

In your subreport, create and place this formula instead of your current sum field:

//subreport formula
//mysum eval
WhilePrintingRecords;
Shared Numbervar MySum :=
MySum + Sum(@YourDetailFormulaName);
MySum
//end mysumeval

Does that fix it?

Regards,

ro




Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[ProALC]

Ro,
Actually that didn't work. What now?

Amy