ssrs-2008-r2
I am new to report writing and am trying to suppress rows that have zero data. I need to know how to write the conditional iif statement when I have multiple columns of data. Example
account number account description fy 2012 fy 2013 fy 2014 fy 2015
some accounts have zero numbers across the years and I don't want that row to show.
Always enjoy getting help from this site... Thanks to @billinkc.
I had the thought in the back of my mind that maybe the graph had to much data to display but guess it never clicked.
I modified the graph's Category Groups to be an expression for grouping by the hour not just the full date value: =Hour(Fields!CapturedTime.Value)
That allowed the graph to be prettified into this:
As suggested in my comment I would not PIVOT the data within the SQL and let SSRS do the PIVOTING by using a matrix. Why? Because your report will not be flexible should you choose to allow users to specify their own number of periods.
This kind of report is much easier with a Date dimension table so you can easily figure out the week start/end date between a set of dates, this is what happens in the first part of the solution but if you had a Date table permanently stored it would be as case of simply querying that table.
The CTEs named N1 through to N5 are creating a tally table, a table full of numbers, which I use to generate the CTE DateAndWeek which contains a list of dates and the week start date, based on the @@DATEFIRST value.
WITH N1 AS
(
SELECT 1 AS N
UNION ALL
SELECT 1 AS N
)
, N2 AS
(
SELECT A.N FROM N1 AS A
CROSS JOIN N1 AS B
)
, N3 AS
(
SELECT A.N FROM N2 AS A
CROSS JOIN N2 AS B
)
, N4 AS
(
SELECT A.N FROM N3 AS A
CROSS JOIN N3 AS B
)
, N5 AS
(
SELECT A.N FROM N4 AS A
CROSS JOIN N4 AS B
)
, DateAndWeek AS
(
SELECT TOP 365
DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), '20140101') AS [Date]
, DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
+ (1-DATEPART(WEEKDAY, DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), '20140101'))), '20140101') AS WeekStartDate
FROM
N5
)
Depending on your requirements you may or may not need to create dummy/blank rows for items which have no sales during the period and this is what happens in the ReportTemplate CTE. It returns a dataset with all the weeks within the period specified and performs a 'CROSS JOIN' against all unique ItemID and Description values.
, ReportTemplate AS
(
SELECT DISTINCT
WeekStartDate
, ItemId
, Description
FROM
DateAndWeek
CROSS JOIN
(
SELECT DISTINCT
ItemID
, Description
FROM
#Sales
) AS Sales
WHERE
[Date] BETWEEN @StartDate AND @EndDate
)
The final CTE perform the AGGREGATION of your sale data by JOINING to the DateAndWeek CTE to figure out which week the sale belongs in.
, SalesInPeriod AS
(
SELECT
ItemID
, WeekStartDate
, SUM(QTYOrdered) AS Total
FROM
#Sales
JOIN DateAndWeek
ON CAST(#Sales.ReceiptDate AS DATE) = DateAndWeek.Date
WHERE
DateAndWeek.Date BETWEEN @StartDate AND @EndDate
GROUP BY
ItemID
, WeekStartDate
)
Finally, LEFT JOIN the ReportTemplate and SalesInPeriod CTEs to get the result set for your report.
Here is the full solution
IF OBJECT_ID('tempdb..#Sales', 'U') IS NOT NULL
DROP TABLE #Sales
GO
DECLARE
@StartDate DATE = DATEADD(WEEK, -6, CAST(GETDATE() AS DATE))
, @EndDate DATE = CAST(GETDATE() AS DATE)
CREATE TABLE #Sales
(
ID INT
, ItemID NVARCHAR(6)
, [Description] NVARCHAR(10)
, QTYOrdered NUMERIC
, ReceiptDate DATETIME
)
INSERT INTO #Sales
VALUES
(1, N'AAL100', 'Ice Cream', 21 , DATEADD(WEEK, -1, GETDATE()))
, (2, N'AAL100', 'Ice Cream', 40 , DATEADD(WEEK, -1, GETDATE()))
, (3, N'AAL100', 'Ice Cream', 50 , DATEADD(WEEK, -3, GETDATE()))
, (4, N'AAL101', 'Burger', 43 , DATEADD(WEEK, -3, GETDATE()))
, (5, N'AAL101', 'Burger', 42 , DATEADD(WEEK, -5, GETDATE()))
, (6, N'AAL101', 'Burger', 76 , DATEADD(WEEK, -5, GETDATE()))
, (7, N'AAL102', 'Pizza', 76 , DATEADD(WEEK, -7, GETDATE()))
, (8, N'AAL102', 'Pizza', 34 , DATEADD(WEEK, -7, GETDATE()))
, (9, N'AAL103', 'Burrito', 56 , DATEADD(WEEK, -9, GETDATE()))
, (10, N'AAL103', 'Burrito', 75 , DATEADD(WEEK, -9, GETDATE()))
;
WITH N1 AS
(
SELECT 1 AS N
UNION ALL
SELECT 1 AS N
)
, N2 AS
(
SELECT A.N FROM N1 AS A
CROSS JOIN N1 AS B
)
, N3 AS
(
SELECT A.N FROM N2 AS A
CROSS JOIN N2 AS B
)
, N4 AS
(
SELECT A.N FROM N3 AS A
CROSS JOIN N3 AS B
)
, N5 AS
(
SELECT A.N FROM N4 AS A
CROSS JOIN N4 AS B
)
, DateAndWeek AS
(
SELECT TOP 365
DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), '20140101') AS [Date]
, DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
+ (1-DATEPART(WEEKDAY, DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), '20140101'))), '20140101') AS WeekStartDate
FROM
N5
)
, ReportTemplate AS
(
SELECT DISTINCT
WeekStartDate
, ItemId
, Description
FROM
DateAndWeek
CROSS JOIN
(
SELECT DISTINCT
ItemID
, Description
FROM
#Sales
) AS Sales
WHERE
[Date] BETWEEN @StartDate AND @EndDate
)
, SalesInPeriod AS
(
SELECT
ItemID
, WeekStartDate
, SUM(QTYOrdered) AS Total
FROM
#Sales
JOIN DateAndWeek
ON CAST(#Sales.ReceiptDate AS DATE) = DateAndWeek.Date
WHERE
DateAndWeek.Date BETWEEN @StartDate AND @EndDate
GROUP BY
ItemID
, WeekStartDate
)
SELECT
ReportTemplate.WeekStartDate
, ReportTemplate.ItemID
, ReportTemplate.Description
, SalesInPeriod.Total
FROM
ReportTemplate
LEFT JOIN SalesInPeriod
ON ReportTemplate.ItemID = SalesInPeriod.ItemID
AND ReportTemplate.WeekStartDate = SalesInPeriod.WeekStartDate
Best Answer
Here's how you hide How to: Hide an Item (Reporting Services)
Here's the expression