Parity for Reports and Data Apps
Updated
IntermediateWhat you'll learn
~50 min- Inventory a report's datasets from its definition file (RDL, the XML a reporting server stores a report in) without rendering it, and say what that inventory cannot see
- Prove a rebuilt report's dataset returns the legacy rows by comparing both result sets as multisets (counting duplicates), column values converted to text by exact type
- Show with real output that a view built on SELECT * changes the report's shape without anyone touching the report, and tie the fix to lesson 24's schema contract
- Decide what to compare after the dataset matches, and why rendered output comes second
Lesson 24 put rpt_DailyIntake on its dependency map with one warning: it is a view that selects *, so the table under it is frozen. This lesson rebuilds the report that reads that view. A report is the dependent that makes the least noise when it breaks: nothing throws, a column is simply missing or a total is a little off, and someone finds out at a monthly review. So the proof has to be a test, and it has to run on the rows, not on a picture of them.
Before the detail — Artifact: modernization/reports.md (one row per report dataset: command text, parameters, objects read, owner), a dataset-equivalence test that compares the legacy query and the rebuilt query on the same database, and a shape check on the view the legacy report reads. Acceptance test: the rebuilt dataset returns the legacy rows; a boundary fault puts one row only in the new result; a column added to dbo.Intake changes the view’s shape only at sp_refreshview and the shape check names it. Status: reusable guidance; comparing the dataset before the render is this module’s method, not a published standard (the legacy-stack facts file marks report equivalence testing as unverified).
A dataset is the case rows a report reads, so running it against a restore handles CUI (controlled unclassified information; in a commercial shop, customer PII under contract) as lesson 24’s callout describes. The agent in this lesson reads report definitions, query text and the output of the comparison. Give it no database credentials and paste no row into a prompt: the tests below print rows only from the synthetic rehearsal database.
What goes wrong, and where it lands
| Problem | The pattern | Where your platform lesson lands it | You decide |
|---|---|---|---|
| Nobody has a list of reports, so one is rebuilt and four are forgotten | inventory every dataset from the report definition files, statically | the report server’s files are read from a copy of the content store (the report server’s own catalog database); the inventory itself needs no cloud | who owns each report, and which reports retire instead of being rebuilt |
| The rebuilt query “looks the same” | compare the two result sets as multisets, values converted to text by exact type | the comparison runs on the restore from lesson 24 on any of 34, 35 or 36, since it needs only SQL Server | which parameter sets are the golden ones (a month-end, a leap day, an empty day) |
The report reads a view on SELECT * | a shape check on the view, and lesson 24’s contract freezing the table | the contract check runs in the pipeline you chose in lesson 37 | when the freeze lifts: after the report names its columns and the positional readers are gone |
| The render differs, and nobody can say whether it matters | compare the dataset and the totals first; compare the render only to confirm layout | where the new report runs is a hosting decision (status box) | whether layout-level differences need a named sign-off |
The agent’s first move: read, do not run
Plan the change with your agent
The first job is an inventory, and it needs no database. Open your agent in the project root, switch to Plan mode (Shift+Tab, /plan, or the mode picker), and ask it to plan the report dataset inventory from the .rdl definitions:
Inspect legacy/reports/*.rdl and src/modernization/reports/rdl.ts read-only, and plan modernization/reports.md to inventory every report dataset and record what static extraction cannot see. Ask me anything unclear before writing files.What to check in the plan before you approve
- The inventory reuses
src/modernization/reports/rdl.ts(rather than writing a second parser) and connects to no database and renders no report. - For every dataset in
legacy/reports/*.rdl,modernization/reports.mdrecords the report, dataset name, data source, command type, command text, parameters, tables/views/procedures named, and whether it usesSELECT *. - Commented-out datasets are ignored, and items the static extractor cannot inspect (subreports, embedded
<Code>blocks, expression-built queries, shared datasets, and report-server email/file subscriptions) are listed inunknowns.md(U-80,U-81).
If the agent’s plan treats an embedded Visual Basic <Code> block as harmless formatting and omits it from unknowns.md, correct it before approving:
Record any <Code> block and any subreport reference as an unknown in unknowns.md (U-81) because it can change rendered values per row.- Typed in the terminal:
claude - Mode:Plan mode
- You type in the session: Plan first, read-only: inventory the datasets in fixtures/reports/DailyIntake.rdl with the extractor and tell me what it cannot see.
- The tool replies: DailyIntake | Text | dbo.rpt_DailyIntake | @ReportDate | SELECT * CaseTypeTotals | StoredProcedure | dbo.usp_CaseTypeTotals | @ReportDate | - WaitingOver30 | Text | dbo.Intake,dbo.IntakeAudit | | - subreports: SupervisorSummary embedded code: true Not inventoried (unknowns): the SupervisorSummary subreport is a separate file I have not been given (U-81); the report's <Code> block runs on every row of the render and my regex does not read it (U-81); whether anyone subscribes to this report by email or file share is held by the report server, not the definition (U-80). The commented-out dataset 'Retired' is ignored.
- You type in the session: Run the dataset-equivalence tests against the rehearsal database.
- The tool replies: equivalent: {"shape":[],"diff":{"onlyInLegacy":[],"onlyInNew":[],"matched":4}} boundary fault: {"onlyInLegacy":[],"onlyInNew":[{"row":["6","TANF","Open","2026-11-04 00:00:00.0000000","2","1","22"],"count":1}],"matched":4} type fault: ["SubmittedAt: type datetime2 -> datetime"] rows only in legacy: 4 only in new: 4 The rebuilt dataset matches on 4 rows. The two plants are caught: a <= next-midnight boundary adds case 6 to the new result, and a datetime where the legacy column is datetime2 changes the type and every value's text.
- You type in the session: Now the trap. Add a nullable Channel column to dbo.Intake and show me the view's columns before the change, after it, and after sp_refreshview.
- The tool replies: view columns before ALTER : CaseId,CaseType,Status,SubmittedAt,HouseholdSize,DaysWaiting,Score view columns after ALTER : CaseId,CaseType,Status,SubmittedAt,HouseholdSize,DaysWaiting,Score change: [] view columns after refresh : CaseId,CaseType,Status,SubmittedAt,HouseholdSize,DaysWaiting,Score,Channel change: ["+ Channel"] dataset compare after refresh: ["Channel: missing from the new dataset"] The migration changed nothing the report could see. The refresh, which any deployment of the view can trigger, did. This is the case lesson 24's contract blocks: dbo.Intake is frozen because rpt_DailyIntake reads SELECT *.
Illustrative session; exact output varies by tool and agency configuration.
The extractor is deliberately small. Here is its core:
REFERENCE CODE src/modernization/reports/rdl.ts (excerpt): static RDL dataset and subreport inventory open reference hide reference Reference implementation from gov-dev-reference-app; open to inspect or compare with what your agent builds.
// src/modernization/reports/rdl.ts (excerpt)export function inventory(rdl: string): Inventory { const xml = rdl.replace(/<!--[\s\S]*?-->/g, ''); // a commented-out dataset is not a dataset const datasets: Dataset[] = []; for (const m of xml.matchAll(/<DataSet\s+Name="([^"]+)"\s*>([\s\S]*?)<\/DataSet>/g)) { const query = tag(m[2], 'Query') ?? ''; const raw = tag(query, 'CommandText'); if (raw === undefined) continue; // a shared-dataset reference has no command text here const cmd = text(raw).trim(); // ... selectStar: /\bselect\s+(?:top\s*\(?\s*\d+\s*\)?\s+)?(?:\w+\.)?\*/i.test(cmd), }); } return { datasets, reportParameters: [...xml.matchAll(/<ReportParameter\s+Name="([^"]+)"/g)].map((p) => p[1]), subreports: [...xml.matchAll(/<Subreport\s+Name="([^"]+)"/g)].map((p) => p[1]), hasEmbeddedCode: /<Code>[\s\S]*?\S[\s\S]*?<\/Code>/.test(xml), };}An RDL file is XML, so its query text and parameters can be read as text, and the regex parser is acceptable for an inventory on three conditions the code states. It ignores XML namespaces, so it would also match an element with the right name in the wrong place. It does not evaluate expressions: a command text that starts with = is a query built at run time and is reported as an expression, not as SQL. And it does not read what it counts but cannot understand: subreports (other report files that this one embeds), embedded Visual Basic code, and shared datasets (datasets stored on the server rather than in the file). Those become unknowns.md rows, not facts. A real XML parser would remove the first limit; the other two stay, because they are about what the file does not contain.
The rules this lesson adds, as rows of modernization/rules.md:
| ID | Rule | Citation | Side effects |
|---|---|---|---|
| R-80 | the daily report’s rows are the cases whose SubmittedAt falls on @ReportDate, excluding status Withdrawn and keeping a NULL status | legacy/reports/DailyIntake.rdl:DailyIntake; view:dbo.rpt_DailyIntake | none |
| R-81 | a report dataset names its columns; it never selects * from a view or table that a contract freezes | decisions/D-01-shared-database.md:4 | none |
The NULL-status clause is the kind of rule nobody wrote down: Status <> 'Withdrawn' alone drops rows whose status is NULL, so the view spells out the second half and a rebuild has to as well. Unknowns: U-80, whether anyone subscribes to the report (a schedule that emails or files a rendered copy); U-81, what the subreport and the embedded code do.
Dataset equivalence, first
The dataset is the only part of a report that is data. Layout, fonts and pagination are presentation, and everything in them is computed from the dataset. If the rebuilt report’s query returns the same rows, the totals and the groupings on the page can differ only through a layout bug; if it does not, no amount of rendering will tell you why.
Plan the dataset comparison with your agent
Stay in Plan mode and ask your agent to plan src/modernization/reports/dataset.ts so both the legacy and rebuilt queries are compared on the same database:
Plan src/modernization/reports/dataset.ts with compareDatasets() to run the legacy and rebuilt report queries with identical parameters, convert columns to lossless text by exact SQL type, and diff both shape and multiset rows. Ask me anything unclear first.What to check in the plan before you approve
- Column names and types are read from SQL Server via
sys.dm_exec_describe_first_result_setrather than guessed from JavaScript values. - Each query is wrapped unchanged as a derived table with every column converted to lossless text by exact type (
style 2for money,style 121for date/time,style 3for float,style 1hex for binary), throwing an error on any type without a lossless conversion. - Shape differences (missing, extra, retyped, or reordered columns) are reported separately from row differences, and rows are compared as multisets (
onlyInLegacy,onlyInNew, andmatchedcounts) so duplicate rows from a fan-out join are caught.
If the first plan compares rows using a JavaScript Set or lets the driver return Date objects for datetime2 columns, steer it before approving:
Convert every column to text inside SQL with style 121 for dates, and compare rows as a multiset with duplicate counts rather than a Set. REFERENCE CODE src/modernization/reports/dataset.ts (excerpt): lossless SQL type conversion and multiset row diff open reference hide reference Reference implementation from gov-dev-reference-app; open to inspect or compare with what your agent builds.
// src/modernization/reports/dataset.ts (excerpt)export function asText(c: Column): string { const col = `[${c.name}]`; const style = AS_TEXT[c.type]; if (style === 1) return `CONVERT(varchar(max), CAST(${col} AS varbinary(max)), 1)`; if (style !== undefined) return `CONVERT(varchar(60), ${col}, ${style})`; if (AS_IS.has(c.type)) return `CAST(${col} AS nvarchar(max))`; throw new Error(`${c.name}: type ${c.type} has no lossless text form here; add one and a test before trusting a match`);}// ...// The dataset's own query becomes a derived table, so the report's SQL runs unchanged apart from one trailing semicolon, and every column leaves as text.export function normalizedQuery(query: string, cols: Column[]): string { return `SELECT ${cols.map((c) => `${asText(c)} AS [${c.name}]`).join(', ')} FROM (\n${singleStatement(query)}\n) AS dataset`;}// ...// A multiset, not a set: two identical intake rows on one side and one on the other is a difference.export function diffMultiset(legacy: Row[], next: Row[]): Diff { const tally = (rows: Row[]) => { const m = new Map<string, number>(); for (const r of rows) { const k = JSON.stringify(r); m.set(k, (m.get(k) ?? 0) + 1); } return m; }; const a = tally(legacy), b = tally(next); const diff: Diff = { onlyInLegacy: [], onlyInNew: [], matched: 0 }; for (const k of new Set([...a.keys(), ...b.keys()])) { const x = a.get(k) ?? 0, y = b.get(k) ?? 0; diff.matched += Math.min(x, y); if (x > y) diff.onlyInLegacy.push({ row: JSON.parse(k), count: x - y }); if (y > x) diff.onlyInNew.push({ row: JSON.parse(k), count: y - x }); } return diff;}Two choices carry the weight. Exact-type text is lesson 24’s lesson applied to a second job: a driver that hands back a JavaScript date has already rounded a datetime2 to milliseconds, so a rebuilt query that casts to datetime would pass a naive comparison. Here it does not, as the type-fault run shows below. The multiset matters because a report is a list, not a key lookup: a join that fans out duplicates a case, and a comparison that puts rows in a set sees nothing wrong. Compare by value with counts and the duplicate is a row “only in new” with count: 1.
Two limits. Wrapping the query as a derived table means every column needs a name and the query cannot carry an ORDER BY (sorting is presentation here, and the multiset ignores order); one trailing semicolon is stripped, and text holding more than one statement is refused with an error, so compare each statement as its own dataset; a dataset that depends on a sort for correctness, such as a running total, needs its own check. And the comparison needs the same database for both sides. Comparing a legacy dataset on one server with a rebuilt one on another is a different test, and lesson 24’s intact check has to pass first.
The SELECT * trap, proved
Lesson 24 says that SQL Server fixes a view’s column list when the view is created, so a new column on the table is invisible to the report until the view is refreshed. Here it is, with output. The reference environment test in tests/env/reports.env.test.ts from gov-dev-reference-app adds a nullable column and reads the view’s own column list at each step:
// tests/env/reports.env.test.ts (excerpt) it('the SELECT * trap: a new column on dbo.Intake does not reach the view until sp_refreshview', async () => { const before = await viewColumns(pool, 'dbo.rpt_DailyIntake'); await pool.query('ALTER TABLE dbo.Intake ADD Channel varchar(10) NULL'); const afterAlter = await viewColumns(pool, 'dbo.rpt_DailyIntake'); await refreshView(pool, 'dbo.rpt_DailyIntake'); const afterRefresh = await viewColumns(pool, 'dbo.rpt_DailyIntake'); // ... expect(afterAlter).toEqual(before); expect(shapeChange(before, afterRefresh)).toEqual(['+ Channel']);The run printed the three states you saw in the session above: no change after the ALTER, + Channel after sp_refreshview. Nothing in the migration touched the report, and the report’s shape changed anyway, on whatever day someone refreshed the view or deployed it again. The view’s contents did not change either; only its columns. That is why the shape belongs in the test next to the rows, and why R-81 asks the rebuilt dataset to name its columns: the rebuilt query above lists seven columns and keeps returning those seven after the refresh. The comparison shows the new state honestly (Channel: missing from the new dataset) because the legacy dataset has grown and the rebuilt one has not.
The reference shape check in src/modernization/reports/shape.ts from gov-dev-reference-app is three small functions:
// src/modernization/reports/shape.ts (excerpt)export async function viewColumns(pool: sql.ConnectionPool, view: string): Promise<string[]> { const r = await pool.request().input('v', sql.NVarChar(300), view) .query('SELECT name FROM sys.columns WHERE object_id = OBJECT_ID(@v) ORDER BY column_id'); return r.recordset.map((x: { name: string }) => x.name);}// ...export const shapeChange = (before: string[], after: string[]): string[] => [ ...after.filter((c) => !before.includes(c)).map((c) => `+ ${c}`), ...before.filter((c) => !after.includes(c)).map((c) => `- ${c}`),];What the schema contract protects here
The contract from lesson 24 is what keeps the trap from firing in production. db/schema-contract.json freezes dbo.Intake because rpt_DailyIntake reads SELECT *, so a migration that adds Channel fails the contract check before it merges. The two lessons divide the work. The contract stops the change at the table. The shape check and the dataset comparison tell you what the report sees when a change gets past it, including changes the contract does not model, such as someone refreshing a view. When the rebuilt report names its columns and the export job’s positional readers are gone, the freeze lifts through a reviewed change, and R-81 is the reason that change is allowed.
Rendered output, second
Compare the render only after the dataset matches, and compare less than you would like. A pixel diff of two rendered reports fails on font substitution, a different page-break position, and anti-aliasing, none of which a case worker cares about, so teams switch it off after a week of noise. This lesson builds no render comparison, for that reason. What survives as a signal:
- The report’s totals and group counts, taken from the render or its export (CSV, Excel) and checked against the same sums over the dataset. This catches a grouping or a filter set in the layout rather than the query.
- Row counts per page group and the first and last row of each, for a report with a long detail section.
- A named person looking at the layout once, against the legacy render for the same golden parameters. Layout is a judgment, and the sign-off belongs in the rulings file from lesson 25 like any other mismatch.
Embedded code and subreports in the definition (U-81) are where a render can differ while the dataset matches, which is why the inventory lists them and the extractor does not pretend to read them.
Prove it
Three acceptance tests, each with the fault it must catch. The environment test creates and drops its own reports database in the local SQL Server container: M14_ENV=1 npx vitest run tests/env/reports.env.test.ts. The unit tests run anywhere: npx vitest run tests/unit/reports.test.ts.
Inventory. The unit tests read fixtures/reports/DailyIntake.rdl. Expected: three live datasets, the commented-out one ignored, SELECT * flagged on DailyIntake only, <> readable in the CDATA (text the XML parser would otherwise treat as markup) dataset, and the subreport and embedded code reported. CDATA text stays literal, so a quoted & inside it is not turned into &; entities are decoded only outside CDATA. The run passed 16 of 18 unit tests.
Equivalence. The rebuilt dataset returned the legacy rows (matched: 4, nothing on either side). Plant the boundary fault, SubmittedAt <= DATEADD(day, 1, @ReportDate) in place of the date equality, and the same test fails with this diff output in its message:
"matched": 4, "onlyInLegacy": [],- "onlyInNew": [],+ "onlyInNew": [+ {+ "count": 1,+ "row": [ "6", "TANF", "Open", "2026-11-04 00:00:00.0000000", "2", "1", "22" ],Case 6 was submitted at exactly midnight the next day. A report that says “today’s intakes” would have shown it on the wrong day. Plant the type fault, CAST(SubmittedAt AS datetime), and the run reports SubmittedAt: type datetime2 -> datetime and four rows only on each side, because the stored .1234567 and the cast’s .123 are different text.
Shape. The trap test above passes: unchanged after ALTER, + Channel after refresh. The environment tests ran 4 of 4 green, and the one planted red shown above was produced by editing the rebuilt query in the test file, then restoring it. These ran against a synthetic seven-row database in a container, not a restore, not a report server, and not a rendered report; the rendered comparison is described, not run.
The rebuilt report's dataset query returns the same rows as the legacy one, and the totals in the render are the same. A week later a column appears on the legacy report. What explains it, and what would have caught it?
Why does compareDatasets convert every column to text by exact type instead of comparing the values the driver returns?
Availability, authorization, agency approval, region support, and price are five separate facts. SQL Server Reporting Services (SSRS, Microsoft’s on-premises report server that runs RDL reports): Microsoft’s consolidation FAQ says that starting with SQL Server 2025 on-premises reporting services consolidate under Power BI Report Server (PBIRS) and no new versions of SSRS will be released; SSRS 2022 is supported through January 11, 2033; PBIRS is described as a superset of SSRS and existing RDL reports are described as fully compatible with it; there is no in-place upgrade from SSRS, and Microsoft publishes a migration guide for the report server installation (consolidation FAQ, updated 2026-10-19). PBIRS uses Microsoft’s Modern Lifecycle and can be used by SQL Server 2025 Standard and Enterprise licensees. Azure Government: Power BI and Power BI Embedded appear in the audit-scope table (audit scope); PBIRS is on-premises software, so it has no cloud-region row. Reporting targets on AWS GovCloud and Google Assured Workloads were not researched for this module, so this lesson names none. Recheck the cited source and your agency’s authorization boundary before you rely on any of it.
Practice status — proving a rebuilt report returns the legacy rows
| Practice | Status | Also called |
|---|---|---|
| inventory report datasets from the definition files | common baseline | report catalog; report inventory |
| compare the dataset before the render | strong optional | data reconciliation; result-set diff |
| multiset comparison with exact-type text conversion | strong optional | row-level reconciliation |
| shape check on a view built on SELECT * | strong optional | schema drift check |
| contract freezing the table a report reads by position | strong optional | database contract test |
| pixel comparison of rendered reports | emerging | visual regression testing; screenshot diff |
Scale: required | common baseline | strong optional | reference-shop (seen only at organizations that publish their own practice) | emerging
Key takeaway
A report is a query with a layout on top, so prove the query first. Inventory every dataset from the definition files and write down what the inventory cannot see. Run the legacy and rebuilt queries on the same database, convert every column to text by exact type, and compare the rows as multisets with the shape reported beside them. Treat a view on SELECT * as a moving shape, and let lesson 24’s contract hold the table still until the report names its columns. Compare the render last, on totals and by a named reviewer, because pixels measure fonts. Lesson 38 lists these tests by name in the capstone’s evidence.
- LEADERSHIP DECISION
- a rebuilt report does not replace the legacy one until its dataset matches on the golden parameter sets, with every unmatched row ruled on by a named owner
- PRACTITIONER ACTION
- inventory the report definitions, run the dataset comparison and the shape check in CI, and rewrite the report to name its columns
- SUCCESS MEASURE
- audit finding avoided: no report whose totals changed with nobody having changed the report
Legend: LEADERSHIP DECISION = what a leader decides · PRACTITIONER ACTION = what the engineer does next · SUCCESS MEASURE = how you know, in calendar time, assessor-hours or cost, contract or revenue risk, or audit finding avoided