Formula for Counting certain records 2

[kchaudhry]

I am trying to perfrom the COUNT for records with a certain word. I am trying to generate a Transaction count field, but for that field I only want the records which have the word LOCAL in them.

Currently I am using the following formula:

if {Participant_Move_in_Trans.Trans_Category} = "Local" then Count ({Participant_Move_in_Trans.Trans_Category})

I have created a group with the Employee ID's.When I place this formula in the group header it give me the count for the entire report not the selected employee.

Can someone please guide me in the right direction?

Thanks

 

[bdreed35]

Create yourself a formula that will evaluate each record:

if {Participant_Move_in_Trans.Trans_Category} = "Local" then
    1
else
    0

Now you can create a Summary of this formula for the report overall or any of your groups.

Insert, Summary.
Select the formula above in the second drop down, and then choose SUM in the first drop down.
Choose the appropriate group (Employee IDs per your post), then click OK.
It defaults to the Group Footer but you can move it to the Group Header if you like.

~Brian

 

[synapsevampire]

Or use a Running Total and in the Evaluate Use a Formula place:

{Participant_Move_in_Trans.Trans_Category} = "Local"

this eliminates the need to place a formula in the details to be summarized.

-k

 

[bdreed35]

SV:

On a side note, you don't have to insert my formula in the detail section. You can create a summary off of the formula and evaluates correctly.

~Brian

 

[synapsevampire]

Agreed, Breed, you could, though I would place the field as a kindness to the next developer.

One advantage to your approach is that you can instantly create summaries for all groups and a grand total, otherwise I prefer the Running Total approach, it's just a bit cleaner.

-k

 

[kchaudhry]

Thanks for your reponse. I have tried both the suggestions and both of them work. Now I am facing another probelm. I am trying to create a formula which divides two values but in some cases the denominator is 0. There is no answer for this so I am not able to place the formula in the report. I have tried the following:

If {Participant_Move_in_Trans.Lease_Target_Rate} <> 0
then
{Participant_Move_in_Trans.Override_Price_Variation}/{Participant_Move_in_Trans.Lease_Target_Rate}
else 0

Assuming that if the denominator has 0 then just put a zero in the report, otherwise do the calculation. I am getting zeros every where.

Can you please help?

 

[bdreed35]

Could {Participant_Move_in_Trans.Lease_Target_Rate} have NULL value? If so, you need to check for null in your formula:

If IsNull({Participant_Move_in_Trans.Lease_Target_Rate})
   OR {Participant_Move_in_Trans.Lease_Target_Rate} = 0 then 
    0
Else
   {Participant_Move_in_Trans.Override_Price_Variation}/{Participant_Move_in_Trans.Lease_Target_Rate}

~Brian

 

[kchaudhry]

Brain, thanks it worked. Appreciate all your guidance.

KCHAUDHRY