We have a situation where effectively the same report is requested by different customers but they:
- Don't want all the columns
- Want the columns in a different order than we naturally have them
- Want them called something different than how we have is stored ("Customer No" vs "Customer Number" for example)
The intent is to ease the amount of effort needed accommodate these customization requests. We are currently in a position with a couple hundred instances of these basically identical reports (excluding these superficial differences). I am looking to see if I needed once instance of these base Dynamic Queries per Parameter set or if I could handle all possible parameter sets via 1 Stored Procedure. The hope is to also not have to have a bunch of specific instances of some kind of SSRS RDL file or an SSIS DTSX package to handle these changes. That the data would come out of the Stored Procedure as we need it to be displayed/presented.
Lets assume I build out a Dynamic SQL Command where the output looks something like:
SELECT
Col1 AS 'Alias1',
Col2 AS 'Alias2',
Col3 AS 'Alias3'
FROM View
WHERE DateCol >= @StartDate
AND DateCol < @EndDate
It is built from a couple different parts using a couple of tables. Below table structures are more pseudo code to get the ideas across, so please ignore things like there are no Primary Keys declared, etc...
CREATE TABLE [report].[ReportTemplate]
(
ID INT NOT NULL, --(Primary Key)
ReportName VarChar(100) NOT NULL,
ReportTypeID INT NOT NULL --(FK To report.ReportTemplateType.ID)
)
CREATE TABLE [report].[ReportTemplateType]
(
ID INT NOT NULL, --(Primary Key)
Name VarChar(50), --(Unique Constraint)
BaseCommand VarChar(2000), --Holds FROM and JOIN clauses
WhereCommand VarChar(2000), --Holds WHERE Clause
WhereCommandParameters VarChar(2000), --Holds declaration of the parameters
)
CREATE TABLE [report].[ReportTemplateColumnDetails]
(
ID INT NOT NULL, --(Primary Key)
ReportTemplateID INT NOT NULL, --(FK to report.ReportTemplate.ID)
ColumnName VarChar(256) NOT NULL,
ColumnAlias VarChar(256) NULL, --Have logic handle blank vs NULL values
ColumnOrder SmallInt NOT NULL
)
+----+-------------------+--------------+
| ID | ReportName | ReportTypeID |
+----+-------------------+--------------+
| 1 | Customer 1 Status | 1 |
| 2 | Customer 1 Sales | 2 |
+----+-------------------+--------------+
+----+--------+-----------------+------------------------------------------------------------------------------+-------------------------------------------------------------------+
| ID | Name | BaseCommand | WhereCondition | WhereConditionParameters |
+----+--------+-----------------+------------------------------------------------------------------------------+-------------------------------------------------------------------+
| 1 | Status | FROM StatusView | WHERE DateCol >= @StartDate AND DateCol < @EndDate | @StartDate DATEIME, @EndDate DateTime |
| 2 | Sales | FROM SalesView | WHERE DateCol >= @StartDate AND DateCol < @EndDate AND Col4 = @TypeParameter | @StartDate DATEIME, @EndDate DateTime, @TypeParameter VarChar(20) |
+----+--------+-----------------+------------------------------------------------------------------------------+-------------------------------------------------------------------+
+----+------------------+------------+-------------+-------------+
| ID | ReportTemplateID | ColumnName | ColumnAlias | ColumnOrder |
+----+------------------+------------+-------------+-------------+
| 1 | 1 | Col1 | Alias1 | 1 |
| 2 | 1 | Col2 | Alias2 | 2 |
| 3 | 1 | Col3 | Alias3 | 3 |
| 4 | 2 | Col4 | Alias1 | 1 |
| 5 | 2 | Col5 | Alias2 | 2 |
| 6 | 2 | Col6 | Alias3 | 3 |
+----+------------------+------------+-------------+-------------+
The command is built using code below:
CREATE PROCEDURE [report].[ExecuteReportTemplate] (@ReportName VarChar(50))
AS
BEGIN
DECLARE @SQLCommand VarChar(MAX) = 'SELECT ',
@FirstColumnAdded BIT = 0,
@BaseCommand VarChar(2000),
@WhereCondition VarChar(2000),
@WhereConditionParameters VarChar(2000)
SELECT @BaseCommand = RTT.BaseCommand,
@WhereCondition = RTT.WhereCommand,
@WhereConditionParameters = RTT.WhereCommandParameters
FROM [report].[ReportTemplateType] RTT
INNER JOIN [report].[ReportTemplate] RT
ON RTT.ID = RT.ReportTypeID
WHERE RT.Name = @ReportName
DECLARE @ColumnName VarChar(256),
@ColumnAlias VarChar(256)
DECLARE ColumnCursor CURSOR FOR
SELECT ColumnName,
ColumnAlias
FROM [report].[ReportTemplateColumnDetails]
ORDER BY ColumnOrder
FETCH NEXT FROM ColumnCursor INTO @ColumnName, @ColumnAlias
WHILE (@@FETCH_STATUS = 0)
BEGIN
--Add a comma inbetween columns, does not happen on the first one
IF(@FirstColumnAdded = 1)
BEGIN
SET @SQLCommand = @SQLCommand + ', '
END
ELSE
BEGIN
SET @FirstColumnAdded = 1
END
--Adds the column into the list
SET @SQLCommand = @SQLCommand + @ColumnName
--If we have been provided an alias, set the alias
IF(@ColumnAlias IS NULL OR LTRIM(RTRIM(@ColumnAlias)) = '')
BEGIN
@SQLCommand = @SQLCommand + 'AS ''' + @ColumnAlias + ''' '
END
END
CLOSE ColumnCursor
DEALLOCATE ColumnCursor
--Now Add The Base Command
SELECT @SQLCommand = @SQLCommand + ' ' + @BaseCommand + ' ' + @WhereCommand
EXECUTE sp_executesql @sqlCommand, @WhereConditionParameters
@StartDate = '2019-01-01',
@EndDate = GETDATE()
END
Is there a way to dynamical change the parameters that are configured and passed in without having to build a separate command?
I would like to be able to populate [report].[ReportTemplateType].[WhereCondition] and [report].[ReportTemplateType].[WhereCondition] with a different WHERE and Parameters. For example adding a 3rd column in the WHERE condition something like Col4 = @TypeParameter. The only way I know to solve this is to create a different Stored Procedure where everything is identical to the above Stored Procedure but we would change the last piece to:
EXECUTE sp_executesql @sqlCommand, @WhereConditionParameters
@StartDate = '2019-01-01',
@EndDate = GETDATE(),
@TypeParameter = 'SomeStringValue'
Is there a way to dynamical change the parameters that are configured and passed in without having to build a separate command?
