Worksheet output options#

Each worksheet you include on a distribution job's Reports step can carry its own Output Options, settings that control how that worksheet appears and behaves in the generated file. They are configured on the Reports step of the Distribution Job Editor, per included worksheet, alongside the worksheet's Parameters mapping.

These options apply to a single worksheet. Job-level packaging (collating and splitting) is set through Job options.

Before you begin#

• Include at least one worksheet on the Reports step. Until an Output Options entry is added, the worksheet is generated with default behavior.

Set the options#

On the Reports step, open a worksheet's Output Options panel and set any of the following.

When you set an option in the panel, it wins over the equivalent worksheet formula (RW.Tools.SheetName, RW.Tools.SheetColor, and RW.Tools.SheetSuppress; the short aliases RWSHEETNAME, RWSHEETCOLOR, and RWSHEETSUPPRESS also work). The formula is used only when the panel option is left unset.

Page breaks are not a per-worksheet Output Option. Control them from within the worksheet with the RWPAGEBREAK function. See General report and layout functions.

Shape the PDF bookmark tree and table of contents#

When a job's output is delivered as PDF, Reportworq builds a bookmark tree in the PDF sidebar. When you turn on Table of Contents in the PDF output options, it builds a matching contents page from the same structure, so anything you do to the bookmark tree shapes the table of contents the same way.

The tree has three levels:

• Level 1 is the root. It defaults to the job name.
• Level 2 is a report node under the root. It defaults to the report name.
• Level 3 is the leaf for each worksheet. It defaults to the worksheet name.

With no overrides, every worksheet sits under one root (the job) and one report node (the report), with the worksheet as the leaf, giving a clean single-root outline.

This same tree shows up in the output viewer. When a run also produces a PDF, the output viewer's HTML view reads this bookmark tree back out of the PDF and shows it as the sheet rail, titled Bookmarks, so a worksheet omitted from PDF bookmarks below is also left out of that rail.

Set the levels for a worksheet#

Each worksheet's Sheet name option carries all three levels. When PDF is one of the job's output formats, opening that option shows three fields:

• Level 1 (PDF bookmark root)
• Level 2 (PDF bookmark report)
• Level 3 (Sheet name), the worksheet's output name. This is the only field shown when PDF is not an output format.

Each field offers the same three modes:

• Do not modify, the default, leaves the level to inherit (see How worksheet order builds the tree).
• Literal Text lets you type the title, optionally with parameter tokens.
• Cell Reference reads the title from a calculated cell.

Set Level 1 and Level 2 to give a worksheet a custom root and report node; leave a level on Do not modify to inherit it.

The formula equivalent is RW.Tools.SheetName with one, two, or three arguments. The last argument is always Level 3; earlier arguments fill Level 2, then Level 1. Omit an argument to let that level inherit.

=RW.Tools.SheetName(Level3)                    ' sheet name only
=RW.Tools.SheetName(Level2, Level3)            ' report node and sheet name
=RW.Tools.SheetName(Level1, Level2, Level3)    ' root, report node, and sheet name
=RW.Tools.SheetName(, Level3)                  ' set the leaf; Level 2 cascades

If you set the levels in the panel, they win over the formula. See the RW.Tools.SheetName function.

How worksheet order builds the tree#

Reportworq builds the tree by walking the worksheets in output order and grouping adjacent worksheets that resolve to the same title:

• Consecutive worksheets that resolve to the same Level 1 title share one root.
• Within a root, consecutive worksheets that resolve to the same Level 2 title become siblings under one report node.
• A worksheet whose Level 1 differs from the worksheet before it opens a new root, so a PDF can have more than one root.
• A worksheet whose Level 2 differs opens a new report node.

A level left on Do not modify cascades: it inherits the last title an earlier worksheet set explicitly. If nothing earlier set it, Level 1 falls back to the job name and Level 2 to the report name.

Because grouping is by adjacency, two worksheets that resolve to the same title but are not next to each other form separate nodes. So order your worksheets first, then label them.

Worked example. Six worksheets in this output order, with these levels:

builds this tree, and the same table of contents:

FY25 Pack                 (Level 1 root)
    Summary               (Level 2)
        Group             (worksheet 1)
        Regions           (worksheet 2, Level 1 and Level 2 cascade from #1)
    Detail                (Level 2, worksheet 3 opens a new report node)
        North             (worksheet 3)
        South             (worksheet 4, Level 2 cascades from #3)
Appendix                  (Level 1 root, worksheet 5 opens a second root)
    Notes                 (Level 2)
        Cover             (worksheet 5)
        Glossary          (worksheet 6, cascades from #5)

Worksheets 1 and 2 are siblings under Summary because worksheet 2 inherits both levels from worksheet 1. Worksheet 3 sets Level 2 to Detail, opening a new report node, and worksheet 4 cascades into it. Worksheet 5 sets a new Level 1 (Appendix), opening a second root, and worksheet 6 cascades under it.

Leave a worksheet out with a section break#

To keep a worksheet's pages in the PDF but out of the bookmark tree and table of contents, select Omit this worksheet from the PDF bookmarks and table of contents in the Sheet name option, shown under PDF section break when PDF is an output format.

Use this for an intentionally blank divider page. Its pages still print, but the worksheet is left out of the bookmark tree and table of contents, and its pages are excluded from page numbering. The visible numbering stays continuous across it, so page 3 is followed by the next numbered page 4.

Tip: a blank divider sheet needs a print area. The Omit option only controls the bookmark tree, the table of contents, and page numbering. It never removes the page. A page disappears for a different reason: a worksheet that is both empty (no cell content) and has no print area set renders no pages at all, so it never reaches the PDF, Omit or not. To use a blank worksheet as a divider, set a print area on it (any range, such as A1). That guarantees it exports as one page, and Omit then keeps that page out of the bookmarks, table of contents, and numbering while the divider still appears.

The formula equivalent is an optional trailing FALSE on RW.Tools.SheetName:

=RW.Tools.SheetName("Divider", FALSE)    ' name the sheet and omit it
=RW.Tools.SheetName(FALSE)               ' omit it without renaming

The checkbox wins over the formula.

Upgrading from version 5? The Custom TOC Title and PDF Parent Bookmark settings are now part of the Sheet name option: a Custom TOC Title becomes Level 1 and a PDF Parent Bookmark becomes Level 2. Your existing settings carry over automatically.

Suppress a worksheet conditionally#

Sheet suppression deletes the tab from the output, it does not hide it, when its cell or RWSHEETSUPPRESS formula evaluates TRUE (a value of 1 or a boolean true). On FALSE the tab is kept.

A common pattern is a formula that keeps a tab only when it holds data, so recipients never receive an empty schedule. A worksheet is also auto-suppressed when it carries a provider report that returns no data.

When to use it: drop lookup or staging tabs so recipients see only presentation worksheets, or hide a schedule that has nothing to show this period.

How the run status is decided when sheets are suppressed#

A suppressed worksheet is not in the file your recipients get, so its calculation problems do not turn the step or the job to Warnings. If a lookup or staging tab that you suppress has a formula error or warning, the run still finishes as Success when everything on the delivered worksheets is clean.

• This applies to suppressed worksheets only. A worksheet that is merely hidden in Excel is still in the output, so its errors and warnings still count.
• A TM1 formula whose server is missing, on a suppressed worksheet, is treated the same way.
• Data source warnings that belong to a suppressed worksheet are not counted either: Vena warnings, and TM1 Quick Report warnings about invalid members ("Invalid Quick Report members on Worksheet ...").
• Nothing is lost. The step log (logreport.txt) lists the details at every log level, under the line "Information: N formula error(s) or warning(s) on worksheets removed by Sheet Suppression were not counted toward the status; the details are listed below." Data source warnings get a matching "data source warnings" Information line.

What still counts: List Report query warnings carry no worksheet, so they still make the run Warnings. A Quick Report or Universal Report that cannot run at all (for example, a title member that does not exist, or an axis with no valid member) still fails the report, because that happens before suppression runs.

When suppression removes every report#

If Sheet Suppression removes every worksheet of every report in a step, for example a single-tab report that has nothing to show for this slice, the step has nothing to produce or send. By default the run fails with "No reports included in job output", as in earlier versions.

If that outcome is expected for your job, open Job Options and tick Succeed when Sheet Suppression removes every report (unticked by default; see Job options). With it ticked, the step and the run finish as Success, no file is sent for that step, and the run's success notification goes out as usual. The step log says why in one line:

"Information: No reports were produced: Sheet Suppression removed every worksheet."

The run notification marks that step with "No reports were produced, so nothing was sent." In a burst, each slice is decided on its own. Anything that still counts keeps its status, so a List Report warning on the removed worksheet still makes the run Warnings.

The checkbox applies only when Sheet Suppression removed a worksheet that would otherwise have been delivered. A hidden lookup worksheet that stays in the report does not change that. If a report has nothing to output for any other reason, the run still fails with "No reports included in job output", even with the box ticked: for example, every included worksheet is hidden in Excel, or the only worksheet suppression removed was itself hidden.

If someone uses Release All on a run that produced no reports, nothing is sent, and the release step log says "Information: Nothing to release: no reports were produced."

Upgrading? Earlier versions counted calculation problems on suppressed worksheets. If you use QA history comparisons, a job whose status changes because of this (from Warnings to Success, or from Failure to Success once you tick Succeed when Sheet Suppression removes every report) is flagged as a difference against a baseline captured before the upgrade. Capture a new baseline once you have checked the result.

Send only on an exception (the exception check)#

The exception check turns a worksheet cell into a send gate for the whole output. The rule is an OR across every worksheet:

• If no worksheet defines an exception cell, the output always sends.
• If one or more worksheets define an exception cell, the output is delivered only when at least one of those cells, anywhere in the output, evaluates TRUE. If all of them are FALSE, delivery is suppressed.

When some outputs in a run are held this way, the job log and the run notification say so: a line such as "2 of 5 reports were held by the exception check and not sent.", and each held output is marked "[Not sent: exception check]". A held output is not a failure: a run whose every output is held finishes as Success, and the step log says "Information: No reports were sent: the exception check held every report." If the run is a Test run, or uses Hold for Delivery or QA test mode, nothing is sent for those reasons and the exception check is not named.

When to use it: send a variance pack only to the managers who are actually over budget. Put an exception cell on a validation tab that turns TRUE when a threshold is breached, and recipients whose numbers stay within tolerance receive nothing.

Notes and limits#

• Sheet tab color applies to Excel output; the bookmark levels (Level 1 and Level 2) and the PDF section break apply to PDF output. The options are format-relevant, and the bookmark fields appear only when PDF is an output format.
• Suppression removes the tab entirely. There is no keep-but-hidden state at this layer.

Related topics#

• Choose the source workbook and worksheets
• Job options
• Parameters reference