A Query is a saved component that is available for repeated use in the Analysis Templates module in PARcore. It provides the capability to retrieve test taker data of interest from the Analysis Datalake based on the criteria that is specified. A query is a required field in an analysis template and at least one test taker selection query will need to be configured for your program before an analysis template can be created. Once a query is associated to an analysis template it will be used in a Request to query the correct set of data to be analyzed.  Additionally, if you will plan on using the downloadable test taker record files for a request and want to include additional attibutes in the CandidateSelectionData.csv (i.e., include Customer ID to identify the records), then those attributes will need to be included in the query (i.e., add "Customer_ID is not NULL"). For more information see Download Test Taker Records. 

Create a Query
Select the Queries tile on the Landing Page or from the Main Navigation select Data and Queries

1. Select the Add Query button

Add Query

2. In the Add Query page, do the following:
  • The program defaults to the program selected in the Global Navigation
  • Name – Enter a unique name within the program
  • Description
  • Query Type
    • Test Taker Selection: This query type is used for retrieving test taker data used in Analysis Templates and Requests (a future capability will include being used for exporting test taker data).
Add Query Modal
 
  • Query Builder
    • Select the +Rule, +Ruleset , or +Math Rule buttons to start building the query expression
      • Rule - Use Rules when all the conditions in your query (field/operator/value) will be grouped by AND/OR conditions, or if the query only contains one condition with no AND/OR. 
      • Ruleset - Use a Ruleset when you want to group one or more rules or math rules together and may have different AND/OR conditions then the higher-level rule rows.
      • Math Rule - Use Math Rule if you need to enter a math expression to perform addition, subtraction, multiplication, or division on more than one attribute.
        • Once the Math Rule button is selected it will be disabled since all math calculations should be entered within the math expression text box
    • When a Rule is added: 
      • The first dropdown list contains all standard and grouped attributes that have been configured in the enriched layout(s) for your program.  Note that if you have more than one layout configured for your program, this list will contain attributes across all layouts that have been configured.
        • Standard Attributes - Values are mapped to Standardized_Attribute_Name field in the enriched layout
        • Grouped Attributes - Values are mapped to Group_Attribute_Name field and the Standardized_Attribute_Name fields within each group in the enriched layout 
          • GENERATED_SCORES - This grouped attribute is available for all programs and is coming from analysis scoring based on the scores that are configured in the scoring system for your program. The Standard Attributes within this group that are available for selection are Score Name and Score Code where you can create a query for the Score Value for those scores:
            • NAME[Score Name]:SCOREVALUE
            • CODE[Score Code]:SCOREVALUE
      • Based on the data type of the attribute selected the second dropdown field will list the operators that are available for selection (refer to table below)
      • Based on the data type and operator selected the third field is where you will enter the value(s)
        • If the value of the attribute should be blank/null, select the “is” or “is not” operator. The value box will automatically populate as read only _NULL_
        • Selecting the “in” operator will make the value field multi entry. To enter multiple values, select the Enter button on your keyboard after every value entered.
      • If the attribute selected was identified in the enriched layout with the ADL_Group_Cardinality field having a value greater than 1, an optional Instance integer text box will display. If you want to query a specific instance of the attribute then populate this field with the instance number you are interested in, otherwise leave it blank. Leaving it blank will query against all instances of the attribute.
Data Type Operators Value
TEXT  =, !=, contains, starts with, ends with, in, not in, is, is not Free Form Entry
INTEGER, NUMBER  =, !=, >, >=, <,<=, is, is not Number Input Field
DATE  =, !=, >, >=, <,<=, is, is not Date Picker
TIMESTAMP  =, !=, >, >=, <,<=, is, is not Date/Time Picker
BOOLEAN  =, !=, is, is not True or False
 
  • When a Math Rule is added:
    • The math rule row will contain a free form text entry box where you will enter a math expression
    • Attributes with a Data Type of Number or Integer can be used in the expression
    • Note that queries that contain math expressions will not be available for selection in an Item Analysis - Differential Item Functioning analysis template in the comparisons test taker selection query section.
    • Allowable arithmetic operators are addition (+), subtraction (-), multiplication (*), and division (/)
    • The formatting of the attribute names should be as follows:
      • Standard Attributes - Standardized_Attribute_Name  (i.e., MCSCORE)
      • Grouped Attributes - Group_Attribute_Name:Standardized_Attribute_Name (i.e., POST_ADMIN_SCORES:SkmQnt00Enr)
      • Grouped Attributes with Cardinality(Instance) - Group_Attribute_Name:Standardized_Attribute_Name:Instance (i.e., CATEGORY_SCORES:CAT_SCORE:2)
      • GENERATED_SCORES Grouped Attribute - GENERATED_SCORES:Standard Attribute[Score Name or Score Code]:SCOREVALUE (i.e., GENERATED_SCORES:NAME[TOTMCNA]:SCOREVALUE)
    • The format of the expression will follow standard order of operation math syntax
      • Example 1:  GENERATED_SCORES:NAME[TOTMC]:SCOREVALUE + GENERATED_SCORES:NAME[TOTMCNW]:SCOREVALUE >=3
      • Example 2: (GENERATED_SCORES:NAME[R3_NA]:SCOREVALUE + GENERATED_SCORES:NAME[R4_NA]:SCOREVALUE + GENERATED_SCORES:NAME[R5_NA]:SCOREVALUE) >=7
  • When more than one rule/ruleset/math rule row is added, select either the AND or OR button that is above the rule/ruleset rows (nothing is selected by default). This selection is required.
    • Only one math rule can be added to each of the rule or ruleset conditions. Once the row has been added the button will be disabled.
  • The query that is being built will automatically populate in the Query Expression section using the syntax it will be saved in to the database. 
Query Builder Examples

Query Builder in Add Query Modal with Rules and Rulesets

Query Builder in Add Query Modal with Rules, Rulesets with GENERATED_SCORES

Query Builder in Add Query Modal with Rule with an Instance and a Math Rule
 
  • When you have populated all required fields and finished building a query, select the Save button. Once the query is saved it will appear in the Queries table.
    • Note:  If an analysis should include all test taker records for a request context (program/test/admin/form) then create a query with a blank builder/expression. 
Query View, Details, Actions
In the Query table view you can see a list of queries for your program (sorted alphabetically by Name), view the details of each query, and perform actions (edit, duplicate, delete) on that query.

Note:
  • The initial view is pre-filtered to the program selected in the Global Navigation. Clearing the program filter will display the queries that are configured for the program(s) you have access to.
  • The Delete action is permission based. This option will be enabled if you are a Super Admin or PAR Admin.
  • The Edit, Delete, Duplicate actions are only enabled for the queries for the program selected in the Global Navigation. Otherwise, they will be disabled.
Query Table View

Query Details
To view the details and audit history for a saved Query, select the Name link. The details of the saved query, including  Analysis Template associations and the query expression will be on the Overview tab. To view the audit history of all user actions performed on the query expression, select the Audit tab. The Audit details can be exported as an Excel or CSV file by selecting Export List.

Overview
Query Details Overview

Audit
Query Details Audit

Edit a Query

1. Select the Edit button in the Actions column for the Query that you want to edit. The following fields are editable:
  • Description
  • Query Builder
2. Select the Save button to save your changes.

Duplicate a Query

1. Select the Duplicate button in the Actions column for the Query that you want to duplicate to the program that is selected in the Global Navigation. All fields are pre-populated with the duplicated query details and are editable.  
2. Select the Save button to save your changes.

Delete a Query

1. Select the Delete button in the Actions column for the Query that you want to delete. You will only be able to delete a Test Taker Selection query if it is not associated to an Analysis Template. If the query is not associated to an Analysis Template a confirmation message will pop-up. Select Yes to delete or No to cancel this action. The query will be removed from the Query table once deleted. If a query is associated to one or more analysis templates a Cannot Delete Query message will pop-up and the only option will be to close the message and disassociate the query from the Analysis Template and then go back and delete.   

 

Copyright 2024 by Educational Testing Service. All rights reserved.
All trademarks are the property of their respective owners.