Skip to content

CREFLEXSQL

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.

Syntax

CREFLEXSQL (Datasource, StoredProcName, Parameters, DataRange, FormatRange, LeftOffset, TopOffset, RightOffset, BottomOffset, MaxRows, MaxColumns, NullsAs)

Arguments

Datasource : string (required)

Specify the relational databases’ datasource name (defined in Server Configuration) for connection.

StoredProcName : string (required)

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

Parameters : string (required)

Specify the parameters of the stored procedure.  For example, "@param1='Hello',@param2='world', @ID=22"

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: '@param1='Hello',@param2='world', @ID=22

Data Range : string (required)

Specify the data range of the report.

Format Range : string (required)

Specify the format range of the report.

Left Offset : number

Specify the left offset of the report.

Default value: 0

Top Offset : number

Specify the top offset of the report.

Default value: 0

Right Offset : number

Specify the right offset of the report.

Default value: 0

Bottom Offset : number

Specify the Bottom Offset of the report.

Default value: 0

Max Rows : number

Specify the max row number of the report. -1 represents unlimited rows.

Default value: -1

Max Columns : number

Specify the max column number of the report. . -1 represents unlimited columns.

Default value: -1

Nulls As : string

Specify the value that will replace nulls.

Default value: 0

Usage Scenarios

CREFEXSQL is useful for bringing back transactional information into reports which is not (or does not belong) in an Analysis Services cube.

For example:

  • 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.

Examples

=CREFLEXSQL("CS3","Test","@id=25,@firstname='john'",C3:H10,C1:H1,1,1,0,0,20,10,0)

=CREFLEXSQL("CS3","Test","@id=25,@firstname='john'",C3:H10,C1:H1)

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 fctAuditLog:

  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:

=CREFLEXSQL($B$3,$B$4,"@source='"&B9&"',@loginID='"&B8&"'",$D$23:$I$1022,A16:N21,3,0,0,0,1000,-1,0)

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 @Source and @LoginID.

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.

Back to top