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.

Supported relational databases are Microsoft SQL Server.

Syntax

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

Arguments

Datasource

(Required)

Specify the relational databases’ datasource name (defined in Server Configuration) that you want to connect to.

StoredProcName

(Required)

Specify the name of the authorised procedure that you want to return data from. SQL schemas are allowed in the name.

For example: admin.sp_test

Parameters

(Required)

Specify the Stored Procedures’ parameters.  For example, “@param1=’Hello’,@param2=’world’, @ID=22”

If the parameter @loginId is defined in the stored proc, the 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

(Required)

Specify the data range of the report.

Format Range

(Required)

Specify the format range of the report.

Left Offset

Specify the left offset of the report.

Default value: 0

Top Offset

Specify the top offset of the report.

Default value: 0

Right Offset

Specify the right offset of the report.

Default value: 0

Bottom Offset

Specify the Bottom Offset of the report.

Default value: 0

Max Rows

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

Default value: -1

Max Columns

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

Default value: -1

Nulls As

Specify the value which can replace null.

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)

Where the stored procedure has a parameter that is datatype date, you can passed the date through as 1-Jan- 2010  - and unlike text parameters, do not 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 authorized process (authorized 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