Produce PDF reports (Xreport)

Spark can produce a PDF for selected worksheets. This is useful if calculations need to be included in a document as the final output. Spark is NOT designed to be a replacement for a document processing solution.

Map the report

Syntax: Xreport_<parameter>

1. Define the Print Area for the target sheets.

   • Print areas can include multiple pages.

   • Ensure that each Xreport worksheet has a worksheet scope variable called Print_Area.

   • It is suggested to add an extra row and column of padding around any cells with large text.

   • Please do not include any Xinputs in the worksheet - these will not be rendered correctly.

   • Spark does not support Print_Area ranges that are dynamically defined using formulas.

2. Rename the worksheet(s) to include the Xreport prefix. The name must match the above syntax exactly.

3. Upload the service to Spark and view the API Tester.

4. In the API request Field view, there will be a list of all the Xreports that have been defined in the workbook.

5. Next to the Report Name field, there is a File Name field, which corresponds to the FileName parameter of the API request. Enter the desired filename here (customfilename.pdf in the example above) to customize the filename of the resulting PDF. If no filename is provided then a UUID is used for the filename.

6. Click Submit to make the API request.

7. A hyperlink to the resulting PDF report can be found by expanding the response Reports section. The link can also be found in the JSON view and Raw view.

8. It is strongly recommended to inspect the PDF to ensure the contents match the intended outcome.

Supported fonts

Xreport supports the following fonts:

• AR PL UMing (Chinese)

• Angsana (Thai)

• Arial (Default font)

• Garuda (Thai)

• Halant (Devanagari script)

• HK Grotesque

• Montserrat

• Noto Sans CJK Regular (Chinese/Japanese/Korean)

• Open Sans

If Spark detects any font other than the ones listed above, it will use the Arial font.

Example of Xreport mapping

This service can be uploaded to Spark to use as a simple example.

30KB

Xreport.xlsx

Open

Exclude certain rows in the report

It is common to have Excel output content that may vary in length depending on the amount of data and number of calculations. If the content is shorter, it may be desirable to exclude extra empty pages at the end of the output.

Whether a row is included in an Xreport can be controlled by inserting a column within the Print Area with the heading Hide in Report Output. Within this column a value of FALSE will exclude that row from being included in the report.

This service can be uploaded to Spark to use as a simple example.

29KB

Xreport Multipage Dynamic.xlsx

Open

Include charts with Xreport PDF documents

Not all chart types are supported by Spark.

Xreport is able to include charts into the published PDF documents. To make the data series for a chart visible, the data series must reference data that is within the Xreport worksheet's defined Print_Area.

Control how Xreport sheets are processed

Execute API (v3) request_meta xreport_options includes additional parameters to control how Produce PDF reports (Xreport) are produced. The default settings are:

{ "produce_pdfs": true, "multiple_docs": true, "page_numbers": true }

• By default, a Spark service with Xreport will return a PDF document on each execution. However, this may not be desirable for a more rapid response or if the PDF document is not required. Whether a PDF is returned is determined by the value of produce_pdf.

• If a workbook has multiple worksheets tagged as Xreport, multiple_docs controls whether Spark will return one combined PDF or separate PDF documents for each Xreport worksheet.

• page_numbers controls whether the default page numbers will be included into the PDF documents.

Change images displayed based upon an input

It may be desirable to change a particular image such as a logo upon different Xinput values. This file offers an example of how it can be done.

• Make sure that all the formulas that resolve the image are within the Xreport worksheet PrintArea named range.

36KB

Xreport Dynamic Images.xlsx

Open

Troubleshoot Xreport inconsistencies

We have observed that saving Excel workbooks using different Windows Display Scaling settings (e.g. 100%, 125%, 150%, etc., see Display settings in Windows) can lead to slightly different margin widths. When uploaded to Spark, this may lead to Xreport not rendering as intended by the user.

If you are experiencing margins shift between different iterations of a Spark service, check that all contributors are using a consistent Windows Display Scaling setting.