Exopen Report 6 Report Developer Manual

Introduction

Terminology

Sheet functions

Exopen Report 6 uses the Excel built-in function CHOOSE to specify the Exo…-functions in in the Excel sheets, e.g.

For more details, see the section ExoFunctions in EOX6 further down in this document.

Store functions

In Exopen Report 6 the Exo…Store functions need to call a stored procedure, which in turn requires an User Defined Type (UDT) set up to define the table-valued parameter. All the rows in the function input area are sent to the procedure at once, and in the procedure you can define how the data is to be handled.

See also the section Creating store functions and associated procedure and UDT section under the Examples section further down in this document.

Security Functions

By default, every report requires an ExoProtect function to be present. This function contains a hash value of the report’s functions and query areas to prevent inadvertent and unauthorized modification of the report functionality and queries. The hash value of the function is updated when publishing the report, or when re-running the Function Wizard on it.

For those who create reports frequently, it can be convinient to enable the Ignore ExoProtect setting for the user so one doesn’t have to update the ExoProtect function constantly during the report creation process.

Administrative Features

Use the menus under Admin to manage e.g.


Examples

Creating store functions and associated procedure and UDT

The following example demonstrates how the Store functions work. In the example we create a database containing a table, an User Defined Type (UDT), and a stored procedure. And we create a report that can read and write data from that table.

This example requires an instance of Microsoft SQL Server where you can create and modify databases.

  1. Connect to the database instance with SQL Server Management Studio and create a new database with the name ExampleSimpleBlog

  2. Create a table

  3. Create a table-valued User Defined Type (UDT)

  4. Create a Stored Procedure

  5. Add a connection in Exopen Report to this database.

  6. Add a function for ExoClearArea in cell C20.

    This function is for clearing the input/output range before running the Load function.

    =CHOOSE(1;"ExoClearArea";;$C$26:$J$100;;;;;;;;;;;)

  7. Add a function for ExoListLoad in cell C20.

    This function retrieves the data from the database table Blog and outputs it to the output area of C25:I100 on the sheet. The first row in the area is the column names.

    =CHOOSE(1;"ExoListLoad";;"ExampleSimpleBlog";$C$1:$C$19;$C$25:$I$100;"";"VERTICAL";"FALSE";"TRUE";"FALSE")

  8. Add a function for ExoListStore in cell E20.

    This function calls the stored procedure named in the query area, BlogCreateUpdateDelete in this case and sends the data from the input area C26:J100 as a table valued variable typed as BlogCreateUpdateDeleteType to the procedure. The proceure then performs an INSERT, UPDATE, or DELETE based on the data.

    The delete functionality is in this case achieved by having an explicit delete flag not part of the Store function’s output. Set a 1 in the J column for the rows you want to delete.

    =CHOOSE(1;"ExoListStore";;"ExampleSimpleBlog";$E$1:$E$19;$C$26:$J$100;BlogCreateUpdateDeleteType)

Using this example Store report

  1. Start by running Load Data so any data that is in the database table will be loaded into the report. The first time there is no data in the table so the output area will remain empty with the exception of the column headers.

  2. On an empty row in the input area Enter a title in column D and some content in column E.

  3. Run Store Data.

  4. Run Load Data. Now the column C on the row has the ID value the row got in the database table, and column F has the creation timestamp, and column G has the username of the user who ran the Store Data that inserted this row.

  5. Change the title and/or value in columns D and/or E, and run Store Data again.

  6. Run Load Data. Column H has the modification timestamp, and column I has the username of the user who ran the Store Data that modified this row.

  7. Set a 1 in column J on the row.

  8. Run Store Data.

  9. Run Load Data. Now the row has been deleted.

  10. Now try adding multiple rows, editing, and deleting so you get a feel for how this works.


ExoFunctions in EOX6

General information

All ExoFunctions use the Excel function CHOOSE to contain the function arguments. The first argument (Index_num) is always 1 and the second argument (Value1) is always the name of the ExoFunction. This way Excel will always display the name of the ExoFunction in the cell, and it allows for EOX6 to identify the CHOOSE function as an ExoFunction. Third argument (Value2) and onward are specific to the ExoFunction in question.

Usually the third argument (Value2) is used for function versioning, that is, if breaking changes (e.g. function parameters have been added, removed, or changed meaning) have at some point been introduced to a function, the value in this argument can be used to have older behaviour. Most functions do not use this argument and in that case it should just be left empty.

If the function uses a connection to a data source, usually the connection name is placed in the fourth argument (Value4).

The most convenient way of creating these Exo-functions is by using the Function Wizard in Exopen Report 6. The following list specifies the function arguments if you are editing the Exo-functions using Excel’s function editor.


Load Functions

ExoColumnLoad

CHOOSE function arguments for ExoColumnLoad

ExoColumnReplaceLoad

CHOOSE function arguments for ExoColumnReplaceLoad

ExoGroupedListLoad

CHOOSE function arguments for ExoGroupedListLoad

ExoListLoad

Performs a query to the data source and outputs the result set to a list on the sheet.

CHOOSE function arguments for ExoListLoad

ExoMatrixLoad

CHOOSE function arguments for ExoMatrixLoad

ExoMatrixMultiLoad

CHOOSE function arguments for ExoMatrixMultiLoad

ExoRowLoad

CHOOSE function arguments for ExoRowLoad

ExoSingleRecordLoad

CHOOSE function arguments for ExoSingleRecordLoad


Store Functions

ExoColumnStore

CHOOSE function arguments for ExoColumnStore

ExoListStore

CHOOSE function arguments for ExoListStore

ExoMatrixStore

CHOOSE function arguments for ExoMatrixStore

ExoRowStore

CHOOSE function arguments for ExoRowStore


Query Functions

ExoFormatValue

CHOOSE function arguments for ExoFormatValue

ExoInfoSet

CHOOSE function arguments for ExoInfoSet

ExoInPermission

CHOOSE function arguments for ExoInPermission

ExoInQuery

CHOOSE function arguments for ExoInQuery

ExoInRange

CHOOSE function arguments for ExoInRange


Input Functions

ExoInputCalendar

CHOOSE function arguments for ExoInputCalendar

ExoInputFilter

CHOOSE function arguments for ExoInputFilter

ExoInputFilterMulti

CHOOSE function arguments for ExoInputFilterMulti

ExoInputText

CHOOSE function arguments for ExoInputText


Other Functions

ExoActiveUser

CHOOSE function arguments for ExoActiveUser

ExoClearArea

CHOOSE function arguments for ExoClearArea

ExoDrillDown

CHOOSE function arguments for ExoDrillDown

ExoFormatArea

CHOOSE function arguments for ExoFormatArea

ExoHide

CHOOSE function arguments for ExoHide

CHOOSE function arguments for ExoHyperLink

ExoListSummation

CHOOSE function arguments for ExoListSummation

ExoProtect

CHOOSE function arguments for ExoProtect

ExoPublish

CHOOSE function arguments for ExoPublish

ExoTimeStamp

CHOOSE function arguments for ExoTimeStamp

ExoVerify

CHOOSE function arguments for ExoVerify

ExoVerifyQuery

CHOOSE function arguments for ExoVerifyQuery


Compatibility functions for migration from EOX4

The Exopen Report 4 Import functions cannot be converted automatically to the Exopen Report 6 Store functions. When using the EOR6 Convert EOX4 to EOX6 functionality, any EOX4 Import functions will be converted to these compatibility functions.

From a performance point of view, it is recommended to manually convert these functions to corresponding Store functions.

Note: Some of the function options may not be implemented.

ExoColumnImport

CHOOSE function arguments for ExoColumnImport

ExoMatrixImport

CHOOSE function arguments for ExoMatrixImport

ExoRecordImport

CHOOSE function arguments for ExoRecordImport

ExoRowImport

CHOOSE function arguments for ExoRowImport

ExoSingleRecordImport

CHOOSE function arguments for ExoSingleRecordImport