091501

Advanced Reporting: Part 1

(September 15, 2001)

Audience: This bulletin will be of interest to those who are confident in their ability to produce simple reports but would like to learn more advanced reporting techniques. The bulletin offers in-depth discussions on how the Skills Manager report filters operate. It would be advantageous to read the Basic Reporting bulletin prior to reading this one.

More on Filters

The Basic Reporting bulletin described the basic function of report filters pretty well. Here is an excerpt from that bulletin:

A " filter" essentially sifts the database, using values supplied by you, to exclude irrelevant data from a report leaving only the records that you want to appear. Filters are used frequently since only on very rare occasions would you want to print the entire content of your database.

This basic concept is fundamental to constructing any report definition in Skills Manager. There are other concepts that you will need to know to successfully create more complex report definitions.

Filter Components

This section will help you to become familiar with the organization of the filters and the terms used to describe different filter components. Illustrations are presented to help you understand the concepts.

Filter Groups

A "filter group" is simply a group of filters that are associated with a particular file in the database. An example of one filter group is the employment filter group. This filter group contains all filters used to evaluate records contained in the employment file. There are other filter groups such as certification, education, training, notes, etc.

A filter group is activated by placing a checkmark next to the group name on the main Report Queue entry form as shown in the adjacent illustration. Activating a filter group causes the associated file to be processed and evaluated when the report is launched. When a filter group is deactivated (unchecked) the associated file is not processed or evaluated.

IMPORTANT: Activating additional filter groups will cause a report to process longer because you are essentially instructing the report to process data contained in additional files. Activating just one additional filter group may cause hundreds or even many thousands of additional records to be processed, depending on the size of your database. You may find that you occasionally need to activate as many as 3 or 4 filter groups to get the results you desire.

Filters

A "filter" is used to evaluate the content of a field (or column) in the associated database file. A filter is activated by entering one or more values into the entry field provided. The adjacent figure shows the agency filter in the employment filter group. This particular filter operates on the agency id field in the employment file.

NOTE: When a new report definition is created the filters are set by default to include all records.

Filter Values

The "filter values" are the actual codes, dates, or numbers that are placed into a filter entry field. These values are compared with the content of the field (or column) in the associated file when the report is processed.

Filter Relationships

It is also important to understand the relationships between the various report filter components so that you can use them effectively without regularly applying the old "trial and error" approach. There are two kinds of relationships that can exist between filters and they are the "and" relationship and the "or" relationship.

The "and" Relationship

The and relationship links filters (or conditions) together to create a single, larger expression. Every filter in the expression must evaluate to TRUE for the overall equation to also be TRUE.

Here is an illustration to help you understand how and filters operate. Imagine you are standing in front of a large crowd and you shout the following question: "Will all those who have blonde hair and blue eyes please step forward?" Do you realize that this question contains an expression composed of two and filters (or conditions)? Look carefully at the question again. Can you see the two filters? Blonde Hair and Blue Eyes

If the appropriate people respond to your request then you will see a very specific subset of the crowd step forward. Every person standing before you should have blonde hair and blue eyes. An and filter of sorts has just been executed. It doesn't seem quite so technical when described like this, does it?

The "or" Relationship

The or relationship separates filters to create a series of individual expressions. Every filter is evaluated independently. Only one of the expressions must evaluate to TRUE.

Let's take the same illustration used above and change it slightly to demonstrate this concept. Standing in front of the same crowd you shout: "Will all those who have blonde hair or blue eyes please step forward?" Can you see that this question is composed of two independent or filters (or conditions)? Blonde Hair or Blue Eyes

The result of this request should be a much larger group of people than with the first request. All of the people who respond to the first question should also respond to this one. In addition, brunettes should respond if they have blue eyes and those with brown eyes should respond if they have blonde hair. Other individuals may also respond as long as they meet either one of the conditions.

Relationships Predefined

All of the filter relationships in Skills Manager have been preprogrammed for you so that you don't have to compose a complex query expression as some products require. Still, it will be helpful for you to know how these relationships have been implemented to use the filters effectively.

1) An and relationship exists between filter groups. (A filter group is defined previously in this bulletin).

Example: Let's say that you want a report showing all active employees that are certified driving instructors. To accomplish this you will activate the employment filter group and the certification filter group. In the employment filter group you set the employment status filter to select all "Active" employees. In the certification filter group you set the certificate filter to "Driving Instructor". Because of the and relationship between these two filter groups, the resulting report will include only employees who are currently employed and also certified driving instructors.

NOTE: If the relationship described above was an or relationship instead, the result of the report would be completely different. The report would include all active employees regardless of their certification and it would also include all driving instructors regardless of their employment status.

2) An and relationship exists between individual filters within the same filter group. (A filter is defined previously in this bulletin).

Example: Let's say that you want a report showing all active employees working at the Portland Police Department. You activate the employment filter group and you set the employment status filter to select all "Active" employees and the agency filter to "Portland" (represented by the code "PDX"). Because of the and relationship between these two filters in the same filter group, the resulting report will include only employees who are currently employed at the Portland Police Department.

NOTE: If the relationship described above was an or relationship instead, the result of the report would be very different. The report would include all active employees regardless of their agency affiliation and it would also include employees from the Portland Police Department regardless of their employment status.

3) An or relationship exists between filter values within the same filter. (Filter values are defined previously in this bulletin).

Example: Let's say that you want a report showing all employees certified as driving instructors or radar instructors. You activate the certification filter group and set the certificate filter to include "Driving Instructor" (represented here by the code "DI") and "Radar Instructor" (represented here by the code "RI"). Because of the or relationship between these two filter values in the same filter, the resulting report will include employees who are certified either as a driving instructor or a radar instructor.

NOTE: If the relationship described above was an and relationship instead, the result of the report would be very different. The report would include only employees who are certified as both driving instructor and radar instructor

Some filter groups have an advanced feature that permits you to alter the relationship between filter values. The relationship between filter values defaults to or as we just discussed. However, this advanced feature allows you to change this relationship to and if you have the need to do so.

"Who" and "What" Filter Phases

When a report is launched for processing, it begins to process records contained in the database. As the records are processed they are evaluated against the filters specified in the associated report definition. The appropriate fields in each record are compared with the values supplied in the corresponding filters to determine if the record should be included or excluded from the report.

There are two phases of filter operation and we have named these phases to help you remember them. The first phase is called the "who phase", and the second is called the "what phase".

In the who phase, the filters are applied to determine which entities will appear on the report. In the case of an employee report the entities are employees. For example, who are the employees that will appear on my report? In the case of an agency report the entities are agencies.

In the what phase, the filters are applied to determine what details will appear on the report for the entities selected in the who phase. The what could be employment history, certification history, training history, and so on. While all reports utilize the who filters, only some use the what filters. Use of the what filters is determined by the type of report selected. Let's take a look at some examples that illustrate the difference between the who phase and what phase filters.

Figure 1 presents a simple Employee Roster report. The employee gender filter is used here to select only female employees for the report.

Gender = "Female"

Name	Social Security
Anderson, Renee	500-55-1234
Andrews, Alice	500-55-2345
Atwater, Martina	500-55-3456

Figure 1. Employee Roster

You will notice with this first report, that the who phase filters are used to determine who appears in the report, no males just females. The what phase filters are not required as there are no related details to be filtered or printed.

Figure 2 presents an Employee Training Report. The same employee gender filter is used here to select only female employees for the report, but in addition a training date filter is used to select only females who have attended training between the dates of January 1, 2001 and March 31, 2001.

Gender = "Female"

Training Date = "01-01-2001 to 03-31-2001"

Course	Title	Date	Hours	Score
Andrews, Alice
150050	Weather Training	1-3-01	2.0	95.0
080639	Patrol Techniques	2-14-01	8.0	93.5
070114	Snowmobile Safety	2-15-01	1.0	98.0
Atwater, Martina
150050	Weather Training	1-3-01	2.0	97.0
070221	Telephone Harassment	3-17-01	3.0	92.0

Figure 2. Employee Training Report

You will notice with this second report, that the who phase filters are used to determine who appears on the report in the same way they were used in the first report. Only this time, in addition to the selecting just females, the training filter selected only those who attended training between the specified dates. Renee Anderson appears in the first report but not the second, therefore we can assume that she did not attend any training between the specified dates.

You will also notice that the what phase filters are at work in this report. Look at the training records that were printed for each employee. Can you see that the only training records printed are those between the dates specified in the filters? This is a result of the what filters working to limit the details included in the report.

If the what filters were eliminated the report would look considerably different. Without the what filters, all of the training records for the selected employees would appear in the report and this is typically undesired. The report would be lengthy and it would be filled with irrelevant data. Figure 3 shows us how the second report might look if the what filters were removed. Notice the number of training records outside of the date range specified in the previous example.

Course	Title	Date	Hours	Score
Andrews, Alice
070218	Livestock Theft / Inspection	10-8-00	5.0	89.0
150050	Weather Training	1-3-01	2.0	95.0
080639	Patrol Techniques	2-14-01	8.0	93.5
070114	Snowmobile Safety	2-15-01	1.0	98.0
155214	Gambling	5-24-01	4.0	100.0
156317	Game & Fish Laws	7-10-01	16.0	95.0
Atwater, Martina
080233	Hate Crimes	3-2-00	4.0	96.0
150117	Human Relations	9-18-00	8.0	88.0
070106	Hunter Safety	9-19-00	3.0	98.0
150050	Weather Training	1-3-01	2.0	97.0
070221	Telephone Harassment	3-17-01	3.0	92.0

Figure 3. Employee Training Report

After reading this bulletin you should clearly understand the various filter components, the relationships between these components, and the two phases of filter operation. With this knowledge and a little practice you should be able to effectively use the reporting capabilities provided in Skills Manager.

Still, there will be occasions when you will be called upon to create a report that tests your knowledge. I encourage you to give the report a good effort when such a need arises and I think you will impress yourself and learn much. However, do not allow yourself to labor over or become frustrated by such a report. If you get stuck, call the Skills Manager support center and we will be glad to help you.

by Steve Wesner

Crown Pointe Technologies, Inc.