locked
SSRS Grouping Issues RRS feed

  • Question

  • User1679135995 posted

    Hello,

    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?

    Friday, October 30, 2015 11:50 AM

Answers

  • User1644755831 posted

    Hello Inbernas,

    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
    (
     Groupno int,
     total int
    )
    
    insert @Test 
    SELECT 1,45 
    UNION ALL 
    select 1,54
    
    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
    
    
    

    With Regards,

    Krunal Parekh

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, November 2, 2015 9:23 PM