Tallying Records

[BradW]

Good afternoon folks, I have a tricky problem that I've been unable to solve and am banging my head on the monitor by now!

I have a SQL Server database with multiple tables, the two that I'm working with on this report have a one-to-many relationship, the first one is "incidents" (the one record) and related "actions" where there are no to many records per incident.

The actions can fall into many categories that are defined by a field. I'll label the actions A, B, C, D and so on. Each Incident can have any one or more of the actions, up to all of them.

I want to count how many actions take place in a timeframe (calendar month, year, etc) but here is the tricky part, they need to be counted based on what actions are present.

Example:

Incident 1
Action A <---Count
Action B

Incident 2
Action A <---Count
Action B
Action C

Incident 3
Action B <--- Count
Action C

I've written running totals (one for each action to be counted) to try to count his with a formula in the Evaluation section, but it is not functioning correctly, it is counting the B records even when an A is present.

Thanks for all help in solving this!

 

[Charliy]

Is the answer you're looking for in this example:

3 Incidents
Action A: 2
Action B: 1
Action C: 0
Action D: 0 Shared variable my be the best way to get this result


or

3 Incidents
Action A: 2
Action B: 2
Action C: 2
Action D: 0 Running Totals with conditional evaluation give you this result

 

[BradW]

Answer #1,

2
1
0
0

I've not tried shared variables yet, could you give me a start on this?

Thank you VERY MUCH for your response!

 

[lbass]

You need to provide the rules that tell when an action should be counted.

-LB

 

[BradW]

In this case, count A whenever present (one time for each incident, as determined by a unique incident number - same for all counting), no matter how many items are thre, count B when present and no actions are A. Count C when present and no actions are A or B, count D when present and no actions are A, B or C.

Thanks for your help!

 

[lbass]

I would start out by creating a formula that assigns a letter to the action, based on priority. Let's say this is a crime database, and murder is highest level felony, followed by armed robbery, and then by breaking and entering. Create a formula {@ActionCode} like this:

select {table.action}
case "Murder" : "A"
case "Armed Robbery" : "B"
case "Breaking and Entering" : "C" //etc.

Then insert separate running totals for each code, e.g., {#CodeA}, where you do a distinctcount of incident #, evaluate using a formula:

minimum({@ActionCode},{table.incident#})="A"

Reset never or on change of group at the level you want to evaluate. Place the rt in the group footer or report footer depending up on the level of evaluation.

This assumed you have inserted a group on incident#.

-LB

 

[BradW]

Thanks so much for the help, it worked like a charm!