Create Custom Reports

OneList reports are implemented using SQL stored procedures.

Input parameters

The stored procedure must have these input parameters no matter they are used in the reporting logic.

Parameter	Nullable	Description
@startDate datetime	No	The start date of the reporting period.
@enddate datetime	No	The end date of the reporting period.
@categories nvarchar(max)	Yes	Selected category list separated by comma, e.g. 'Salesforce Opportunities', 'Purchase Orders'
@users nvarchar(max)	Yes	Selected user names separated by comma, e.g. 'Approver1', 'Approver2'
@platforms nvarchar(max)	Yes	Selected task action platform list separated by comma, e.g. 'Web', 'Mobile'

Extended properties

The following two extended properties are required for the stored procedure to be selected for reporting:

Description;
Purpose: must be one of the following
- UsageReport - the stored procedure must implement the chartable report specification below;
- Export - export only, the stored procedure can produce any result table.

Chartable report specification

For on-screen chart, the stored procedure must produce the following two tables in the order as result:

The first table contains the charting meta data:

Column	Value	Description
seriesLabel	The name of a column in the data table.	Value of this column is grouping for the data series.
xfield	The name of a column in the data table.	Value of this column is used to plot the x-axis
yfield	The name of a numeric column in the data table.	Value of this column is used to plot the y-axis
charttype	Fixed string value.	The default chart type. Must be one of the following: Bar Line Pie Radar Doughnut
stack	Fixed number.	0/1 whether the chart should stack the data series.

The second table is the data table. This table must have
- at lease one numeric column that can be used for the y-axis in the chart;
- at lease two other columns to be used for gouping the data series and the x-axis in the chart.

Below is the Actioned By Category report:

Action By Category report

CREATE PROCEDURE [dbo].[ActionedByCategory] 
	@startDate datetime,
	@enddate datetime,
	@categories nvarchar(max) = null,
	@users nvarchar(max) = null,
	@platforms nvarchar(max) = null
AS
BEGIN
	declare @sql as nvarchar(max);

	set @sql = N'SELECT COUNT(*) AS Value, t.[Category], CONVERT(nvarchar(7), t.[ActionDate], 120) AS ActionDate 
                FROM dbo.CompletedTaskList t 
				WHERE t.[ActionDate] >= @date1 AND t.[ActionDate] <= @date2 '
	if(@categories IS NOT NULL)
	BEGIN
	 set @sql = @sql + N'AND t.[Category] IN (' + @categories + N') '
	END				
	if(@users IS NOT NULL)
	BEGIN
	 set @sql = @sql + N'AND t.[ActionedBy] IN (' + @users + N') '
	END	 
	if(@platforms IS NOT NULL)
	BEGIN
	 set @sql = @sql + N'AND t.[ActionPlatform] IN (' + @platforms + N') '
	END
				
	set @sql = @sql + N'GROUP BY t.[Category], CONVERT(nvarchar(7), t.[ActionDate], 120) ';

	select N'Category' as [seriesLabel], N'ActionDate' as [xfield], N'Value' as [yfield], N'Bar' as [charttype], 1 as [stack]

	EXEC sp_executesql @stmt = @sql, @params = N'@date1 datetime, @date2 datetime', @date1 = @startDate, @date2=@enddate 
END


GO

EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'Actioned By Category' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'ActionedByCategory'
GO

EXEC sys.sp_addextendedproperty @name=N'Purpose', @value=N'UsageReport' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'ActionedByCategory'
GO