PIMS Resource Center
Custom Queries

 
Overview
3/20/15

The Custom Queries menu lets you design your own PIMS reports.  There are four steps to creating your own report:

  1. Define Your Query
  2. Select Criteria
  3. Select Group and Site Filters
  4. Process Query
 

 
1) Define Your Query

Query Type

You can choose between five report types:

  • Participant Data (5 columns)- displays up to five fields of participant data for all participants satisfying your specified criteria.  This query displays one record per participant and cannot be used to display repeated activities such as home visits and referrals. To choose each of the five fields, first select a Query Category, and then select a Query Field.
  • Participant Data (10 columns)- displays up to ten fields of participant data for all participants satisfying your specified criteria.  This query displays one record per participant and cannot be used to display repeated activities such as home visits and referrals. To choose each of the ten fields, first select a Query Category, and then select a Query Field.
  • Participant Activities- displays up to ten fields of data for all participants satisfying your specified criteria.  This query will display one row per occurrence of the selected activity- for example, one row per referral for each of the participants in the cohort. To choose each of the ten fields, first select a Query Category, and then select a Query Field.
  • Frequency Distribution- displays the distribution of all participants across any one selected category.  For example, you can report on what percentage of participants had scores of mild, normal, severe, and unknown for any FSC risk factor item.
  • Participant Groups- uses the specified criteria to select a group of participants for inclusion in a user-defined group. Group assignments are useful for filtering Standard Reports.

define report

Sub Type
If you choose the query type "participant activities," you will also have to choose which specific activity you want to display in your report.
 


 
2) Select Criteria

The second set to creating your report is to select criteria, using the following steps:

  • Choose a Field Type, or category on which you want to report
  • Choose a specific Field
  • Choose a mathematical Operator
  • Choose a Value.  Depending on the field you select, the value field may be a text box or a drop-down box, and if you choose the “is between” operator, you will be required to select two values.
  • Once you have selected your criteria, click the Add button.

select criteria dialogue

When you click the Add button, your new criteria will appear in the Query Criteria box.

select criteria menu

Once you have added criteria, you have several options for organizing these criteria:

  • Remove allows you to remove one criteria at a time
  • And/Or allows you to specify logic for your criteria.  If you are not familiar with queries or Boolean Logic, you may need some training to practice this.
  • ( ) allows you to group logic for your criteria.  If you are not familiar with queries or Boolean Logic, you may need some training to practice this.
  • Clear All removes all selected criteria

boolean options

 

 
3) Select Group and Site Filters

select groups box

 

 
4) Process Query
You have the following options for using your query:
  • Preview Query Results - opens a report in Microsoft Access displaying your query results.
  • Export Spreadsheet - saves a spreadsheet which can be opened in Microsoft Excel.
  • Save Query - saves a query, which can later be re-opened using the Saved Query Manager.
  • Create Group - creates a group, which can be used to filter cohorts in the Standard Reports Menu.

select groups box


 
  © Copyright 2007-2018 Prevent Child Abuse America. All rights reserved.