Excel: the analyst pane#

Early release. This feature is in early release, so its screens and behavior may change. Tell us what you think with Send feedback. See Early release features and Send feedback.

The analyst pane is the Reportworq task pane in Excel. You build a report on a worksheet, see it render in your own cells as you edit it, and keep it in the workbook so it refreshes later, on demand or on a schedule. This page covers what the pane is for, how large one report may get and what the messages mean, what happens when a job runs the workbook, and what is not in this release.

What it is for#

The pane is for authors. You sign in with an author account (or a system administrator account), pick a data model, and build the report in your workbook. The pane needs no license or role of its own: it asks only for your author rights, on a server whose license includes authoring or contribution. On a server licensed for neither, the add-in says "Excel Addin requires a Reporting or Contribution license." Reportworq has no "analyst" role in this release, and the name "analyst pane" describes what the pane is for, not who may open it. A sign-in without author rights does not open the pane: the add-in says "The Excel Addin requires the Author entitlement." Readers do not run live queries from Excel.

The pane takes the place of the Companion pane described in Excel: build and refresh reports. Your administrator can switch it off (see below), and a version of Excel that is too old gets the Companion pane instead.

Before you start#

• An author sign-in. The server also needs a Reporting or Contribution license (without one the add-in says "Excel Addin requires a Reporting or Contribution license."). You also need a data model with a Public view. The pane lists only Public views; a view set to Hidden or Disabled is not listed (see View visibility), or a saved cube connection (TM1, Adaptive, Vena or Pigment). See Data models.
• A recent Excel. The pane needs a version of Excel that supports ExcelApi 1.20 (Excel on the web, Windows or Mac). On an older Excel the Companion pane opens with the notice "This version of Excel does not support the Reportworq analyst pane. Update Excel to use it; until then, the Companion pane is shown."
• The pane switched on. It is on for everyone by default. A system administrator turns it off with Use the analyst pane in Excel under Settings > Configuration > Excel add-in; a change applies to panes opened afterwards. See Server configuration.

What you can build#

The same pane also runs inside the web report studio. See The Excel add-in and the studio. In Excel, the add-in's Reportworq tab has a New report group with List, Crosstab, Professional (dim), Chart and Build with AI.

Start a report from Home: pick a view in the model list, or choose a report type under "Or start from a report type". On a sheet with no reports Home offers the same types. These are the kinds you can build:

Professional reports are a licensed feature. In Excel the option is dim, with the reason "Professional reports aren't in Excel yet. Build them in the web report studio." In the web report studio, on a server whose license does not include them, it is disabled with the reason "Professional reports are coming soon." See Professional reports.

How large one report may get#

Very large reports are slow to write and hard to read in Excel, and the server holds each written cell in memory, so filter or limit rows where you can. So the pane limits one report in two steps: it asks before it writes a large report, and it refuses a report that is too large.

Both are administrator settings under Settings > Configuration > Excel add-in. A change applies to panes opened after it is saved. Cells are counted as the rows times the columns of the report the pane has built, checked before anything is written to your sheet.

When the pane asks#

A report between the two sizes waits. Its card in Home reads "Waiting to write", and a callout headed Large report appears on the card and at the top of the report's editor:

"Regional sales" will write 240,000 cells (12,000 rows by 20 columns). Large reports can take a minute to write and slow Excel while they do. Auto-update is off for this report, so it updates only when you press Refresh.

• Write 240,000 cells (the button shows the actual count) writes the report. Auto-update is then off for that report, so it refreshes only when you press Refresh.
• Cancel writes nothing. The card shows a "Not written" status, and the callout says "Regional sales" was not written because you stopped it before 240,000 cells were written, and that Refresh asks again. Ask again refreshes the report and asks again.

If the report is on another sheet, a short notice, "Large report waiting", tells you to open it.

When the pane refuses#

A report over the largest size writes nothing. Its status is Too big for Excel, and the callout says:

"Regional sales" would write 2,480,000 cells (124,000 rows by 20 columns). A report can write up to 2,000,000 cells. Add a filter, remove columns, or set a row limit in the report's settings, then refresh.

Your sheet is not changed. Choose how to make the report smaller:

• Limit to N rows. When a row limit would help, the callout offers it as a button. N is the number of rows that fits under the limit once the title, header and total rows are counted. Pressing it sets the report's row limit (for a crosstab, the rows it reads from the source), saves it and refreshes. You have answered the question for that report, so a limited report that is still above the Ask above size is written without asking a second time.
• Add a filter or remove columns, then refresh.

The button is not shown when no limit would help: not even one row fits, the limit would not be lower than the rows already read, or a crosstab filter is applied after the rows are read (a limit would then keep an arbitrary slice).

How many rows a report reads from its source#

Separately from the cell count, each list and crosstab reads a limited number of source rows.

• No list or crosstab reads more than 1,000,000 source rows. In the pane, a plain list with no limit of its own reads its first 10,000 rows and says so; Load all rows, or All rows under Rows read from the source in the report's settings, reads up to 1,000,000. A list that sorts, filters or ranks, and every crosstab, reads every row up to 1,000,000, and is not shown above that. Change the limit per report: Default (10,000), 1,000, 10,000 or All rows (up to 1,000,000), or type a number.
• A list that sorts, filters or ranks, and every crosstab on a data model, needs every row to be right, so a source with more than 1,000,000 rows is not shown, and the report tells you why and what to do. A plain list shows its first 10,000 rows (or the rows that fit on its sheet, if fewer) and says there are more.
• The cell limits apply on top of the row limit: a report of more than the largest report's cells (see above) is not written.
• A report whose author set a lower limit, or a plain list the 10,000-row default stopped, shows a Not every row callout in its editor, for example "This list read only the first 10,000 rows of its source." For a crosstab it adds that its totals cover only those rows. Load all rows reads up to 1,000,000 rows instead; if that read would be refused, the limit is kept and the pane says why.
• A read that takes longer than 120 seconds fails the report and asks you to add a filter or a lower row limit.

Where a report can sit#

In Excel for Mac, Excel on the web and Excel for Windows, a report can sit anywhere on its sheet, up to Excel's last cell, XFD1048576. In an older or unrecognized Excel client, a report must sit inside A1:AZ1000 of its sheet: the first 1,000 rows and the first 52 columns. In such a client, the pane does not check your cells past column AZ when a report deletes or hides whole rows, so keep your own content beside a report inside A1:AZ1000 there.

• A report is never moved for you. If a saved report lies outside the area the pane can hold, the pane leaves it exactly as it is, does not treat it as a report, and does not write over your cells.
• A write that would reach past the area is refused. In Excel for Mac, Excel on the web and Excel for Windows: "Reportworq can't write past the last cell Excel has (XFD1048576), and this needs ... Nothing was written. Make the report smaller, or move it up or left." In an older or unrecognized Excel client: "Reportworq can work only inside A1:AZ1000 of a sheet in Excel for now, and this needs ... Nothing was written. Move the report up or left, or make it smaller."
• A list that would run past the last row the pane can use draws the rows that fit and keeps its totals.
• In Excel for Mac, Excel on the web and Excel for Windows, a change that would push your cells past the last row or column of the sheet is refused before it moves your cells.
• In Excel for Mac, Excel on the web and Excel for Windows, the reports in one workbook can hold at most 5,000,000 cells together. A report that would take the workbook past that writes nothing, and the pane says what to do: limit the report's rows, show fewer members, or remove another report. A report near the largest size may need a workbook of its own. When the pane opens a workbook whose reports already pass that total, the reports past it are left as they are in Excel and not shown in the pane.
• In the rare case that the add-in changes while the pane is open to a version that works only in A1:AZ1000, the pane stops writing to the workbook and asks you to close and reopen the workbook. Nothing is written in the meantime.

If you open a workbook's reports in the web report studio, a report that reaches past row 1,000 or column AZ opens as view-only: the studio shows the first 1,000 rows and 52 columns, will not change it, and tells you to edit it in Excel.

Copying a sheet that holds a report#

A report is stored with its sheet, and Excel does not carry that storage along when you copy a sheet. The pane keeps a small hidden copy of each report's definition beside it, so it can recognize a copy.

• A copy of a sheet in the same workbook is normally recognized as its own report when the pane reads the workbook. It refreshes on its own, with its own id, and you do not need to do anything.
• A copy the pane cannot safely turn back into a report (for example the original was deleted, or the sheet came from another workbook without its layout) is left as plain cells. Nothing is written over it and it does not refresh in the pane.

Scheduled runs#

A workbook saved with analyst-pane reports can be used as a job's source report like any Excel workbook. On each run the job refreshes the workbook's lists and crosstabs where they sit, reading as the report's author, and then sends static copies. The job never prompts, so the Ask above question does not apply to it.

What a job does differently from the pane:

• The same largest-report limit applies, and a job never cuts a report. If a report would write more than the limit, that sheet fails with a message such as: "Nothing was distributed: "Regional sales" on sheet Sales would write 2,480,000 cells, over the 2,000,000-cell limit for one report. Add a filter or set a row limit in the report's settings." Fix the report and run again.
• A job ignores the pane's 10,000-row default. A report with no row limit of its own reads up to 1,000,000 source rows. A row limit you set on the report is honored.
• A report that cannot be read fails the run. If a stored definition is damaged, was edited by hand, or was saved by a newer version of Reportworq, the run fails and names the report, instead of sending the saved values as if they were fresh. Nothing is distributed.
• A report past Excel's last row (1,048,576) or column (XFD) fails the sheet, or one that grows past it as it refreshes: "has more rows than a job can write (row 1,048,576, column XFD). Set a row limit in the report's settings."
• Copies are prepared first. The job recognizes copied sheets the way the pane does, refreshes the ones it can rebuild, and sends the rest as saved values, with a note in the run log.
• Some report types go out as saved. Maps and AI insights are not refreshed by a job; charts are redrawn from the refreshed cells.
• Run the job on the same version or later. A workbook saved by this version of the add-in cannot be read by an older Reportworq server.

Differences between Excel clients#

Windows, Mac and Excel on the web all open the same pane and offer the same report types. The differences you may notice:

• Excel on the web takes a pane width of 350 to 500 pixels only.
• Excel for Mac, with another workbook in front, may show "Excel is busy with another workbook. Click into this workbook, then Retry." Click into the workbook and choose Retry.
• An older Excel gets the Companion pane, not the analyst pane.

What is not in this release#

• Bursting from the add-in is not available. Bursting stays in the web Job Editor.
• Professional reports need a license that includes them. Without it their option is disabled with the reason "Professional reports are coming soon."
• Readers cannot run live queries from Excel.
• Restore and Discard for a copy the pane set aside are not in the pane yet.

Related#

• The Office add-ins
• Excel: build and refresh reports, the Companion pane
• Server configuration, for the three analyst-pane settings