16 June 2012

TSQL Number Generator and column counter for SSRS

Here is another cool script I use in SSRS. I have several reports that use a Matrix with a non-predetermined number of columns and rows. I get around this by using the below number generator to predetermine the placement of my columns so everything fits nicely on one page. As you can see this is flexible enough to be used on all of my reports that have this requirement. Some reports have only two columns and others have up to 6.

In my query for my data I assign a column number to each grouping which corresponds to this query
DENSE_RANK() OVER(PARTITION BY [dbo].[Table].[PKId]
                  ORDER BY [dbo].[Table].[SomeOrderColumn] DESC,[dbo].[Table].[Name])



/*BEGIN Settings for script*/
DECLARE @NumbersToRepeatEveryRow NVARCHAR(255) = '1,13'
DECLARE @KeepRepeatNumbersAtStart bit = 1 --0 causes the numbers to stay in proper asc order
DECLARE @NumberOfItemsPerRow int = 6
DECLARE @TotalNumberOfItems int = 15 --select count of your items
/*END Settings for script*/

DECLARE @NumberOfItemsInRepeatList int = (SELECT COUNT(Value) FROM [dbo].[CsvToStringTable](@NumbersToRepeatEveryRow))
DECLARE @NumberOfNewItemsPerRow int = @NumberOfItemsPerRow - @NumberOfItemsInRepeatList
DECLARE @NumberOfItemsToGenerate int
IF @NumberOfItemsInRepeatList >= @TotalNumberOfItems
    SET @NumberOfItemsToGenerate = 0
  ELSE
    SET @NumberOfItemsToGenerate = @TotalNumberOfItems - @NumberOfItemsInRepeatList
;WITH
 E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
           SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
           SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
           SELECT 1)--10^1 or 10 rows
 ,E2(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E1 a, E1 b) --10^2 or 100 rows
 ,[NumberCollection] (
                      [ItemNumber]
                      ,[RowAssignment]) AS
  (
    SELECT TOP (@NumberOfItemsToGenerate)
      N AS [ItemNumber]
      ,CASE
        WHEN N IN (SELECT Value FROM [dbo].[CsvToStringTable](@NumbersToRepeatEveryRow))
          THEN NULL
        ELSE
          (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1) / (@NumberOfNewItemsPerRow)
      END AS [RowAssignment]
    FROM
      E2
    WHERE N NOT IN (SELECT Value FROM [dbo].[CsvToStringTable](@NumbersToRepeatEveryRow)) --Function you should always have
    UNION ALL
    SELECT
      Value AS [ItemNumber]
      ,NULL AS [RowAssignment]
    FROM
      [dbo].[CsvToStringTable](@NumbersToRepeatEveryRow) --Function you should always have
  )

SELECT DISTINCT
  CASE
    WHEN @TotalNumberOfItems <= @NumberOfItemsInRepeatList
      THEN 0
    ELSE
      [RowAssignment]
  END AS [DisplayOrder]
  ,@NumbersToRepeatEveryRow AS [NumbersToRepeatEveryRow]
  ,CASE
    WHEN @KeepRepeatNumbersAtStart = 1
         AND @NumbersToRepeatEveryRow IS NOT NULL
      THEN @NumbersToRepeatEveryRow + ',' + STUFF(
                                                    (
                                                      SELECT
                                                        ',' + CONVERT(NVARCHAR(255),[InternalNumberCollection].[ItemNumber])
                                                      FROM
                                                        [NumberCollection] AS [InternalNumberCollection]
                                                      WHERE
                                                        [InternalNumberCollection].[RowAssignment] = [NumberCollection].[RowAssignment]
                                                      ORDER BY [ItemNumber]
                                                      FOR XML PATH('')
                                                    , TYPE).value('.', 'NVARCHAR(255)')
                                                  ,1,1,'')
    ELSE STUFF(
                (
                  SELECT
                    ',' + CONVERT(NVARCHAR(255),[InternalNumberCollection].[ItemNumber])
                  FROM
                    [NumberCollection] AS [InternalNumberCollection]
                  WHERE
                    [InternalNumberCollection].[RowAssignment] = [NumberCollection].[RowAssignment]
                    OR [RowAssignment] IS NULL
                  ORDER BY [ItemNumber]
                  FOR XML PATH('')
                , TYPE).value('.', 'NVARCHAR(255)')
              ,1,1,'')
  END AS [NumberCollection]
  ,(SELECT COUNT([InternalNumberCollection].[ItemNumber])
      FROM
        [NumberCollection] AS [InternalNumberCollection]
      WHERE
        [InternalNumberCollection].[RowAssignment] = [NumberCollection].[RowAssignment]
        OR [RowAssignment] IS NULL
    ) AS [RowItemCount]
FROM
  [NumberCollection]
WHERE [RowAssignment] IS NOT NULL
      OR @TotalNumberOfItems <= @NumberOfItemsInRepeatList

No comments:

Post a Comment

Blog Slideshow (all images from this blog)