Create Custom Report

Owned by Alex Xie
Last updated: Nov 05, 2019 by John Datuin

OneList reports are implemented using SQL stored procedures. 

Input parameters

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

ParameterNullableDescription
@startDate datetimeNoThe start date of the reporting period.
@enddate datetimeNoThe end date of the reporting period.
@categories nvarchar(max)YesSelected category list separated by a comma, e.g. 'Salesforce Opportunities', 'Purchase Orders'
@users nvarchar(max)YesSelected user names separated by a comma, e.g. 'Approver1', 'Approver2'
@platforms nvarchar(max)YesSelected 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 a result:

  • The first table contains the charting metadata:
ColumnValueDescription
seriesLabelThe name of a column in the data table.Value of this column is grouping for the data series.
xfieldThe name of a column in the data table.Value of this column is used to plot the x-axis
yfieldThe name of a numeric column in the data table.Value of this column is used to plot the y-axis
charttypeFixed string value.

The default chart type. Must be one of the following:

  • Bar
  • Line
  • Pie
  • Radar
  • Doughnut
stackFixed number.0/1 whether the chart should stack the data series.
  • The second table is the data table. This table must have
    • at least one numeric column that can be used for the y-axis in the chart;
    • at least two other columns to be used for grouping 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

IQX OneList Documentation - 6.40