Ad Hoc Tips & Suggestions

There are some things to consider when creating your ad hoc report. The data may respond in a seemingly "odd" way depending on the columns selected. The following is a list of tips and suggestions to consider when you are customizing your reports.

Help

Filter Builder: Rules and Groups

The filter builder allows users to create rules and groups. What is the difference and how are they used?

RULE	A single value that will limit the report results. Comprised of a field, operator and value.
GROUP	Allows users to group multiple rules together.

Users can add as many rules as they want to a report. They just have to select a data field, an operator like "Equals" and then select what value the report should match on for the selected data field. A group is useful as it allows users to group together multiple rules. Generally, this would be done when you want to treat some rules different than others (via And/Or, see below for more details).

This is best explained with an example:

Groups can give users more inclusive results when they are setup using the "Or" command. Users can have primary rules that they want to apply to their results (like person type) and then create a group to be able to add some variables. For instance, if they wanted to get results for all active officers with a current appointment in Territorial Headquarters or CFOT they could create filters like:

Another example for how a group could be used is if users wanted to see active officers that are specific ranks, like Captain or Major. This would return all active officers that have a rank of either Captain or Major:

In short, the trick with rules and groups is to use some primary rules that apply across the board (i.e.: person type) and then create a group for additional specifics.

Is it an "AND" or an "OR"?

When creating your report filters it is important to understand how the "And/OR" options will impact your results.

AND	Will return results that match ALL of the filters.
OR	Will return results that match any ONE of the filters.

Most reports will just use the "AND" option. One place users will use "OR" often will be in a filter group.

Filters, Filters, Filters

Always try to define your set of data very specifically. By default, ad hoc reports is going to try reporting on all records serving in the territory and users will need to be careful to limit the results strategically. It is suggested to always start by using the following filters:

Filter Name	Purpose	Example Data

Filter Name	Purpose	Example Data
Person Type	Use to restrict your report to specific types of records	"Officer", "Cadet" etc
Person Status	Use to restrict your report to records with a specific status	"Active", "Retired" etc

Too Many Duplicate Officers

The data returned in a report is dependent on the selected columns. When a column is selected the ad hoc report will try to return as much information as possible for the type of data (column). For example, if a user chooses to include the column "Command Name" the report will try to return the command for all appointments for an officer in the territory.

Depending on what is desired there are several things users can do in this example.

Use the filter "Is Current Appointment" - This will restrict results to records with current appointments.
Use the filter "Is Primary?" - This will restrict results to only primary appointments.
Use select dates (like appointment "Start Date"/"End Date") - This will restrict results to a specific range of time.

Dependent Listing Duplicates

If a report is created to show dependents then the results will display results for dependents for each parent. Meaning if a child record is marked as a "dependent" on both parents records it will be shown twice in the results. Once for the mother and once for the father.

One way this could be managed would be to filter on the parent gender. This would mean that the report would only list one dependent result per couple.

Use the filter "Gender" = F - This will restrict results to one parent result

This may not be effective if there are any dependents set for only one spouse.

Try things and keep in mind that you can always remove a filter and try again.

OMS Service Desk