It is possible to embed a query that displays relevant statistics for the organization record being viewed on an organization dataset record. For example, this can include inquiry and application counts by term, counting only those where the organization being viewed exists as a school on the record.

Step One: Create the query

This query is intended to display counts by entry term. For this reason, the query is built to return one row per prompt value using the Lookup Prompts query base.

1. Select Queries / Reports on the top navigation bar and select Queries.

2. Select New Query and give the query a Name.

3. Under Sharing, select Share query with other users with the query and query base permissions.

4. Under Folder, assign the query to the folder Other, and enter "System". Select Other for the subfolder, and enter "Tab".

   Important!

   Follow the above step even if the System folder already exists. Selecting the existing System folder from the dropdown will not prompt the Subfolder dropdown to display, as the System folder does not normally allow subfolders. (You can safely disregard the error message for this task.)

   Returning to edit these query settings will cause the subfolder to be removed. If it is necessary to make changes here in the future, repeat these steps to place the query in a folder of Other: "System" with a subfolder of Other: "Tab" and save.

5. Select Configurable Joins as the Type.

6. Select System as the Category.

7. Select Prompt as the query Base.

8. Select Save.

Step Two: Display appropriate term values

Configure the query to display only the term prompts that are relevant to include on organization dataset records.

1. Filter by Prompt Key to find only term prompts.

2. Filter by Prompt Active Status of active. This way, only terms active in the database will display.

3. Export the Prompt Value. As this will be the label for your rows, rename the export Term.

4. Sort by Prompt Value. Note: sort is used when the query is run but ignored in preview.

5. Click Preview Results. This will return a list of active term prompts.

   

Step Three: Add a parameter

A parameter is used to pass the ID of the record currently being viewed into the embedded query. You will use this to limit subquery results to only count those record related to the organization being viewed.

1. Click Edit Query.

2. Click Edit Web Service.

3. Enter the following in the Parameters field:

   <param id="record" type="uniqueidentifier" />

4. Click Save.

Step Four: Build aggregate subquery exports

Create exports to count relevant records (prospects, inquiries, applications) based on the value in a particular term field. Each export built will appear as a new column in the embedded query. 

1. Create a new subquery export.

2. Set the Output to Aggregate with a function of Count.

3. Join to Field Values and filter by the ID of the entry term field.

   • Person or Application? Use the person-scoped field for person counts (prospect, inquiry), and the application-scoped field for application counts.

4. Join to Person if you used a person-scoped field. If you used an application-scoped field, Join first to Application, then to Person. This join path matters to return accurate results.

5. Join to the relevant school:

   • Use School by Level of Study, Rank configured to High School rank 1, if these counts should consider only the most recent high school.

   • Use School by Rank Overall configured to rank 1 to consider the most recent school of any level.

6. Join to Organizations.

7. Filter by Organization GUID and enter the parameter configured previously: @record

8. Click Save.

Here are four common examples of aggregate exports to include:

Prospects

Inquiries

Applications

Admitted Applications

Important!

When counting applications with specific decisions, a join to Decisions in the aggregate count subquery will affect the count because applications can have more than one decision.

For this reason, the join to Decisions should be made in a nested subquery filter to find applications where a certain decision exists. See the Admitted Applications example above.

Step Five: Associate the query with dataset records

The final step is to create a new custom field that allows the query to be associated with the Organization dataset.

1. Click Database in the top navigation bar and select Fields.

2. Insert a new field with the following settings on the Details tab:

   • Scope Category: Records

   • Scope: Select the Organizations dataset with which to associate the query.

   • ID: Provide a unique field ID.

   • Name: We suggest providing a descriptive name for the displayed rows, such as “Applications List.” The name will also appear as the title of the table.

   • Field Type: Select Dataset Row Query.

   • Query: Select the desired query saved in the System / Tab folder.

3. Click Save.

4. Edit the field you just created:

   • Under the Display tab, a Tab setting will now appear. Set the tab to Dashboard.

Tip

If multiple dataset row queries are added to this dataset, use the Order setting in each of the fields to control the order in which the queries display on organization records.

The query will now display as a table on the dashboard tab of an organization record: