Exopen Report 6 uses the Excel built-in function CHOOSE to specify the Exo
…-functions in in the Excel sheets, e.g.
=CHOOSE(1;"ExoListLoad";;"Contoso";$A$1:$A$34;A40:AA1000;"";"VERTICAL";"FALSE";"TRUE";"FALSE")
For more details, see the section ExoFunctions in EOX6 further down in this document.
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.
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.
Use the menus under Admin to manage e.g.
Settings: logging, default start page, default connection, etc.
Connections: Define the connections to data sources.
Groups: Manage the groups that users can belong to.
Users: Manage users and set what groups they are members of.
Document Permissions: Manage which users have access to which reports.
Dimensions: Define logical hierarchies of data, e.g. Cost Centers
Objects: Define dimension entries, e.g. specific Cost Centers.
Object Permissions: Manage which users have access to which Objects.
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.
Connect to the database instance with SQL Server Management Studio and create a new database with the name ExampleSimpleBlog
Create a table
Create a table-valued User Defined Type (UDT)
Create a Stored Procedure
CREATE PROCEDURE [dbo].[BlogCreateUpdateDelete]
@EosTableVariable AS dbo.BlogCreateUpdateDeleteType READONLY,
@EosUserVariable AS NVARCHAR(100)
AS
BEGIN
SET NOCOUNT ON;
SET @EosUserVariable =
SUBSTRING(@EosUserVariable,
CHARINDEX('\', @EosUserVariable) + 1,
LEN(@EosUserVariable) - CHARINDEX('\', @EosUserVariable) + 1)
BEGIN /* MERGE statement for Insert/Delete/Update. */
MERGE dbo.Blog AS t
USING @EosTableVariable AS s
ON (t.id = s.id)
-- Insert new entries.
WHEN NOT MATCHED BY TARGET
THEN INSERT
(
title,
content,
created,
createdby
)
VALUES
(
s.title,
s.content,
SYSDATETIME(),
@EosUserVariable
)
-- Delete existing entries which have the Delete field set to 1.
WHEN MATCHED AND s.[delete] = 1
THEN DELETE
-- Only update existing entries if the title and/or content has actually changed.
WHEN MATCHED AND (t.title <> s.title OR t.content <> s.content)
THEN UPDATE
SET
t.title = s.title,
t.content = s.content,
t.modified = SYSDATETIME(),
t.modifiedby = @EosUserVariable
;
END /* MERGE statement for Insert/Delete/Update. */
END
Add a connection in Exopen Report to this database.
Start Excel, go to the Exopen Report tab, log in, and then under Admin, choose Connections.
Add a connection with the name ExampleSimpleBlog
whose connection string points to the above database.
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;;;;;;;;;;;)
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")
Insert the fhe function’s query in range C1:C19.
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)
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.
On an empty row in the input area Enter a title in column D
and some content in column E
.
Run Store Data.
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.
Change the title and/or value in columns D
and/or E
, and run Store Data again.
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.
Set a 1
in column J
on the row.
Run Store Data.
Run Load Data. Now the row has been deleted.
Now try adding multiple rows, editing, and deleting so you get a feel for how this works.
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).
=CHOOSE(1, "ExoListLoad",
… )
=CHOOSE(1, "ExoMatrixStore",
… )
=CHOOSE(1, "ExoClearArea",
… )
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.
CHOOSE
function arguments for ExoColumnLoad
1
"ExoColumnLoad"
CHOOSE
function arguments for ExoColumnReplaceLoad
1
"ExoColumnReplaceLoad"
CHOOSE
function arguments for ExoGroupedListLoad
1
"ExoGroupedListLoad"
Performs a query to the data source and outputs the result set to a list on the sheet.
CHOOSE
function arguments for ExoListLoad
1
"ExoListLoad"
VERTICAL
or HORIZONTAL
TRUE
or FALSE
TRUE
or FALSE
TRUE
or FALSE
CHOOSE
function arguments for ExoMatrixLoad
1
"ExoMatrixLoad"
CHOOSE
function arguments for ExoMatrixMultiLoad
1
"ExoMatrixMultiLoad"
CHOOSE
function arguments for ExoRowLoad
1
"ExoRowLoad"
CHOOSE
function arguments for ExoSingleRecordLoad
1
"ExoSingleRecordLoad"
CHOOSE
function arguments for ExoColumnStore
1
"ExoColumnStore"
CHOOSE
function arguments for ExoListStore
1
"ExoListStore"
CHOOSE
function arguments for ExoMatrixStore
1
"ExoMatrixStore"
CHOOSE
function arguments for ExoRowStore
1
"ExoRowStore"
CHOOSE
function arguments for ExoFormatValue
1
"ExoFormatValue"
CHOOSE
function arguments for ExoInfoSet
1
"ExoInfoSet"
CHOOSE
function arguments for ExoInPermission
1
"ExoInPermission"
CHOOSE
function arguments for ExoInQuery
1
"ExoInQuery"
CHOOSE
function arguments for ExoInRange
1
"ExoInRange"
CHOOSE
function arguments for ExoInputCalendar
1
"ExoInputCalendar"
CHOOSE
function arguments for ExoInputFilter
1
"ExoInputFilter"
CHOOSE
function arguments for ExoInputFilterMulti
1
"ExoInputFilterMulti"
CHOOSE
function arguments for ExoInputText
1
"ExoInputText"
CHOOSE
function arguments for ExoActiveUser
1
"ExoActiveUser"
CHOOSE
function arguments for ExoClearArea
1
"ExoClearArea"
CHOOSE
function arguments for ExoDrillDown
1
"ExoDrillDown"
CHOOSE
function arguments for ExoFormatArea
1
"ExoFormatArea"
CHOOSE
function arguments for ExoHide
1
"ExoHide"
CHOOSE
function arguments for ExoHyperLink
1
"ExoHyperLink"
CHOOSE
function arguments for ExoListSummation
1
"ExoListSummation"
CHOOSE
function arguments for ExoProtect
1
"ExoProtect"
CHOOSE
function arguments for ExoPublish
1
"ExoPublish"
CHOOSE
function arguments for ExoTimeStamp
1
"ExoTimeStamp"
CHOOSE
function arguments for ExoVerify
1
"ExoVerify"
CHOOSE
function arguments for ExoVerifyQuery
1
"ExoVerifyQuery"
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.
CHOOSE
function arguments for ExoColumnImport
1
"ExoColumnImport"
CHOOSE
function arguments for ExoMatrixImport
1
"ExoMatrixImport"
CHOOSE
function arguments for ExoRecordImport
1
"ExoRecordImport"
CHOOSE
function arguments for ExoRowImport
1
"ExoRowImport"
CHOOSE
function arguments for ExoSingleRecordImport
1
"ExoSingleRecordImport"