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