Ok so i thought I cracked this until I passed to a colleague to check the Math and I have realized its the colour of the rows that's the issue . Is there away to control the colour by the declared @year in this Query? . The report data adds up its just defining the years that is problematic.
Query
DECLARE @year date
DECLARE @yearB date
SET @year = GETDATE()
SET @yearB = GETDATE()
SET @year = DATEADD(YEAR, DATEDIFF(YEAR, 0, @year), 0)
SET @yearB = DATEADD(YEAR, DATEDIFF(YEAR, 0, @yearB)-1, 0)
SELECT
dbo.wfn_GetUDFNvarchar ('Category','Products',Products.Product)AS 'Category',
CommissionClasses.CommissionClassID,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 00, @year) AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 01, @year)
THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Jan,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 01, @year) AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 02, @year)
THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Feb,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 02, @year) AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 03, @year)
THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Mar ,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 03, @year) AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 04, @year)
THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Apr,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 04, @year) AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 05, @year)
THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS May,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 05, @year) AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 06, @year)
THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS June ,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 06, @year) AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 07, @year)
THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS July,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 07, @year) AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 08, @year)
THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Aug,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 08, @year) AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 09, @year)
THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Sep ,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 09, @year) AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 10, @year)
THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Oct ,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 10, @year) AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 11, @year)
THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Nov,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 11, @year) AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 12, @year)
THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Dec,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= @year AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 12, @year)
THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS TotalValue
FROM SalesInvoices INNER JOIN
SalesInvoiceItems ON SalesInvoices.SalesInvoice = SalesInvoiceItems.SalesInvoice INNER JOIN
Products ON SalesInvoiceItems.Product = Products.Product INNER JOIN
Customers ON SalesInvoices.Customer = Customers.Customer INNER JOIN
CommissionClasses ON Customers.CommissionClass = CommissionClasses.CommissionClass
WHERE
CommissionClasses.CommissionClassId IN (@CommClass)
GROUP BY
dbo.wfn_GetUDFNvarchar ('Category','Products',Products.Product),
CommissionClasses.CommissionClassID
UNION ALL
SELECT
dbo.wfn_GetUDFNvarchar ('Category','Products',Products.Product)AS 'Category',
CommissionClasses.CommissionClassID,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 00, @yearB) AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 01, @yearB)
THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Jan,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 01, @yearB) AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 02, @yearB)
THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Feb,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 02, @yearB) AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 03, @yearB)
THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Mar ,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 03, @yearB) AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 04, @yearB)
THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Apr,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 04, @yearB) AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 05, @yearB)
THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS May,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 05, @yearB) AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 06, @yearB)
THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS June ,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 06, @yearB) AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 07, @yearB)
THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS July,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 07, @yearB) AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 08, @yearB)
THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Aug,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 08, @yearB) AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 09, @yearB)
THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Sep ,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 09, @yearB) AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 10, @yearB)
THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Oct ,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 10, @yearB) AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 11, @yearB)
THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Nov,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= DATEADD(MONTH, 11, @yearB) AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 12, @yearB)
THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS Dec,
SUM(CASE WHEN SalesInvoices.EffectiveDate >= @yearB AND
SalesInvoices.EffectiveDate < DATEADD(MONTH, 12, @yearB)
THEN SalesInvoiceItems.ItemValue ELSE 0 END) AS TotalValue
FROM SalesInvoices INNER JOIN
SalesInvoiceItems ON SalesInvoices.SalesInvoice = SalesInvoiceItems.SalesInvoice INNER JOIN
Products ON SalesInvoiceItems.Product = Products.Product INNER JOIN
Customers ON SalesInvoices.Customer = Customers.Customer INNER JOIN
CommissionClasses ON Customers.CommissionClass = CommissionClasses.CommissionClass
WHERE
CommissionClasses.CommissionClassId IN (@CommClass)
GROUP BY
dbo.wfn_GetUDFNvarchar ('Category','Products',Products.Product),
CommissionClasses.CommissionClassID
ORDER BY dbo.wfn_GetUDFNvarchar ('Category','Products',Products.Product) ASC
Results
Code being used currently to change the colour
'*************************************************************************
' -- Display alternate color banding (defined below) in detail rows
' -- Call from BackgroundColor property of all detail row textboxes
'*************************************************************************
Function AlternateColor(Byval rowNumber as integer) As String
Dim OddColor As String = "AntiqueWhite"
Dim EvenColor As String = "IndianRed"
If rowNumber mod 2 = 0 then
Return EvenColor
Else
Return OddColor
End If
End Function