Posts Tagged SQL Server

Practical Recursion – Part 3

Often complex problems can be solved with Recursive CTEs, but rarely are they used. Recursion can give even the most seasoned of developers a headache, so in this series of posts I’ll share some practical recursive CTEs I’ve used frequently for a variety of problems. Here in part 3 of the series is an example of how recursion can be used to perform splitting of a delimited list.

Also See…

Practical Recursion – Part 1

Practical Recursion – Part 2

Recursive CTE vs While Loop – Performance Analysis

SET NOCOUNT ON;

DECLARE

@ParameterValues VARCHAR(4000) = ‘Value1,Value2,Value3,Value4,Value5,Value6,Value7,Value8,Value9’

, @Delimiter CHAR(1) = ‘,’;

WITH

[ParameterValues] AS

(

SELECT

CASE

WHEN CHARINDEX(@Delimiter, @ParameterValues) = 0

THEN @ParameterValues

ELSE SUBSTRING(@ParameterValues, 1, CHARINDEX(@Delimiter, @ParameterValues) 1)

END AS [Value]

, CASE

WHEN CHARINDEX(@Delimiter, @ParameterValues) = 0

THEN NULL

ELSE RIGHT(@ParameterValues, LEN(@ParameterValues) CHARINDEX(@Delimiter, @ParameterValues))

END AS [RemainingValues]

UNION ALL

SELECT

CASE

WHEN CHARINDEX(@Delimiter, PV.[RemainingValues]) = 0

THEN PV.[RemainingValues]

ELSE SUBSTRING(PV.[RemainingValues], 1, CHARINDEX(@Delimiter, PV.[RemainingValues]) 1)

END AS [Value]

, CASE

WHEN CHARINDEX(@Delimiter, PV.[RemainingValues]) = 0

THEN NULL

ELSE RIGHT(PV.[RemainingValues], LEN(PV.[RemainingValues]) CHARINDEX(@Delimiter, PV.[RemainingValues]))

END AS [RemainingValues]

FROM

[ParameterValues] AS PV

WHERE

CHARINDEX(@Delimiter, PV.[RemainingValues]) IS NOT NULL

)

SELECT

PV.[Value]

FROM

[ParameterValues] AS PV

OPTION

(MAXRECURSION 0);

Advertisements

, , , , , ,

Leave a comment

Recursive CTE vs While Loop – Performance Analysis

When it comes to Recursive CTEs in SQL Server there are often misconceptions about their performance, especially compared to While Loops. In this post we’ll explore two iterative scenarios and explore the performance of both a Recursive CTE solution and a While Loop solution. Its important to understand that both a Recursive CTE and While Loop both follow a linear pattern. That means that the duration and use of resource will grow linearly with increases in the number of iterations/recursions/rows required to process the query. The linear growth of both is where a lot of the misconceptions occur. While Loops are clear iterative processes and its easy to see that the growth would be linear, but a Recursive CTE is not immediately obvious as being linear because of the way they are written with an anchor and a recursive element. The thing to remember is that a Recursive CTE is the equivalent of writing multiple selects with union all statements in between. So each recursion is essentially a new union all and select statement.By thinking about Recursive CTEs as multiple union all statements it is easier to understand the linear growth.

Now onto the performance test results. There are two scenarios for this performance test. The first scenario is a data generation scenario and the second scenario is a row concatenation scenario. The data generation scenario will illustrate performance for an extreme number of recursions/iterations as only one row of data will be generated for each iteration. The row concatenation scenario will illustrate a more average scenario where the number of recursions/iterations is low, maxing out at 30 iterations, but across a wide range of rows of data.

Looking at the Data Generation scenario we can easily see that the Recursive CTE and While Loop have equal durations as the variance is under 10.00% and even at 10 million iterations/recursions/rows the Recursive CTE is only 3 seconds faster than the While Loop. Additionally we can see that the Recursive CTE is making better use of CPU than the While Loop but the While Loop makes better use of Reads. This is typical of Recursive CTEs, they frequently out perform other query types in CPU usage, but the reads are always higher. Its important to note though that the reads are all from buffer cache, there is no actual disk IO occurring. The charts depicting the ratios of each metric per row shows conclusively the linear growth of both query types have equal performance.

Finally in the Row Concatenation scenario that more accurately mimics a real world scenario for both query types we see the Recursive CTE really shinning. Now only is it substantially more efficient with its CPU usage, but we see almost 200% better efficiency on duration. Like before though there are substantially more reads with the Recursive CTE, but again these are from buffer cache and no actual disk IO is occurring. The data also suggests that additional rows of data would see the Recursive CTE actually out perform the While Loop in terms of Reads as well.

Hopefully this performance analysis will help to dispel the misconceptions about Recursive CTEs and their performance.

Since there is a lot of results for the performance tests I’ve attached the full performance results that includes additional charting and metrics as well as the full break out of the data. Each test was run three times and then an average taken. Additional below is the full code samples used for testing.

Recursive CTE vs While Loop – Data Generator – Performance Analysis

Recursive CTE vs While Loop – Row Concatenator Performance Analysis


 

Data Generation Scenario:

Generates N rows of parent child key combinations using N iterations/recursions.

Recursive CTE

SET NOCOUNT ON;

DECLARE

@Iteration INT = 0

, @MaxIterations INT = 10000000

, @Partitioner INT = 1

, @ParentKey INT = 0

, @ChildKey INT = 0;

CREATE TABLE #GeneratedData

(

[ParentKey] INT

, [ChildKey] INT

);

————————————————————————————————————–

WITH

[GeneratedData] AS

(

SELECT

@Iteration AS [Iteration]

, @Partitioner AS [Partitioner]

, CASE

WHEN @Iteration % @Partitioner = 0

THEN @Iteration

ELSE @ParentKey

END AS [ParentKey]

, CASE

WHEN @Iteration % @Partitioner = 0

THEN @Partitioner 1

ELSE @Iteration % @Partitioner

END AS [ChildKey]

UNION ALL

SELECT

GD.[Iteration] + 1 AS [Iteration]

, CASE

WHEN ((GD.[Iteration] + 1) % GD.[Partitioner]) = 0

THEN ((GD.[Partitioner] + 1) % 30) + 1

ELSE GD.[Partitioner]

END AS [Partitioner]

, CASE

WHEN ((GD.[Iteration] + 1) % GD.[Partitioner]) = 0

THEN GD.[Iteration] + 1

ELSE GD.[ParentKey]

END AS [ParentKey]

, ((GD.[Iteration] + 1) % GD.[Partitioner]) AS [ChildKey]

FROM

[GeneratedData] AS GD

WHERE

GD.[Iteration] < @MaxIterations

)

————————————————————————————————————–

INSERT INTO

#GeneratedData

([ParentKey], [ChildKey])

SELECT

GD.[ParentKey] AS [ParentKey]

, GD.[ChildKey] AS [ChildKey]

FROM

[GeneratedData] AS GD

OPTION

(MAXRECURSION 0);

————————————————————————————————————–

DROP TABLE #GeneratedData;

 


 

 

While Loop

SET NOCOUNT ON;

DECLARE

@Iteration INT = 0

, @MaxIterations INT = 10000000

, @Partitioner INT = 1

, @ParentKey INT = 0

, @ChildKey INT = 0;

CREATE TABLE #GeneratedData

(

[ParentKey] INT

, [ChildKey] INT

);

————————————————————————————————————–

WHILE

@Iteration < @MaxIterations

BEGIN

————————————————————————————————————–

INSERT INTO

#GeneratedData

([ParentKey], [ChildKey])

SELECT

CASE

WHEN @Iteration % @Partitioner = 0

THEN @Iteration

ELSE @ParentKey

END AS [ParentKey]

, CASE

WHEN @Iteration % @Partitioner = 0

THEN @Partitioner 1

ELSE @Iteration % @Partitioner

END AS [ChildKey];

————————————————————————————————————–

SELECT

@Iteration = @Iteration + 1

, @Partitioner =

CASE

WHEN (@Iteration % @Partitioner) = 0

THEN ((@Partitioner + 1) % 30) + 1

ELSE @Partitioner

END

, @ParentKey =

CASE

WHEN (@Iteration % @Partitioner) = 0

THEN @Iteration + 1

ELSE @ParentKey

END

, @ChildKey = (@Iteration % @Partitioner);

END;

————————————————————————————————————–

DROP TABLE #GeneratedData;


 

Performance Results

Recursive CTE vs While Loop - Data Generator - Performance Analysis - Duration

Recursive CTE vs While Loop - Data Generator - Performance Analysis - CPU

Recursive CTE vs While Loop - Data Generator - Performance Analysis - Reads

Recursive CTE vs While Loop - Data Generator - Performance Analysis - Charts

 


 

 

Row Concatenation Scenario:

Concatenates all child keys for each parent key. This uses the data generation query from before to populate the source data and as such the maximum number of child keys is 30 but the actual number varies for each parent key to mimic real world scenarios.

Recursive CTE

SET NOCOUNT ON;

DECLARE

@Iteration INT = 0

, @MaxIterations INT = 10000000

, @Partitioner INT = 1

, @ParentKey INT = 0

, @ChildKey INT = 0;

CREATE TABLE #GeneratedData

(

[ParentKey] INT NOT NULL

, [ChildKey] INT NOT NULL

);

————————————————————————————————————–

WITH

[GeneratedData] AS

(

SELECT

@Iteration AS [Iteration]

, @Partitioner AS [Partitioner]

, CASE

WHEN @Iteration % @Partitioner = 0

THEN @Iteration

ELSE @ParentKey

END AS [ParentKey]

, CASE

WHEN @Iteration % @Partitioner = 0

THEN @Partitioner 1

ELSE @Iteration % @Partitioner

END AS [ChildKey]

UNION ALL

SELECT

GD.[Iteration] + 1 AS [Iteration]

, CASE

WHEN ((GD.[Iteration] + 1) GD.[Partitioner]) = 0

THEN ((GD.[Partitioner] + 1) % 30) + 1

ELSE GD.[Partitioner]

END AS [Partitioner]

, CASE

WHEN ((GD.[Iteration] + 1) % GD.[Partitioner]) = 0

THEN GD.[Iteration] + 1

ELSE GD.[ParentKey]

END AS [ParentKey]

, ((GD.[Iteration] + 1) % GD.[Partitioner]) AS [ChildKey]

FROM

[GeneratedData] AS GD

WHERE

GD.[Iteration] < @MaxIterations

)

————————————————————————————————————–

INSERT INTO

#GeneratedData

([ParentKey], [ChildKey])

SELECT

GD.[ParentKey] AS [ParentKey]

, GD.[ChildKey] AS [ChildKey]

FROM

[GeneratedData] AS GD

OPTION

(MAXRECURSION 0);

————————————————————————————————————–

ALTER TABLE #GeneratedData

ADD PRIMARY KEY CLUSTERED

(

[ParentKey]

, [ChildKey]

);

GO

————————————————————————————————————–

CREATE TABLE #SortedData

(

[ParentKey] INT

, [ChildKey] VARCHAR(4000)

, [Order] INT

, [ReverseOrder] INT

PRIMARY KEY

(

[Order]

, [ParentKey]

)

);

CREATE TABLE #ConcatenatedData

(

[ParentKey] INT

, [ChildKeys] VARCHAR(4000)

);

————————————————————————————————————–

INSERT INTO

#SortedData

([ParentKey], [ChildKey], [Order], [ReverseOrder])

SELECT DISTINCT

GD.[ParentKey] AS [ParentKey]

, GD.[ChildKey] AS [ChildKey]

, ROW_NUMBER() OVER (PARTITION BY GD.[ParentKey] ORDER BY GD.[ParentKey], GD.[ChildKey]) AS [Order]

, ROW_NUMBER() OVER (PARTITION BY GD.[ParentKey] ORDER BY GD.[ParentKey], GD.[ChildKey] DESC) AS [ReverseOrder]

FROM

#GeneratedData AS GD;

————————————————————————————————————–

WITH

[ParentKeys] AS

(

SELECT

SD.[ParentKey] AS [ParentKey]

, SD.[ChildKey] AS [ChildKeys]

, SD.[Order] + 1 AS [NextOrder]

, SD.[ReverseOrder] AS [ReverseOrder]

FROM

#SortedData AS SD

WHERE

SD.[Order] = 1

UNION ALL

SELECT

SD.[ParentKey] AS [ParentKey]

, CAST((PK.[ChildKeys] + ‘,’ + SD.[ChildKey]) AS VARCHAR(4000)) AS [ChildKeys]

, SD.[Order] + 1 AS [NextOrder]

, SD.[ReverseOrder] AS [ReverseOrder]

FROM

[ParentKeys] AS PK

INNER JOIN

#SortedData AS SD ON

PK.[NextOrder] = SD.[Order]

AND PK.[ParentKey] = SD.[ParentKey]

)

————————————————————————————————————–

INSERT INTO

#ConcatenatedData

([ParentKey], [ChildKeys])

SELECT

PK.[ParentKey] AS [ParentKey]

, PK.[ChildKeys] AS [ChildKeys]

FROM

[ParentKeys] AS PK

WHERE

PK.[ReverseOrder] = 1;

————————————————————————————————————–

DROP TABLE #GeneratedData;

DROP TABLE #SortedData;

DROP TABLE #ConcatenatedData;


 

 

While Loop

SET NOCOUNT ON;

DECLARE

@Iteration INT = 0

, @MaxIterations INT = 10000000

, @Partitioner INT = 1

, @ParentKey INT = 0

, @ChildKey INT = 0;

CREATE TABLE #GeneratedData

(

[ParentKey] INT NOT NULL

, [ChildKey] INT NOT NULL

);

————————————————————————————————————–

WITH

[GeneratedData] AS

(

SELECT

@Iteration AS [Iteration]

, @Partitioner AS [Partitioner]

, CASE

WHEN @Iteration % @Partitioner = 0

THEN @Iteration

ELSE @ParentKey

END AS [ParentKey]

, CASE

WHEN @Iteration % @Partitioner = 0

THEN @Partitioner 1

ELSE @Iteration % @Partitioner

END AS [ChildKey]

UNION ALL

SELECT

GD.[Iteration] + 1 AS [Iteration]

, CASE

WHEN ((GD.[Iteration] + 1) % GD.[Partitioner]) = 0

THEN ((GD.[Partitioner] + 1) % 30) + 1

ELSE GD.[Partitioner]

END AS [Partitioner]

, CASE

WHEN ((GD.[Iteration] + 1) % GD.[Partitioner]) = 0

THEN GD.[Iteration] + 1

ELSE GD.[ParentKey]

END AS [ParentKey]

, ((GD.[Iteration] + 1) % GD.[Partitioner]) AS [ChildKey]

FROM

[GeneratedData] AS GD

WHERE

GD.[Iteration] < @MaxIterations

)

————————————————————————————————————–

INSERT INTO

#GeneratedData

([ParentKey], [ChildKey])

SELECT

GD.[ParentKey] AS [ParentKey]

, GD.[ChildKey] AS [ChildKey]

FROM

[GeneratedData] AS GD

OPTION

(MAXRECURSION 0);

————————————————————————————————————–

ALTER TABLE #GeneratedData

ADD PRIMARY KEY CLUSTERED

(

[ParentKey]

, [ChildKey]

);

GO

————————————————————————————————————–

DECLARE

@Iteration INT = 0

, @MaxIterations INT = 0;

————————————————————————————————————–

CREATE TABLE #SortedData

(

[ParentKey] INT

, [ChildKey] VARCHAR(4000)

, [Order] INT

, [ReverseOrder] INT

PRIMARY KEY

(

[Order]

, [ParentKey]

)

);

CREATE TABLE #ConcatenatedData

(

[ParentKey] INT

, [ChildKeys] VARCHAR(4000)

);

————————————————————————————————————–

INSERT INTO

#SortedData

([ParentKey], [ChildKey], [Order], [ReverseOrder])

SELECT DISTINCT

GD.[ParentKey] AS [ParentKey]

, GD.[ChildKey] AS [ChildKey]

, ROW_NUMBER() OVER (PARTITION BY GD.[ParentKey] ORDER BY GD.[ParentKey], GD.[ChildKey]) AS [Order]

, ROW_NUMBER() OVER (PARTITION BY GD.[ParentKey] ORDER BY GD.[ParentKey], GD.[ChildKey] DESC) AS [ReverseOrder]

FROM

#GeneratedData AS GD;

————————————————————————————————————–

SELECT

@Iteration = 2

, @MaxIterations = MAX(SD.[Order]) + 1

FROM

#SortedData AS SD;

————————————————————————————————————–

INSERT INTO

#ConcatenatedData

([ParentKey], [ChildKeys])

SELECT

SD.[ParentKey] AS [ParentKey]

, SD.[ChildKey] AS [ChildKeys]

FROM

#SortedData AS SD

WHERE

SD.[Order] = 1;

————————————————————————————————————–

WHILE @Iteration < @MaxIterations

BEGIN

————————————————————————————————————–

UPDATE

#ConcatenatedData

SET

[ChildKeys] = CAST((CD.[ChildKeys] + ‘,’ + SD.[ChildKey]) AS VARCHAR(4000))

FROM

#ConcatenatedData AS CD

INNER JOIN

#SortedData AS SD ON

CD.[ParentKey] = SD.[ParentKey]

AND SD.[Order] = @Iteration;

SET @Iteration = @Iteration + 1;

END;

————————————————————————————————————–

DROP TABLE #GeneratedData;

DROP TABLE #SortedData;

DROP TABLE #ConcatenatedData;


 

 

Performance Analysis

Recursive CTE vs While Loop - Row Concatenator - Performance Analysis - Duration

Recursive CTE vs While Loop - Row Concatenator - Performance Analysis - CPU

Recursive CTE vs While Loop - Row Concatenator - Performance Analysis - Reads

Recursive CTE vs While Loop - Row Concatenator - Performance Analysis - Charts

, , , , , , , , ,

1 Comment

Practical Recursion – Part 2

Often complex problems can be solved with Recursive CTEs, but rarely are they used. Recursion can give even the most seasoned of developers a headache, so in this series of posts I’ll share some practical recursive CTEs I’ve used frequently for a variety of problems. Here in part 2 of the series is an example of how recursion can be used to seed a date table.

Also See…

Practical Recursion – Part 1

Recursive CTE vs While Loop – Performance Analysis

DECLARE

     @StartDate DATETIME

     , @EndDate DATETIME;

SELECT

     @StartDate = ‘1970/01/01’

     , @EndDate = ‘2055/12/31’;

WITH

[Dates] AS

(

     SELECT

          CAST(CONVERT(VARCHAR(8), @StartDate, 112) AS INT) AS [DateId]

          , CAST(@StartDate AS DATE) AS [Date]

          , CAST(@StartDate AS DATETIME) AS [DateTime]

          , DATEPART(YEAR, @StartDate) AS [Year]

          , DATEPART(QUARTER, @StartDate) AS [QuarterId]

          , ‘Q’ + CAST(DATEPART(QUARTER, @StartDate) AS CHAR(1)) AS [Quarter]

          , DATEPART(MONTH, @StartDate) AS [MonthId]

          , DATENAME(MONTH, @StartDate) AS [Month]

          , DATEPART(DAY, @StartDate) AS [DayId]

          , DATEPART(DAYOFYEAR, @StartDate) AS [Day]

 

     UNION ALL

 

     SELECT

          CAST(CONVERT(VARCHAR(8), DATEADD(DAY, 1, D.[DateTime]), 112) AS INT) AS [DateId]

          , CAST(DATEADD(DAY, 1, D.[DateTime]) AS DATE) AS [Date]

          , CAST(DATEADD(DAY, 1, D.[DateTime]) AS DATETIME) AS [DateTime]

          , DATEPART(YEAR,DATEADD(DAY, 1, D.[DateTime])) AS [Year]

          , DATEPART(QUARTER,DATEADD(DAY, 1, D.[DateTime])) AS [QuarterId]

          , ‘Q’+CAST(DATEPART(QUARTER,DATEADD(DAY, 1, D.[DateTime])) AS CHAR(1)) AS [Quarter]

          , DATEPART(MONTH,DATEADD(DAY, 1, D.[DateTime])) AS [MonthId]

          , DATENAME(MONTH,DATEADD(DAY, 1, D.[DateTime])) AS [Month]

          , DATEPART(DAY,DATEADD(DAY, 1, D.[DateTime])) AS [DayId]

          , DATEPART(DAYOFYEAR,DATEADD(DAY, 1, D.[DateTime])) AS [Day]

     FROM

          [Dates] AS D

     WHERE

          D.[DateTime] < @EndDate

)

–INSERT INTO

     [Date]

          ([DateId], [Date], [DateTime], [Year], [QuarterId], [Quarter], [MonthId], [Month], [DayId], [Day])

SELECT

    [DateId] AS [DateId]

    , [Date] AS [Date]

    , [DateTime] AS [DateTime]

    , [Year] AS [Year]

    , [QuarterId] AS [QuarterId]

    , [Quarter] AS [Quarter]

    , [MonthId] AS [MonthId]

    , [Month] AS [Month]

    , [DayId] AS [DayId]

    , [Day] AS [Day]

FROM

    [Dates] AS D

ORDER BY

    D.[DateId]

OPTION

    (MAXRECURSION 32767);

, , , , , , , , ,

1 Comment

Practical Recursion – Part 1

Often complex problems can be solved with Recursive CTEs, but rarely are they used. Recursion can give even the most seasoned of developers a headache, so in this series of posts I’ll share some practical recursive CTEs I’ve used frequently for a variety of problems. To kick off the series, this post is a recursive CTE that I’ve used in a number of situations for compiling transactional data into dimensional records. The example is based around member eligibility, but it can be easily modified to convert any transactional data into dimensional data.

Also See...

Practical Recursion – Part 2
Recursive CTE vs While Loop – Performance Analysis

 

WITH

[MemberEligibilityHistory] AS

(

     SELECT

          M.[MemberId] AS [MemberId]

          , M.[Member] AS [Member]

          , M.[FromDate] AS [MemberEligibilityPeriodStartDate]

          , M.[ToDate] AS [MemberEligibilityPeriodEndDate]

          , ROW_NUMBER() OVER (PARTITION BY M.[Year] ORDER BY M.[Year], M.[QuarterId]) AS [Order]

          , ROW_NUMBER() OVER (PARTITION BY M.[Year] ORDER BY M.[Year], M.[QuarterId] DESC) AS [ReverseOrder]

     FROM

          [MemberEligibility] AS M

)

, [MemberEligibility] AS

(

     SELECT

          MEH.[MemberId] AS [MemberId]

          , MEH.[Member] AS [Member]

          , MEH.[Order] AS [Order]

          , MEH.[ReverseOrder] AS [ReverseOrder]

          , MEH.[MemberEligibilityPeriodStartDate] AS [MemberEligibilityPeriodStartDate]

          , MEH.[MemberEligibilityPeriodEndDate] AS [MemberEligibilityPeriodEndDate]

          , MEH.[MemberEligibilityPeriodStartDate] AS [MemberEligibilityStartDate]

          , MEH.[MemberEligibilityPeriodEndDate] AS [MemberEligibilityEndDate]

          , DATEDIFF(DAY, MEH.[MemberEligibilityPeriodEndDate], MEH.[MemberEligibilityPeriodStartDate]) AS [CoveredDays]

          , 0 AS [GapDays]

     FROM

          [MemberEligibilityHistory] AS MEH

     WHERE

          MEH.[Order] = 1

     UNION ALL

     SELECT

          MEH.[MemberId] AS [MemberId]

          , MEH.[Member] AS [Member]

          , MEH.[Order] AS [Order]

          , MEH.[ReverseOrder] AS [ReverseOrder]

          , MEH.[MemberEligibilityPeriodStartDate] AS [MemberEligibilityPeriodStartDate]

          , MEH.[MemberEligibilityPeriodEndDate] AS [MemberEligibilityPeriodEndDate]

          , ME.[MemberEligibilityStartDate] AS [MemberEligibilityStartDate]

          , MEH.[MemberEligibilityPeriodEndDate] AS [MemberEligibilityEndDate]

          , ME.[CoveredDays] + DATEDIFF(DAY, ME.[MemberEligibilityPeriodEndDate], ME.[MemberEligibilityPeriodStartDate]) AS [CoveredDays]

          , ME.[GapDays] + DATEDIFF(DAY, ME.[MemberEligibilityPeriodStartDate], MEH.[MemberEligibilityPeriodEndDate]) AS [GapDays]

     FROM

          [MemberEligibility] AS ME

     INNER JOIN

          [MemberEligibilityHistory] AS MEH ON

               ME.[MemberId] = MEH.[MemberId]

               AND ME.[Order] + 1 = MEH.[Order]

)

 

SELECT

     ME.[MemberId]

     , ME.[Member]

     , ME.[MemberEligibilityStartDate]

     , ME.[MemberEligibilityEndDate]

     , ME.[CoveredDays]

     , ME.[GapDays]

FROM

     [MemberEligibility] AS ME

WHERE

     ME.[ReverseOrder] = 1

ORDERBY

     ME.[Member];

, , , , , , , , , , ,

Leave a comment

SSRS Alternating Background Issue

There is a very common issue with SSRS and background expressions that frequently stumps developers. The issue occurs when using a matrix, and has to do with how the RunningValue function works.

As you all know, when it comes to a matrix you can’t use the simple expression below (exmaple 1) to perform alternating backgrounds. This is because a matrix is a pivot of your dataset and thus the columns in the column groups represent rows in the dataset. So when SSRS evaluates background expressions for textboxes in the columns of a column group, those textboxes occur on different rows and thus your each column in the column group has a different alternating background. Experienced developers will recognize this quickly and switch to using the RunningValue function to solve this problem (example 2).

Example 1:

=IIf(RowNumber() Mod 2 = 0, “Alternate Color”, “Color”)

Example2:

=IIf(RunningValue(Field!RowGroup.Value, CountDistinct, “Table Scope”) Mod 2 = 0, “Alternate Color”, “Color”)

Normally this should do the trick, but occasionally an additional issue crops up. Sometimes the background color will span more than the row it should, continuing on into the next row. The root cause of this problem is that RunningValue ignores Nothing (Null) values. Why would we have Nothing (Null) values though? We have Nothing (Null) values when our dataset is a Ragged Matrix (Irregular Matrix) which creates Nothing (Null) values when its pivoted to the SSRS Rigid Matrix (Regular Matrix). A ragged matrix is one where each group does not have the same number of items.

Example Ragged Matrix:

Group A Item 1

Group B Item 1

Group B Item 2

When the Ragged Matrix is pivoted to a Rigid Matrix (a matrix where ever group is expected to have the same number of items), you end up with nulls for the missing items.

Rigid Matrix Example from Pivotted Ragged Matrix:

Group A      Item 1      Null

Group B      Item 1       Item 2

So when SSRS evaluates the background expression for a textbox in the matrix that has a Nothing (Null) value from the pivoting, it evaluates to the same background expression as the previously evaluated textbox. This is because the RunningValue function ignores the Nothing (Null) value. The RunningValue function does not treat the Nothing (Null) value as a change from the previous value it is currently holding. Thus if the previous value was on the previous group (row) then the background expression for the textbox on row 2 is the color of row 1. The solution to all this is a custom report function that does a running value but treats Nothing (Null) values as a change in the value from the previous value. Below is the code to do this along with an example expression of its use.

Example Background Expression Using Custom Code:

=IIf(Code.CountDistinctRunningValue(Fields!RowGroupItem.Value) Mod 2 = 0, “Alternate Color”, “Color”)

NOTE: The below code contains a <> on line 11 that may be pasted as &lt ; &gt ; which will need to be changed back to <> after pasting.

Custom Report Code:

Public Dim intRowNumber As Int32 = 0
Public Dim varPreviousValue As Object

Public Function CountDistinctRunningValue(ByVal o As Object) As Int32

If intRowNumber = 0 Then
intRowNumber = 1
varPreviousValue = o

Return intRowNumber
End If

If (IsNothing(varPreviousValue) And Not IsNothing(o)) _
Or (Not IsNothing(varPreviousValue) And IsNothing(o)) _
Or varPreviousValue <> o Then

intRowNumber = intRowNumber + 1
varPreviousValue = o

Return intRowNumber

End If

Return intRowNumber

End Function

, , , , , , ,

Leave a comment

Database Unit Testing

New blog post about Database Unit Testing at Magenic.com

http://magenic.com/Blog/DatabaseUnitTesting

, ,

Leave a comment

%d bloggers like this: