Enterprise Reporting 101: Models, Grouping, Aggregation, and Pivots

Overview

SETWorks organizes reporting data in three layers — Entities, Models, and Reports — so that raw, messy system data can be turned into consistent, decision-ready output. For an IDD provider, this structure matters beyond convenience: reports built on shared Models return the same numbers every time they're run, which is what auditors, funders, and internal QA expect when reviewing Medicaid billing, service-hours documentation, or program utilization. Grouping, aggregation, and pivot transformations are the tools that make raw records — like individual service logs — usable at the level decision-makers actually need, whether that's total billable hours per program or a month-over-month comparison across service types.

ℹ️ Note: Terminology can be modified within SETWorks to better fit the needs of your organization. If the terminology in our articles are not the same on your site, talk to your local administrator to assist in translation.

What is Enterprise Reporting?

All users

Enterprise reporting in SETWorks is built on three layers that data moves through, from raw storage to a finished, readable output.

• Entities — Raw data sources. These represent real-world objects in the system, such as Individuals, Services, Users, or Employers, and are typically stored in the underlying databases.
• Models — Simplified, structured versions of Entities. A Model acts as a middle layer: it can pull from multiple Entities, apply calculations, filter out unnecessary data, and format everything into a useful structure.
• Reports — The final output. A Report takes data from one or more Models and presents it in a readable format — a table, chart, or dashboard — that supports a decision.

Example:

• Entity: A "Customer" table with raw data like Name, Age, Address, and Purchase History.
• Model: A "Customer Overview" Model that includes only Name, Age, and Total Purchases, filtering out unnecessary detail.
• Report: A "Customer Spending Report" that uses the Model to show the top 10 customers by total purchases.

Why Use Models?

Models keep reporting manageable by organizing raw data into a meaningful structure before it ever reaches a report. Instead of pulling directly from messy data sources, Reports reference Models to ensure consistency and efficiency across the organization.

Whether to apply calculations and filtering in the Model or directly in the Report depends on performance, reusability, and ease of maintenance — see the breakdown below.

When to Apply Calculations & Filtering: Model vs. Report

Apply in the Model — best for performance, reusability, and data integrity

• Consistency: If multiple Reports need the same calculation or filter, defining it once in the Model ensures every Report stays uniform.
• Performance optimization: Filtering and calculating at the Model level reduces data size early, so Reports load faster.
• Data security & access control: Filtering in the Model ensures users only see what they're allowed to, rather than relying on each Report to enforce security individually.
• Reusability: A well-structured Model can support many Reports without repeating the same calculation or filter logic in each one.
• Complex business logic: If a calculation spans multiple Entities (for example, aggregated revenue from different data sources), the Model is the right place to handle it.

Example: Calculating "Total Revenue per Customer" and filtering out customers with no purchases in the last 12 months belongs in the Model — that way, every Report using customer data stays consistent.

Apply in the Report — best for flexibility and per-report customization

• Ad-hoc analysis: One-off calculations that won't be reused elsewhere.
• User-driven filtering: When different users need different views, such as filtering sales by region dynamically in a dashboard.
• Lighter Models: If a Model is shared across many Reports, keeping it lean and applying filters at the Report level preserves flexibility.
• Quick experimentation: Testing different ways to display data before committing changes to the Model.

Example: A temporary filter like "Show sales only from Q1 2024" — while other Reports still need full-year data — is better applied at the Report level.

Note: As a general rule of thumb, if a calculation or filter will be reused across multiple Reports, build it into the Model. If it's specific to one Report, apply it in the Report.

Grouping & Aggregation

What is Grouping?

Grouping organizes data into categories so it can be analyzed at a higher level instead of reviewing individual records one by one.

Example: A dataset of clients receiving services might include Client ID, Program Type (Residential, Day Program, Therapy), Service Date, and Service Hours. Grouping by Program Type lets you see the total number of clients in each program type rather than listing every client individually.

What is Aggregation?

Aggregation applies a calculation — sum, count, average, and so on — to grouped data. Using the same dataset, grouping by Program Type and aggregating could produce:

• Total Service Hours provided per program
• Average Service Hours per client
• Count of unique clients per program

This turns raw records into answers to questions like "How many total service hours were provided in the Residential Program last month?" or "What is the average number of therapy hours per client?"

Putting It Together

Raw data (before grouping & aggregation):

After grouping by Program Type and aggregating Total Service Hours:

Example — a simple billing report: To report monthly Medicaid billing for IDD services, you could group by Program Type, sum the service hours per program, multiply by the billing rate per hour, and calculate total revenue per program.

Note: As a rule of thumb — reusable, standardized aggregations belong in the Model; flexible, ad-hoc, or user-specific groupings belong in the Report.

Pivot Transformations

A pivot transformation reshapes data by converting rows into columns, making it more readable and easier to analyze in Reports.

• Before pivoting: Data is stored in a long format, where each row represents an individual record.
• After pivoting: Data is transformed into a wide format, where grouped values become new columns.

Example: pivoting client service hours

Raw data (before pivoting):

This long format is useful for detailed logs but makes it difficult to compare service types side by side.

Transformed data (after pivoting Service Type into columns):

Benefits of pivoting:

• Easier analysis: Compare service hours across different types at a glance.
• Better visualization: Useful for building charts and summaries.
• Simplifies reporting: See all data in one row per client and month, instead of filtering through many rows.

Related Articles

• Creating and Managing Reports in the Report Utility
• Creating and Managing Models in the Report Utility
• Creating and Managing Charts in the Report Utility

Frequently Asked Questions

Q: Should I filter out old or inactive records in the Model or in the Report? It depends on reuse. If every Report built on this data should exclude the same records (for example, individuals discharged more than 12 months ago), apply the filter in the Model so it's consistent everywhere. If only one specific Report needs that view, apply it there instead.

Q: Can more than one Report use the same Model? Yes — that's the main advantage of Models. A single, well-structured Model can feed many Reports without repeating the same calculations or filters in each one.

Q: What's the difference between grouping and aggregation? Grouping organizes records into categories (like Program Type). Aggregation applies a calculation — sum, count, average — to each of those groups. You typically group first, then aggregate.

Q: Do I have to pivot my data before building a Report? No. Pivoting is optional and used when you want to compare categories side by side in a single row, such as comparing service types by month. Long-format data is still useful for detailed logs and audits.

Q: Why does this matter for Medicaid billing? Grouping and aggregating service hours by program, then applying the billing rate, is how raw service logs become the total revenue figures used in Medicaid billing reports. Keeping those calculations consistent in a shared Model helps ensure every billing report reflects the same underlying logic.