Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

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)
= null
YesSelected category list separated by a comma, e.g. 'Salesforce Opportunities', 'Purchase Orders'
@users nvarchar(max)
= null
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.

Image Added


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:

Code Block
languagesql
titleAction By Category report
collapsetrue
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