REPORTER

Spreadsheet

A Spreadsheet generates an Excel document based on an Excel template document containing Reporter tags, parser expressions, some Excel functions, and/or table expressions.

A separate license is required to use Spreadsheet and Spreadsheet view. In addition, you must have a Corporater Reporter license to run Spreadsheets.

Spreadsheets are simpler than the other reports. Primarily, the following is supported:

#{} tags - calculate functions
${} tags - resolve as in regular reports; if the result is a number, the cell becomes numeric
#set works as #set in regular reports; a #set statement is in effect until another #set or #clear statement in a later cell changes or undoes it ;
#clear works like #clear in regular reports; a #clear statement is in effect until another #set- or #clear-statement in a later cell changes or undoes it

Cells are resolved row by row, not column by column

${Table.t.123} will add the table with id123 to the sheet, starting with the cell the tag is in

In a Spreadsheet, it is only possible to add tables that are defined as tables in BMP, not build new tables that get information from different sources.

The following is not supported:

- #if
- #repeatElement
- #repeatPeriod
- #insert
- #findq
- #find

The following limitations apply:

General

Excel documents cannot reference external resources, i.e. other documents, data sources, scripts, URLs, etc.
Non-English users must follow these two rules when using functions/formulas:
- Functions must be entered in English (Example: use the English “=SUM()” not in other languages, e.g. the German “=SUMME()”.
- Always use a comma “,” to delimit parameters in Excel functions, even when the language setting in Excel dictates otherwise. Some language settings use ‘;’ semicolon as the parameter separator in Excel.

- See the list of unsupported Excel functions below.
Excel macros will not be processed but they will remain "as-is" in generated reports if present in a template.

Some examples of unsupported Excel functions:

ASC

BAHTTEXT

CLEAN

CALL

CODE

CUBEKPIMEMBER

CUBEMEMBER

CUBEMEMBERPROPERTY

CUBERANKEDMEMBER

CUBESET

CUBESETCOUNT

CUBEVALUE

EUROCONVERT

INFO

JIS

PHONETIC

RTD

SQL.REQUEST

YIELD

YIELDDISC

Creating a Spreadsheet

To create a Spreadsheet, right click in the Reporter model panel in Configuration Studio and choose Add -> Category. Then right click the Category and choose Add -> Spreadsheet.

Properties

General

Name - the name "Spreadsheet" is generated automatically but it may be changed
Description - an optional text describing the object

Audit

Modified by - the name of the last user to make changes to the object.
Last modified - the date and time when the object was last changed.
Created by - the name of the user who created the object.
Created - the date and time when the object was created.

Access control

Read accessors - click the Edit button () and authorize one or more users and user groups to view the object on Web.
Add accessors - click the Edit button() and authorize one or more users and user groups to add new objects to the object on Web.
Write accessors - click the Edit button() and authorize one or more users and user groups to edit the object on Web.
Delete accessors - click the Edit button() and authorize one or more users and user groups to delete the object on Web.
Ownership - click the Edit button () and grant ownership of the object to one or more users and user groups.

Users and user groups who own the object have full access rights to it.
Override access profile - select for the object's access control setting to outrank the object's corresponding access settings in profiles.

Spreadsheet templates are edited in your editor and then imported into the Spreadsheet in Reporter. Right click the Spreadsheet object in the Reporter model panel and choose Edit template from the menu. An empty template (Excel file) opens. Now you You can configure your Spreadsheet template by adding Reporter tags, functions, table expressions, and/or Excel functions. When you have created and saved your report template, right click your report in Reporter and select Import, or Ctrl+r, and choose the file containing you spreadsheet template. The spreadsheet is immediately ready to Verify and Preview.

When Reporter processes Spreadsheets, cells are scanned and resolved by rows. If a Spreadsheet contains both Reporter tags and Excel functions, the Reporter tags will be resolved first, before the Excel functions are resolved.

Reporter tags and Excel functions may not be used in the same cell. Instead, you should use tags in one cell and refer to that cell in an Excel function in another cell. Adding multiple tags on the same sheet is not recommended.