The CREFLEXSQL formula is used to build dynamically sizing reports, or “Reflex reports”, based on a Stored Procedure (“Stored Proc”) in a relational database.
The datasource needs to be added to the Stored Proc servers in Server Configuration.
Stored Procedures need to be authorised through Authorised Procs.
Currently the only supported relational database is Microsoft SQL Server.
CREFLEXSQL (Datasource, StoredProcName, Parameters, DataRange, FormatRange, LeftOffset, TopOffset, RightOffset, BottomOffset, MaxRows, MaxColumns, NullsAs)
Specify the relational databases’ datasource name (defined in Server Configuration) for connection.
Specify the name of the authorised stored procedure that is to return the data. SQL schemas are allowed in the name.
For example: admin.sp_test
Specify the parameters of the stored procedure. For example,
If the parameter
@loginId is defined in the stored proc, the currently logged-in user ID will be passed to the procedure.
Value can be empty (“”) if no parameters are to be submitted.
If you need to reference a string of parameters in another excel cell you’ll need to escape the first
@ symbol with
'. For example:
Data Range :
Specify the data range of the report.
Format Range :
Specify the format range of the report.
Left Offset :
Specify the left offset of the report.
Top Offset :
Specify the top offset of the report.
Right Offset :
Specify the right offset of the report.
Bottom Offset :
Specify the Bottom Offset of the report.
Max Rows :
Specify the max row number of the report.
-1 represents unlimited rows.
Max Columns :
Specify the max column number of the report. .
-1 represents unlimited columns.
Nulls As :
Specify the value that will replace nulls.
CREFEXSQL is useful for bringing back transactional information into reports which is not (or does not belong) in an Analysis Services cube.
- Retrieving a list of locks currently present in the database.
- Retrieving a history of changes to a document from a document management database.
- Retrieving an audit trail of changes made to a particular customer address.
In the case where the stored procedure has a parameter with the datatype of date, a date can be passed through as 1-Jan-2010. Unlike text parameters, it is not neccessary to wrap this parameter value in single quotes.
A common requirement is to have an audit log of changes to a particular cube. Through the use of additional processes, these changes can be written as a transactional record to table
fctAuditLog (or any other table).
The following authorised proc (authorised through the process Authorised Procedures)
spGetAuditHistory, will bring back transactional records for the table
create proc [dbo].[spGetAuditHistory]( @Source varchar(100) = 'spWBFctGL', @loginID varchar(255) = 'xx' ) as begin select top 1000 dataPoint, userId, txnDateTime, wbResult, newValue, notes from [dbo].[fctAuditLog] where writebackSource = @Source order by txndatetime desc end
This is accessed through the following report
The CREFLEXSQL formula is in cell B6, and is:
Cell B3 is the DataSource, whilst cell B4 provides the Stored Procedure to be run (code of which is listed above).
This stored procedure requires 2 arguments, being
The output from the stored procedure should be placed into cell D23 to I1022. Like the CALUMO function CREFLEX, this is dynamic and could be just cell D23.
Formatting rules are applied in rows 16 to 21 (columns A to N).
Since the format range starts 3 columns to the left of the data range, to ensure correct alignment of columns, a left offset of 3 is applied.
For reporting efficiency, the function is limiting the report to 1000 rows, but unlimited columns (1000 and -1 respectively)
To assist with writing CEXECSTOREDPROC formula, the Report Action button (available on the CALUMO toolbar) has been developed.
More information on this can be found at Report Actions.