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