bi-export-plus

Real, binary .xlsx (and typed JSON) exports for the InterSystems IRIS BI/DeepSee
Analyzer — no more opening “Export to Excel” and getting an HTML table wearing an
.xls extension.

• Excel Workbook and JSON buttons appear right in the Analyzer’s own Export
  dropdown, next to the stock Excel/CSV options.
• A standalone REST API (/csp/bi-export/xlsx, /csp/bi-export/json) too, for
  pulling the same exports from scripts/automation without opening the Analyzer.

Try it now - no install, no existing IRIS needed

docker run -d -p 52773:52773 -p 1972:1972 ghcr.io/asinay/bi-export-plus:latest

Pre-loaded with Samples-BI and this
module - openpyxl included - so there’s real data to export immediately:

Building it yourself instead of pulling: docker compose up --build (see
https://github.com/asinay/bi-export-plus/blob/master/Dockerfile). Already running IRIS elsewhere? →
Try it in 2 minutes instead, or keep reading for the full
reference below.

The new buttons, right next to the stock Excel/CSV ones.

A genuine Excel Table - autofilter, sortable headers, a native Totals row.

An Info sheet with title/cube/filters, not squeezed above the data.

Requirements

• InterSystems IRIS with Embedded Python enabled.
• The Python package openpyxl importable
  from IRIS’s embedded Python (needed for the .xlsx path only; JSON export
  needs neither Python nor openpyxl).

Install openpyxl into IRIS’s embedded Python environment, e.g.:

/bin/irispython -m pip install openpyxl

Verify it from an IRIS terminal in any namespace:

Do ##class(%SYS.Python).Import("openpyxl")

If that errors, fix the Python/openpyxl setup before installing this module -
the xlsx export will fail at runtime otherwise.

If openpyxl turns out not to be importable at runtime anyway, every export surface
(Analyzer buttons, dashboard widget buttons, demo page, REST API) detects it and degrades
gracefully instead of producing a broken download - buttons gray out with an explanation
in their tooltip, and the REST API returns 503 with a plain-text reason. JSON export is
never affected either way, since it needs no Python.

Installation

Just want to try it out rather than add it to a real instance? See
Try it now with Docker above instead -
nothing below is needed for that.

Recommended: InterSystems Package Manager (IPM/ZPM). If this module has been
published to the IPM registry:

zpm "install bi-export-plus"

To install straight from a local clone of this repository instead (e.g. before
it’s published, or to run a modified copy):

git clone 

zpm "load /path/to/bi-export-plus"

Point this at the directory that contains https://github.com/asinay/bi-export-plus/blob/master/module.xml, not at the
https://github.com/asinay/bi-export-plus/blob/master/module.xml file itself - e.g. on Windows:

zpm:USER>load C:\path\to\bi-export-plus

IPM reads https://github.com/asinay/bi-export-plus/blob/master/module.xml, loads the classes under https://github.com/asinay/bi-export-plus/blob/master/src/, and
registers the /csp/bi-export web application for you. Take note of the
AfterInstallMessage it prints at the end - it points you at the two usage
options below.

Do $system.OBJ.Load("/path/to/bi-export-plus/https://github.com/asinay/bi-export-plus/blob/master/dist/BIExport-classes.xml", "ck")

Usage

Just want to see it work? Try the demo page

No pivot needed - BIExport.UI.DemoPage.cls is a self-contained form with a few
canned MDX queries and totals/aggregation toggles. Pick one, click a button, get a
real .xlsx/.json download, using the exact same code path as the buttons below:

/csp/<namespace>/BIExport.UI.DemoPage.cls

Option A: In-Analyzer export buttons

Open your pivots via BIExport.UI.Analyzer.cls instead of the stock
_DeepSee.UI.Analyzer.zen - same query params, just a different class name:

/csp/<namespace>/BIExport.UI.Analyzer.cls?CUBE=...

The page’s Export dropdown gains Excel Workbook and JSON entries next
to the stock Excel/CSV ones.

Do ##class(BIExport.Favorites).AddAnalyzerFavorite("/.pivot")

Option B: Standalone REST API

POST an MDX query to /csp/bi-export/xlsx or /csp/bi-export/json:

curl -u _system:PASSWORD -X POST \
  http://host:port/csp/bi-export/xlsx \
  -H "Content-Type: application/json" \
  -d '{
    "mdx": "SELECT [Measures].[Amount] ON 0 FROM [HoleFoods Sales]",
    "title": "Sales Report",
    "cube": "HoleFoods Sales"
  }' \
  -o report.xlsx

Supported request body fields:

rowTotalAgg/columnTotalAgg take the same values as this dropdown - no translation needed.

See sample output in https://github.com/asinay/bi-export-plus/blob/master/samples/.

Option C: Dashboard export

Two related pieces, for saved Dashboards rather than ad-hoc pivots:

Per-widget buttons. Same Excel Workbook/JSON buttons as the Analyzer, but on an
individual dashboard widget’s own Export dropdown. Existing widgets need to be converted
to the export-capable widget class once:

Do ##class(BIExport.DashboardTools).ConvertWidget("/.dashboard", "")

or, to convert every pivot-family widget on a dashboard in one call:

Do ##class(BIExport.DashboardTools).ConvertAllWidgets("/.dashboard")

RevertWidget(pDashboardFullName, pWidgetName) undoes a single conversion. New
dashboards can also just set a widget’s type to BIExport.UI.Widget.pivot directly in
the Dashboard Editor’s widget definition, instead of converting after the fact.

Whole-dashboard export. Open the dashboard via BIExport.UI.DashboardViewer.cls
instead of the stock _DeepSee.UserPortal.DashboardViewer.zen (same query params):

/csp/<namespace>/BIExport.UI.DashboardViewer.cls?DASHBOARD=...

then use Export Dashboard, in the small text-menu row at the bottom of the page
(next to Home/About/Logout) - it downloads one .xlsx workbook with one sheet per pivot
widget on that dashboard.

Try it against a real dashboard, using the Docker image’s
preloaded Samples-BI data - no conversion needed first, since whole-dashboard export
reads any pivot-family widget, stock type included:

http://localhost:52773/csp/user/BIExport.UI.DashboardViewer.cls?DASHBOARD=Basic%20Dashboard%20Demo.dashboard

Click Export Dashboard at the bottom - downloads a 3-sheet workbook (Info sheet plus
one sheet per pivot widget on that dashboard).

Known limitations

• Totals across mixed measures. If a row/column axis mixes different
  measures (e.g. “Amount Sold” and “Units Sold” side by side), rowTotals/
  columnTotals aggregate across them regardless of measure, mixing dollars
  and units - this matches the stock Analyzer’s own CSV/Excel exports, which
  call the same underlying IRIS API. Totals are only meaningful when an axis
  is entirely one measure.
• Nested column headers aren’t Excel Tables. A pivot with a dimension on
  Columns (e.g. Channel x measure) needs a true 2+ row merged header, which
  Excel’s Table feature can’t have (exactly one header row allowed) - that
  export keeps the merged header and skips the Table (no autofilter/banded
  rows), with a note on the Info sheet explaining why.
• Whole-dashboard export uses each widget’s SAVED state. It resolves every widget
  from its saved pivot definition, not whatever the dashboard viewer session currently
  has on-screen (unsaved filter changes, unsaved widget tweaks) - there’s no browser
  session available to the server-side export code. Save the dashboard first if you want
  those reflected. The per-widget export buttons don’t have this limitation - they run
  against the widget’s live, on-screen state.
• Whole-dashboard export uses generic row-header labels. With no live pivot table
  widget to query for row-axis captions in that path, every sheet’s row headers fall back
  to generic “Row 1”/“Row 2” labels rather than the dimension names a per-widget or
  Analyzer export shows.

License

MIT - see https://github.com/asinay/bi-export-plus/blob/master/LICENSE.