CUO Report Wizard

Builds SQL for dbo.ReportDefinition. Every script runs as a preview first (@commit = 0 inserts inside a transaction, shows the stored rows, then ROLLBACKs). Set @commit = 1 to actually persist.

You're working with a Paycheck (or any non-Invoice) category — reports are created with their subreports, and the parent↔subreport junction links are inserted.

You're working with the Invoice category — reports are parent-only: the subreport section is hidden and no ReportDefinitionSubReportDefinition link is inserted.

Step 1 · Category

• Paycheck (or any non-Invoice) → report with subreports. Switch via the Category control at the top of the page.
• Invoice → parent report only; the subreport section is hidden and no ReportDefinitionSubReportDefinition link is inserted. Switch via the Category control at the top of the page.

Step 2 · Choose mode

1. Copy existing — clone a report by its source title + category into a new title. Subreports are discovered and cloned automatically; names are rewritten Avionte → CUO, SPs normalized to CUO_…, and parent↔sub links recreated.Only the single parent report is cloned and renamed (Avionte → CUO, SP normalized to CUO_…) — no subreports and no junction links. New rows get DateModified = GETDATE(). Set @generateSP = 1 (default 0) in the generated script to also script out & (re)create the renamed CUO_ stored procedures from the source ones.
2. Create report — supply the parent title, stored procedure, SP SQL and ReportDefinitionXML. Paste by hand, or 📁 Browse folder to auto-fill everything. Add subreports as needed.The parent is inserted on its own — the subreport section is hidden.

Browse-folder layout

Browsing auto-fills the same fields you'd paste into:

<ReportFolder>/
  Parent.xml / .rdl          ← parent report (root-level)
  subreports/                ← each child report (any subfolder)
     ChildA.xml
  sp/                        ← stored procedures (filename = SP name)
     CUO_exp_Foo.sql

<ReportFolder>/
  Parent.xml / .rdl          ← parent report (root-level)
  sp/                        ← stored procedure (filename = SP name)
     CUO_exp_Foo.sql
  (no subreports/ folder — Invoice is parent-only)

The parent is the file named/pathed parent, else the shallowest XML. SP for each report is guessed by best name match (else the SP named in the XML); always editable. A Files detected table shows how each file was classified.

Buttons

• Generate Report SQL — the ReportDefinition INSERT (preview/commit) for the parent + subreports for the parent only.
• Generate SP Script — a standalone deployment script of just the stored procedures (parent + subsparent only).
• Copy Output — copies the Output SQL box to the clipboard.
• 🔍 RDL Viewer — opens the standalone RDL viewer in a new tab to inspect a report's layout, parameters and datasets.

Workflow

1. Confirm category (Paycheck) → pick mode → fill the fields (or browse).
2. Generate Report SQL, copy it, run in SSMS with @commit = 0 to preview.
3. Review the returned rows + parent↔sub links, then set @commit = 1 and re-run to persist.
4. Review the returned parent row, then set @commit = 1 and re-run to persist.