Posts Tagged Microsoft
Practical Recursion – Part 3
Posted by Brian Beswick in Database Development on July 12, 2014
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…
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);
Recursive CTE vs While Loop – Performance Analysis
Posted by Brian Beswick in Database Development on July 3, 2014
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
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
Practical Recursion – Part 2
Posted by Brian Beswick in Business Intelligence, Database Development on May 31, 2014
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…
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);
Practical Recursion – Part 1
Posted by Brian Beswick in Business Intelligence, Database Development on May 14, 2014
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];
SSRS Alternating Background Issue
Posted by Brian Beswick in Business Intelligence, SSRS on May 12, 2014
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 < ; > ; 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
Microsoft Simplicity Strategy
Posted by Brian Beswick in Business Intelligence on May 15, 2013
New blog post about Microsoft’s Simplicity Strategy and my take away from the PASS Business Analytics Conference at Magenic.com
