Last post Nov 02, 2015 09:23 PM by Krunal Parekh
Oct 30, 2015 11:50 AM|lnbernas|LINK
I have been working on a SSRS report where the data is grouped from 0 to 29 days, 30 to 60 days, 61 to 90 days and 91+ days. I also had to add a filter to the actual dataset to filter on whether or not the flag was a yes or no. Within each group I also had
to filter on the Date being between those ranges to get the report to calculate the correct number. For some reason when I filter on the 61 to 90 days group it just doesn't show up in the report, I know there are no dates between this range but I need it to
print a 0 to show that. I have tried an IIF statement, and I have tried an OrElse statement to see if I could get it to print a 0 instead, but nothing seems to work. Does anyone know of a way to fix this?
Nov 02, 2015 09:23 PM|Krunal Parekh|LINK
You need to handle this in the SQL server side. That you add the dummy data if there is no record. For example take below query.
DECLARE @Test AS TABLE
DECLARE @FilterGroup INT = 2
--suppose you have groupnum 2 that you passed as filter but the query does not return it becuse there is no data then you need to add a dummy row
;With CTE AS
SELECT * FROM @Test UNION ALL SELECT @FilterGroup, NULL
SELECT Groupno,SUM(ISNULL(total,0)) Total FROM CTE WHERE GroupNo = @FilterGroup GROUP BY Groupno