--- title: "Organization Statistics Dataset Row Query" slug: "organization-statistics-dataset-row-query" tags: ["Includes Suitcase ID"] updated: 2026-02-27T19:23:08Z published: 2026-02-27T19:23:08Z --- > ## Documentation Index > Fetch the complete documentation index at: https://knowledge.technolutions.net/llms.txt > Use this file to discover all available pages before exploring further. # Organization Statistics Dataset Row Query You can embed a query that displays relevant statistics for the [organization record](/v1/docs/organization-contacts-dataset-overview). 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. [![](https://cdn.us.document360.io/cd8ea7a6-07f3-4846-a554-627ac016d3e3/Images/Documentation/OrgStats_FeederSchool.png)](https://cdn.us.document360.io/cd8ea7a6-07f3-4846-a554-627ac016d3e3/Images/Documentation/10841787872667.png) #### Try a Slate example 💼 Use [Suitcase](/v1/docs/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. ```SuitcaseID 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**. [![](https://cdn.us.document360.io/cd8ea7a6-07f3-4846-a554-627ac016d3e3/Images/Documentation/Screenshot 2026-02-20 at 1.26.36 PM.png)](https://cdn.us.document360.io/cd8ea7a6-07f3-4846-a554-627ac016d3e3/Images/Documentation/10842927494043.png) 8. Select **Save**. > [!NOTE] > **💡 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. ![](https://cdn.us.document360.io/cd8ea7a6-07f3-4846-a554-627ac016d3e3/Images/Documentation/OrgStats_PreviewResults.png) ## 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: ```plainText ``` [![](https://cdn.us.document360.io/cd8ea7a6-07f3-4846-a554-627ac016d3e3/Images/Documentation/Screenshot 2026-02-20 at 1.02.50 PM.png)](https://cdn.us.document360.io/cd8ea7a6-07f3-4846-a554-627ac016d3e3/Images/Documentation/10843654804123.png) 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: ProspectsInquiriesApplicationsAdmitted Applications [![](https://cdn.us.document360.io/cd8ea7a6-07f3-4846-a554-627ac016d3e3/Images/Documentation/Screenshot 2026-02-20 at 1.34.09 PM.png)](https://cdn.us.document360.io/cd8ea7a6-07f3-4846-a554-627ac016d3e3/Images/Documentation/10846382734747.png) [![](https://cdn.us.document360.io/cd8ea7a6-07f3-4846-a554-627ac016d3e3/Images/Documentation/Screenshot 2026-02-20 at 1.35.29 PM.png)](https://cdn.us.document360.io/cd8ea7a6-07f3-4846-a554-627ac016d3e3/Images/Documentation/10846355841051.png) [![](https://cdn.us.document360.io/cd8ea7a6-07f3-4846-a554-627ac016d3e3/Images/Documentation/Screenshot 2026-02-20 at 1.37.48 PM.png)](https://cdn.us.document360.io/cd8ea7a6-07f3-4846-a554-627ac016d3e3/Images/Documentation/10846358895899.png) ![](https://cdn.us.document360.io/cd8ea7a6-07f3-4846-a554-627ac016d3e3/Images/Documentation/Screenshot 2026-02-20 at 1.49.10 PM.png) 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**. [![](https://cdn.us.document360.io/cd8ea7a6-07f3-4846-a554-627ac016d3e3/Images/Documentation/OrgStats_PreviewByRecord_renamed.png)](https://cdn.us.document360.io/cd8ea7a6-07f3-4846-a554-627ac016d3e3/Images/Documentation/10875187320731.png) ## 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](/v1/docs/organization-statistics-dataset-row-query#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: [![](https://cdn.us.document360.io/cd8ea7a6-07f3-4846-a554-627ac016d3e3/Images/Documentation/OrgStats_ExampleData.png)](https://cdn.us.document360.io/cd8ea7a6-07f3-4846-a554-627ac016d3e3/Images/Documentation/10841787872667.png)