The following is an example for creating a custom report in Scolo. The custom report needs to get a span of dates as well as a trail name from the user to be passed as variables to our procedure. For the span of dates we will use the variables start date ($$START_DATE$$) and end date ($$END_DATE$$). To get the trail name for our stored procedure we will use the variable ($$TRAIL$$).

Our procedure name is going to be ‘sp_Custom_01’. We are going to only allow it be run by users with an account type of System Operator and higher.

Create The Stored Procedure

First we create the stored procedure. This example is very simple and only returns the values of variables passed to it. Be aware that the result alias returned is used by Scolo as the column header description. Also note that all values are passed to the stored procedure as varchar (variable character) types.

MySQL Example

DELIMITER //
CREATE
DEFINER = `service_Scolo`@`%`
PROCEDURE `sp_Custom_01` (in_TrailName varchar(255), in_DateStart varchar(255), in_DateStop varchar(255))
proc: begin
-- =============================================
-- author: john doe
-- created: 2017.12.04
-- description: Test custom report
-- =============================================
    select `in_TrailName` as 'Trail', `in_DateStart` as 'Start Date', `in_DateStop` as 'Stop Date';
end
//
DELIMITER ;

SQL Server Example

CREATE PROCEDURE [dbo].[sp_Custom_01]
@in_TrailName VARCHAR(255),
@in_DateStart VARCHAR(255),
@in_DateStop VARCHAR(255)
AS
BEGIN
-- =============================================
-- author: john doe
-- created: 2017.12.04
-- description: Test custom report
-- =============================================
    select @in_TrailName as 'Trail', @in_DateStart as 'Start Date', @in_DateStop as 'Stop Date';
end
GO

Create Custome Report Record

Now that we have a valid working stored procedure we can create the database records for Scolo.

First we need to create a record in the Reports table for the new custom report. If your report does not require any variables then this is the only addition required. The value for Permission is 30 to signify an account type of System Operator. You could also use 10 (System View Only), 20 (Report and System Viewer), 30 (System Operator), 40 (System Configurator), 50 (System Manager) or 60 (Administrator).

MySQL Example

insert into `Reports` (`ReportCategoryID`,`ReportName`,`ReportTitle`,`Procedure`,`Description`,`Active`,`SortOrder`,`Permission`, `Comment`,`ModifiedBy`,`Modified`)
values
(
    (select `ReportCategoryID` from `ReportCategory` where `Category` = 'Custom'), -- the 'Custom' report category ID
    'Custom report 01', -- this is the name that will be displayed in the navigation menu
    'Current Report 01', -- this is the name that is used in the diplay title for the report
    'sp_Custom_01', -- the name of the stored procedure to call
    'Custom report example', -- the text to display when requesting user input for variables
    'true', -- marked as active, false if it is not to be displayed
    '10', -- the sort order that the report will be listed in the navigation menu
    '30', -- the minimum user priveledges to be able to view this report
    'Custom report example', -- the text to display in the reports tooltip popup in the navigation menu
    'jdoe', -- your name or initials to indicate who created this record
    now() -- the date and time this record was created
);

SQL Server Example

INSERT INTO [dbo].[Reports]
    ([ReportCategoryID],[ReportName],[ReportTitle],[Procedure],[Description],[Active],[SortOrder],[Permission] ,[Comment],[ModifiedBy] ,[Modified])
VALUES
(
    (select [ReportCategoryID] from [ReportCategory] where [Category] = 'Custom'), -- the 'Custom' report category ID
    'Custom report 01', -- this is the name that will be displayed in the navigation menu
    'Custom Report 01', -- this is the name that is used in the diplay title for the report
    'sp_Custom_01', -- the name of the stored procedure to call
    'Custom report example', -- the text to display when requesting user input for variables
    'true', -- marked as active, false if it is not to be displayed
    '10', -- the sort order that the report will be listed in the navigation menu
    '30', -- the minimum user priveledges to be able to view this report
    'Custom report example', -- the text to display in the reports tooltip popup in the navigation menu
    'jdoe', -- your name or initials to indicate who created this record
    CURRENT_TIMESTAMP -- the date and time this record was created
);

Create Custom Report Variable Records

If your procedure requires variables to be passed to it, like our example does, then one more step must be done to set it up. We need to tell Scolo what variables to get from the user and what order those values are passed to our stored procedure. Our procedure requires the trail name first then the start and end dates so the sort in the records we insert reflect that.

MySQL Example

insert into `ReportVariables`
(`ReportID`,`ReportVariable`,`VariableName`,`SortOrder`,`Active`,`ModifiedBy`,`Modified`)
values
((select `ReportID` from `Reports` where `Procedure` = 'sp_Custom_01' and `Active` = 'true'), -- this gets the report ID for our procedure
    '$$TRAIL$$', -- the variable type we are requesting
    'Trail Name', -- the description text to display in the dialog
    '0', -- this is the order in which we pass to the stored procedure
    'true', -- marked as active, false if it is not to be displayed
    'jdoe', -- your name or initials to indicate who created this record
    now()), -- the date and time this record was created
((select `ReportID` from `Reports` where `Procedure` = 'sp_Custom_01' and `Active` = 'true'), -- this gets the report ID for our procedure
    '$$START_DATE$$', -- the variable type we are requesting
    'Start Date', -- the description text to display in the dialog
    '1', -- this is the order in which we pass to the stored procedure
    'true', -- marked as active, false if it is not to be displayed
    'jdoe', -- your name or initials to indicate who created this record
    now()), -- the date and time this record was created
((select `ReportID` from `Reports` where `Procedure` = 'sp_Custom_01' and `Active` = 'true'), -- this gets the report ID for our procedure
    '$$END_DATE$$', -- the variable type we are requesting
    'End Date', -- the description text to display in the dialog
    '2', -- this is the order in which we pass to the stored procedure
    'true', -- marked as active, false if it is not to be displayed
    'jdoe', -- your name or initials to indicate who created this record
    now()); -- the date and time this record was created

SQL Server Example

INSERT INTO [dbo].[ReportVariables]
([ReportID],[ReportVariable],[VariableName],[SortOrder],[Active],[ModifiedBy],[Modified])
VALUES
((select [ReportID] from [Reports] where [Procedure] = 'sp_Custom_01' and [Active] = 'true'), -- this gets the report ID for our procedure
    '$$TRAIL$$', -- the variable type we are requesting
    'Trail Name', -- the description text to display in the dialog
    '0', -- this is the order in which we pass to the stored procedure
    'true', -- marked as active, false if it is not to be displayed
    'jdoe', -- your name or initials to indicate who created this record
    CURRENT_TIMESTAMP), -- the date and time this record was created
((select [ReportID] from [Reports] where [Procedure] = 'sp_Custom_01' and [Active] = 'true'), -- this gets the report ID for our procedure
    '$$START_DATE$$', -- the variable type we are requesting
    'Start Date', -- the description text to display in the dialog
    '1', -- this is the order in which we pass to the stored procedure
    'true', -- marked as active, false if it is not to be displayed
    'jdoe', -- your name or initials to indicate who created this record
    CURRENT_TIMESTAMP), -- the date and time this record was created
((select [ReportID] from [Reports] where [Procedure] = 'sp_Custom_01' and [Active] = 'true'), -- this gets the report ID for our procedure
    '$$END_DATE$$', -- the variable type we are requesting
    'End Date', -- the description text to display in the dialog
    '2', -- this is the order in which we pass to the stored procedure
    'true', -- marked as active, false if it is not to be displayed
    'jdoe', -- your name or initials to indicate who created this record
    CURRENT_TIMESTAMP); -- the date and time this record was created

You now should be able to see the new report the next time you start up and log into Scolo.