DocsOmniFi APIsChangelog
SkySparcContact UsLog In
Docs

Designing reports for Web execution

OmniFi Web runs and distributes reports designed in OmniFi for Excel, e-mails, attachments and file exports using one of two spreadsheet engines, chosen on the Settings page under Execution/Reports with the setting Spreadsheet engine:

  • Excel opens each report in Microsoft Excel on the server.
  • Headless opens each report without Excel, inside OmniFi.

By default, the a Headless spreadsheet engine is used to run the workbooks, generate PDF attachments and flat export files, producing results that are often indistinguishable from running through the Excel engine. There are, however, some limitations.

Only Excel can run VBA macros, PowerQueries, OLAP pivot tables, ActiveX components and SmartArt.

Certain chart types render differently to PDF or cannot be rendered at all, and some narrow Excel features aren't supported. Design your report around these limitations for best possible results running in the Web portal.

Workbook and formulas

LimitationSolution
Workbooks with the old configuration sheet are refused. A report workbook that still carries OmniFi's old configuration sheet can't be uploaded or run.Open the workbook in OmniFi for Excel, which upgrades its configuration to the current format, save it, and upload it again.
External data isn't refreshed. External connections, Power Query, data-model and OLAP pivot tables aren't refreshed, and links to other workbooks keep the values they were saved with.Bring all data into the report through OmniFi queries.
Newer Excel functions may not calculate. Recent functions such as LET, LAMBDA, SEQUENCE and TEXTSPLIT may give #NAME?.Use the long-established worksheet functions in report formulas.
Dates can read and show differently than in Excel on some servers. On a server whose regional format has another language than its region, for example English (Sweden), the Headless engine reads TEXT() date codes in the format’s language, and shows dates with a time in a US style (3/23/23 11:24) in PDFs and CSV exports, unless it is told otherwise.Ask the administrator to set Spreadsheet culture to the region’s main language, for example sv-SE. The Headless engine then reads the same date codes as Excel and shows dates with a time as Excel does (2023-03-23 11:24).
Date codes of another language give #VALUE!. Letters that are date codes only in another language, such as YYYY on a Swedish server, give #VALUE!, where Excel prints the letters as they are. Spreadsheet culture doesn't change this.Use the date codes of the server's language. In Excel these formulas show the letters instead of a date, so they were never doing what they were meant to.
Some text that Excel reads as a date stays text. Excel turns a month name directly followed by digits into a date (MAR26 becomes 26 March of the current year, MAR32 March 1932), and a month followed by a number that can’t be a day into a month and year (1/0 becomes January 2000). OmniFi keeps these as text.If the report needs dates, have the query return dates. If the values are labels such as contract months, OmniFi already keeps them as they are; in Excel, format the output cells as Text.
Accounting format can show a minus sign. With the built-in Accounting format, negative amounts may show with a minus sign where Excel shows parentheses.If parentheses matter, use a custom number format that says so, for example #,##0.00;(#,##0.00).

Charts

LimitationSolution
3D charts come out poorly. They are drawn small and squashed, with overlapping labels.Use the 2D version of the chart.
Some chart types aren't printed. Pie-of-pie, bar-of-pie and surface charts are left out of the PDF. Map charts haven't been tested.Use other chart types, such as a pie or bar chart.
Charts with a date axis that runs from newest to oldest can't be drawn as they are. OmniFi then changes the chart, and as a last resort replaces it with a notice.Sort the chart's data from oldest to newest, or use a text axis.
Automatic axes can be scaled differently. An axis left to scale automatically can get a slightly different range, step or headroom than in Excel, and a date axis can start a month early.Set the axis minimum, maximum and major unit yourself wherever they matter.
Labels and legends can be laid out differently. Axis titles, zero-value data labels and pie-chart callout boxes can be missing. Labels may be staggered instead of slanted, chart titles centred, and legends arranged differently.Check every chart whose labels carry meaning. Keep label text short, and don't rely on zero-value labels or callouts.
Line charts break at missing points. A line breaks at every point that has no number, both #N/A and empty cells, where Excel can draw the line on to the next value.If a line must be continuous, give the chart a number at every point, or leave the missing points out of its data range.
Fills are simplified. Pattern fills and gradients with several colours are drawn in one colour. Zero values can show as a thin line, and the bars of small bar charts laid over a table come out longer.Use solid fills. Check small charts that are placed over cells.

Pivot tables

LimitationSolution
Pivot charts plot the grand total. A pivot chart plots the pivot table's grand total as an extra bar or point. Its title and legend text can differ slightly, and it has no field buttons.Turn off grand totals in a pivot table that feeds a chart.
Pivot formatting can be lost. Refreshing a pivot table may drop formatting you applied inside it.Use the pivot table's own style, or keep the formatting you depend on outside the pivot table.

Page layout and PDF

LimitationSolution
Some objects aren't printed. Slicers, SmartArt, ActiveX controls and threaded comments are left out of the PDF.Don't use them on sheets that are printed.
Medium and thick borders print thinner. Hairline borders print dotted. Thin borders are unchanged.Use thin borders, or shading, where an edge must stand out.
Row heights can change. A row's automatic height is worked out again from its content, so a row that was taller in Excel than its content needs comes out shorter.Set the row height yourself if the layout depends on it.
Fit-to-width pages can run over. A sheet set to fit one page wide can print about 1% larger than in Excel, so a full page can spill a row onto an extra page. Very wide sheets that print across several pages can break slightly differently.Leave a little room on full pages, or set the print scale yourself.
Columns wider than the page aren't cut at the margin. A single column wider than the page is printed out to the edge of the paper.Keep columns narrower than the page, or wrap the text.
Sheets without a paper size use the installation's paper size. Excel uses the server printer's paper. The Headless engine uses PDF default paper size (A4 unless the administrator changes it).Set the paper size on each sheet that is printed (Page Layout, Size).
Binary workbooks (.xlsb) print on Letter. In an .xlsb workbook, a sheet without a paper size of its own prints on Letter, not on PDF default paper size. The OmniFi log also doesn't name the fonts such a report uses that aren't installed.Save reports as .xlsx. If a report must stay .xlsb, set the paper size on each sheet that is printed.
Very large print ranges are refused. A PDF that would print more cells than the installation allows (PDF maximum cells, 5,000,000 by default) isn't created, and the task reports why.Set a print area on large sheets, so only the part that is meant to be read is printed.
Fonts that the server doesn't have are replaced. Fonts that come with Microsoft Office but aren't installed in Windows are replaced by another font. Text, row heights, column widths and page breaks then change.Use fonts that are installed on the server for all users, or ask the administrator to install the fonts your reports use. The OmniFi log names the fonts a report uses that aren't installed, except for .xlsb workbooks.

Pages that would come out completely blank are left out of the PDF, so a report can have fewer pages than with Excel. Pages with a header or footer are always kept. Don't rely on a PDF having exactly the same number of pages as with Excel.

CSV export

LimitationSolution
No blank lines at the top of a CSV file. Empty rows above the data that only carry a row height, a hidden flag or a border are left out of the CSV file, where Excel writes a blank line for each. The data lines are the same.Don't leave formatted empty rows above data that is exported to CSV, if whatever reads the file counts lines from the top.

Did this page help you?