Skip to content

CREPORTUPLOAD

The CREPORTUPLOAD formula allows files to be uploaded into CALUMO for processing with a custom SQL Server stored procedure.

Note

The files are saved into a dedicated location in the CALUMO database and only one file can be uploaded per formula. If more than one is selected by the report user, only the first is uploaded.

This formula will only function for web (Published) reports. In Excel, a simple textual confirmation of the existence of the formula is displayed.

Syntax

CREPORTUPLOAD (ButtonText, Key, ProcDataSource, ProcName, ProcArgs, ResultCell, AutoCalc, Enabled, ImageUrl)

Arguments

ButtonText : string (required)

Text to appear on the upload button shown to the user in the web.

Key : string (required)

A key (presumably unique) passed to the stored procedure, to enable the identification of the specific upload event by the post processing stored procedure.

ProcDataSource : string

The (CALUMO Connection) to a database that contains the stored procedure.

ProcName : string

The name of the stored procedure to run after the file has been uploaded to perform the processing.

ProcArgs : string

Arguments to be passed to the stored procedure in the specified format, identical to CEXECSTOREDPROC, eg “@param1=’Hello’,@param2=’world’,@ID=22”

ResultCell : string (required)

The cell in the report which displays the jobID (a GUID) when the file is successfully uploaded.

Note

This cell needs to be a visible cell to be able to be used.

AutoCalc : boolean

Whether to automatically calculate the report after the file upload is complete. This allows the returned value that is written into the ResultCell to be used by other logic in the report.

Default value: true

Enabled : boolean

Enable or disable this formula. This simply provides a way of disabling the formula without having to remove it.

Default value: true

ImageUrl : string

The URL to an image that will be shown, instead of a button, in the cell for the user to click on.

Examples

=CREPORTUPLOAD("Upload","unique-1","datasource1","myproc","@param1='Hello',@param2='world'",B9,FALSE,TRUE, A10)

Where B9 is the cell that the result will be written to and A10 is a CINFO formula of type UserFileUrl that points to an image to display.

Processing

The uploaded file is processed in two steps:

  1. The details of the upload (including the bytes of the file) are stored in the CALUMO database in the table ReportUploadFile. This table has the following columns:

    • DateUploaded
    • FileByteCount
    • FileBytes
    • FileName
    • LoginID - The login ID of the user uploading the file
    • Key - A server generated key for this process
    • AsJSON - A JSON representation of the file if it is of extension .XLSX, .XLSM or .CSV

  2. The designated stored procedure processes the uploaded file.

    The required parameters of the stored procedure are: loginID, fileKey, fileID (DB ID of the record) and jobID (a GUID for this event in the format “jobid-{GUID}”). The required parameters are always passed to the stored procedure, and custom parameters (specified in the ProcArgs argument of the formula) are then appended.

    If the JSON representation of the upload is required in the stored procedure, then @json parameter must be defined in the stored procedure.

CREATE PROCEDURE [dbo].[uspReportUpload_Process]
    @loginid nvarchar(1000),
    @fileKey nvarchar(1000),
    @fileId nvarchar(1000),
    @jobId nvarchar(1000),
    -- Other @params that are sent up in from the formula should be listed here
    @json nvarchar(max)  -- This is an optional parameter
AS
  -- Custom logic here
GO

JSON representation

The JSON representation that we store in the asJson column and pass through to the stored procedure, (if the @json parameter is detected), has a top-level item for each worksheet detected. If the file is a CSV, then it will only have a single top-level item and it will default to use sheet1. Within that object, there is an array of rows with an array of columns inside. For example:

{
    "sheet1": [
        ["Jan","Feb","Mar"],  //ROW 1 with, COLS A,B,C
        ["10","","44"]
    ],
    "sheet2": [
        ["Australia","New Zealand","Fiji","Samoa","Vanauatu"],
        ["Cleaning Supplies","44","55","66","77"],
        ["Stationary","11","45","12","55"],
        ["IT Supplies","454","12","333","523"]        
    ],
}

As an example, this T-SQL will transform such an input into a tabular format that can be consumed by any custom logic:

SELECT
    Sheet = sheet.[key],
    cellRef = concat(char(65+cells.[key]),rows.[key]+1),
    R1C1cellRef = concat('R',rows.[key]+1,'C',cells.[key]+1),
    cells.value
FROM 
    OPENJSON(@json) sheet
        CROSS APPLY OPENJSON(sheet.value) rows
        CROSS APPLY OPENJSON(rows.value) cells

Another example, taking it a step further, this T-SQL will transform such an input into a tabular format that has rows and columns named the same as an Excel worksheet.

Note

You may need more than A..Z depending on the input worksheet. Just modify the PIVOT part of the T-SQL to expand this.

SELECT * FROM (
SELECT
Sheet = sheet.[key],
col = CASE WHEN cells.[key] > 25 THEN CHAR(64 + ((cells.[key]) / 26)) + CHAR(65 + ((cells.[key]) % 26)) ELSE CHAR(65 + cells.[key]) END,
[row] = rows.[key]+1,
cellValue = cells.value
FROM
OPENJSON(@json) sheet
CROSS APPLY OPENJSON(sheet.value) rows
CROSS APPLY OPENJSON(rows.value) cells
) t
PIVOT (
MAX(cellValue)
FOR col IN ([A],[B],[C],[D],[E],[F],[G],[H],[I],[J],[K],[L],[M],[N],[O],[P],[Q],[R],[S],[T],[U],[V],[W],[X],[Y],[Z]
,[AA],[AB],[AC],[AD],[AE],[AF],[AG],[AH],[AI],[AJ],[AK],[AL],[AM],[AN],[AO],[AP],[AQ],[AR],[AS],[AT],[AU],[AV],[AW],[AX],[AY],[AZ]
,[BA],[BB],[BC],[BD],[BE],[BF],[BG],[BH],[BI],[BJ],[BK],[BL],[BM],[BN],[BO],[BP],[BQ],[BR],[BS],[BT],[BU],[BV],[BW],[BX],[BY],[BZ])
) AS pivot_table
Back to top