Report Filter (mental block!)

[Rich Hartnett]

For some crazy reason I can't seem to wrap my head around this seemingly simple task.

I have a report based on a set. The set consists of a Parent Table which lists Fire Incidents, Locations, Dates, etc.

That table is linked to a child table consisting of "ApparatusTypes" (vehicles) responding to that incident.

The last child table is called "Personnel Responding" and is linked to the ApparatusTypes table.

A typical record would consist of a DATE, an APPARATUS TYPE, and SEVERAL PERSONNEL:

04/12/2010

ENGINE 20

Smith = 1
Jones = 1
Wilson = 1
Hargrove = 1

Total Personnel on this ApparatusType (vehicle) = 4
(Names are actually hidden on the report)

Each person on a vehicle has a field with a value of "1," and on the report I have a calculated field that totals the number of personnel per vehicle, per incident.

Now here's my problem: I need to filter the report to only show incidents (dates) where the number of personnel per ApparatusType (vehicle) is LESS THAN 4, and I can't figure out how to write the filter expression.

The total number of personnel per ApparatusType is figured in the following expession:

total(Personnelresp->Personnel_Count,grp->ApparatusType,grp->Detail)

and I assume I somehow need to figure that into the filter expression, but for the life of me I can't figure out how.

Any enlightenment would be appreciated.

(A sample report is included but not filtered.)

[forskare]

Re: Report Filter (mental block!)

Now here's my problem: I need to filter the report to only show incidents (dates) where the number of personnel per ApparatusType (vehicle) is LESS THAN 4, and I can't figure out how to write the filter expression.

Could you not put a calculated field on the report and hide it? The in the filter, simply refer to the calc field in the filter; so your expression would be: Calcfield > 4

kenn

[Rich Hartnett]

Re: Report Filter (mental block!)

Hi Ken, and thanks for the quick reply.

Actually I thought of the same thing as you, but I can't get it to work.

I took the same calculated field that I used to total all the personnel assigned to each vehicle, and used it in the filter expression.

Here's the caculated field:

PersonnelTL = total(Personnelresp->Personnel_Count,grp->ApparatusType,grp->Detail)

And here is what I put in the filter expression:

PersonnelTL<4

The result is the same with or without the filter.
(Notice the last pic. This is the report printed with the filter added, and
there are responses with 5 and 6 personnel which should not be showing up
since the filter specifies "less than 4.")

[forskare]

Re: Report Filter (mental block!)

Rich,

Use just the field which stores the # of personnel as the filter; personnel <4. You need the filter by the field, not a calc field.

kenn

[Rich Hartnett]

Re: Report Filter (mental block!)

Well I don't seem to be getting anywhere with this problem and I know it should be somewhat easy to do, so let me try this a different way.

In the attached screen shot I have a report which groups calls for a particular fire truck by Incident No.+Date. On each call, there are anywhere from 4 to 10 firemen on the truck. Each fireman gets a count of "1", and the total number of firemen is listed at the bottom of each group. My calculated total field is called "PerTL."

All I want to do is figure out how to filter the report to only show calls where there are (for example) 4 firemen on the truck.

I thought this could be done by using the calculated field and writing it as "PerTL = 4," but it gives me nothing but a blank screen.

What is the correct way to do this?

[Tom Cone Jr]

Re: Report Filter (mental block!)

Hello, Rich.

I don't think you can do this with a report filter that uses the calculated field for a group based on a linked child table to determine whether the parent (incident) record should be included in the report or not.

I can imagine two solutions for you.

1) Add a "flag" field (data type logical) to the incidents table. Run a script before running the report. The script would set all the flags false, and then when step through the incidents and set flags true if any of the vehicles that responded have the desired number of personnel. Then filter your current report limiting the included incidents to those where the flag field is set to true.

Alternatively

2) Run a script that steps through the incidents table and copies each record to a temporary table if and only if any of the responding vehicles have personnel totals that meet your desired criteria. Then base your report on a different set that includes the temp table instead of the incidents table.

It's not clear from your description how you need to handle incidents with multiple vehicles. If two respond, with one carrying 3 people, and the other carrying 5, would you exclude this incident since the total number of people for the incident is 8?

[G Gabriel]

Re: Report Filter (mental block!)

I know it should be somewhat easy to do

It is..

[Rich Hartnett]

Re: Report Filter (mental block!)

Tom in answer to your question about other vehicles, this particular report is already filtered to just show this Vehicle Type, as this is the only one that has to be reported if it leaves the fire house with a certain level of staffing, so the other responding vehicles are not figured into the picture.


Gabriel that works!!!!

Thank You Very Much for putting that together for me. I have been beating my head against the wall for weeks trying to make it work with the info I displayed, and you have made it happen.

As always, I appreciate everyone's input and suggestions. This message board is a wealth of knowledge.

[Tom Cone Jr]

Re: Report Filter (mental block!)

G,

Thanks for the simpler approach. You neatly sidestepped the issue I was worried about. Instead of trying to filter the report based on a calculated field in the layout itself, you used dbcount() in the filter expression to exclude unwanted records from the report. Nice job.

[G Gabriel]

Re: Report Filter (mental block!)

You're both welcome.

[Rich Hartnett]

Re: Report Filter (mental block!)

I'm back again with a somewhat similar request, only this time the report is laid out a little different.

This report shows an incident date, incident no., apparatus type and names of personnel, along with a single "count" for each volunteer firefighter, and a single count for each "career" (paid) firefighter, although I'm only interested in career firefighters for this project. I also have a total of each type (volunteer / career) for each incident. (See attached sample report.)

(The report has a group break on Incident Number, and on Apparatus Type.)

This is a monthly report, and what I am trying to do is create some caclulated fields in the footer that will tally how times an APPARATUS TYPE responded with 1 CAREER F/F, how many times it responded with 2 CAREER F/F's, 3 CAREER F/F's etc.

That information all appears to be available in the report, but I'm at a loss as how to create the correct expression that will give me the totals. I've tried a boatload of calculations but none of them give me the correct results.

As an example, in the attached report the footer should show something like this: (These are valid results for this report)

No. of times Engines responded with 1 Career Firefighter = 1
No. of times Engines responded with 2 Career Firefighters = 1
No. of times Engines responded with 3 Career Firefighters = 3
No. of times Engines responded with 4 Career Firefighters = 2
No. of times Engines responded with 5 Career Firefighters = 0
No. of times Engines responded with 6 Career Firefighters = 0
No. of times Engines responded with 7 Career Firefighters = 0

If I can just figure out how to make one of these expressions work, I can apply that calculation to the other values.

[Keith Hubert]

Re: Report Filter (mental block!)

Hi Rich,

I learned this from Tom Cone.

You need 2 calculated fields for each value you want to count. One to count the other to display the total.

Here is an example from a report that gives the results from a questionnaire. This counts the number of Responses that = 5 and displays the total value for each count in the calc field Res_EX_Tot.

Res_EX = if(Cust_Sat_Data2->Response=5,1,0)

Res_EX_Tot = total(calc->Res_EX,GRP->grand)

If you need more help with this, post some sample data.

[Rich Hartnett]

Re: Report Filter (mental block!)

Yes Tom is certainly "the man" when it comes to expressions.

Keith this looks like it will do the job, thank you very much.

My problem was that I was trying to make it work with a single calculation, rather than with two. As the expression goes, "I couldn't see the forest for the trees," and never thought to use a second calculation to total the first one.

I learn something new every day.

Thanks to both of you.

Rich