You can embed a query that displays relevant statistics for the organization record. This can include inquiry and application counts by term, counting only those where the organization being viewed exists as a school on the record.

This article covers the creation of the query and the steps to embed that query on dataset records.

Try a Slate example 💼

Use Suitcase to import a ready-made example Organization Statistics query.

After import, walk through the steps outlined below to ensure the query is configured correctly for use in your database.

4735e06f-0c4e-47a9-9462-a745123c051f:slate-examples

Or, do it yourself

• Create a new Configurable Joins query eligible to be displayed on dataset records.

• Display appropriate term values.

• Add a parameter to pass the GUID of the current organization record into the embedded query.

• Build aggregate subquery exports to count relevant records.

• Associate the query with organization dataset records.

Step 1: Creating 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, select Other. In the text box that appears, enter "System". Select Other for the subfolder, and enter “Tab”.

5. Select Configurable Joins as the Type.

6. Select System as the Category.

7. Select Prompt as the query Base.
   

8. Select Save.

💡 Tip: Don’t see a subfolder option on the System folder?
Selecting the existing System folder from the select list will not display the subfolder list since the System folder does not normally allow subfolders. By following Step 4 and selecting "Other" as the main folder and manually typing "System" will bypass this limitation.

After saving the new query, upon reopening the Edit Query options the following warning may appear:

The selected folder does not support subfolders. The subfolder currently set will be removed upon saving and may result in the unintended activation of this resource, so you may want to change the folder if desired.

You may safely disregard this advice for this task. If you need to update settings within Edit Query, it will be necessary to reset the Folder to Other: “System” with subfolder Other: “Tab” before saving.

Step 2: Displaying 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 may be ignored in preview.

5. Click Preview Results. This will return a list of active term prompts that will be included in the final query.
   

Step 3: Adding a parameter

Because this will be displayed on organization dataset records, a parameter may be used to pass the GUID of the organization record currently being viewed into the embedded query. In later steps, you will use this parameter to limit subquery results to only count those person or applications records that are associated with the organization being viewed.

1. From the edit query interface, select Edit Web Service.

2. Enter the following in the Custom Parameters field:

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

   

3. Click Save.

Step 4: Building 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 when building person-based counts (prospect, inquiry), and the application-scoped field for application-based 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 is important to ensure 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 in organization statistics:

When counting applications with specific decisions, a join to Decisions in the aggregate count subquery can adversely affect the intended application count—because applications can have more than one decision, and by joining on the same layer as the application count you have essentially changed what you are counting.

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

After filtering exports by a parameter, the Preview Results will always return 0 for these counts as there is no value for the parameter in this context. To see the results as they would display for a specific organization record, use the breadcrumbs to navigate to the query run screen and enter the example organization GUID as the parameter @record. Then click Run Query.

Step 5: Associating 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. Go to Database → Fields.

2. Select New Field.

3. Configure the following settings:

   • Scope Category: Records

   • Scope: Organizations

   • ID: Provide a unique field ID such as org_stats_term.
     🔔 Do not use org_stats, which is already in use and reserved.

   • Name: Include an administrative-friendly and descriptive name (such as "Organization Statistics by Term"). This is the name that will appear as the title of the query results table on the organization dataset record.

   • Field Type: Dataset Row Query

   • Query: Select the desired query saved in the System / Tab folder. If the query is not listed here, return to Step 1: Creating the query and check that Sharing and Folder / Subfolder settings are correct.

4. Select Save.

5. Edit the field you just created. Under the Display tab, a Tab setting will now appear.

   • Tab: select Dashboard.

   • Set Order (optional). If there are multiple embedded dataset row queries on the organization record, the default display order will be alphabetical by name. Setting the order on these fields can be used to customize this display.

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