SSRS Expression "Count of Days with No dissatisfied Customers" Cannot get it working

Question

I have to get a expression in my SSRS table that achieves the count of days with no dissatisfied customers.

Right now I have an expression like this:

=RunningValue(IIF(Fields!SATISFACTION_LEVEL.Value <> "Dissatisfied",1,0),Sum, "DataSet1")

This gives me the number of rows that contain a satisfaction level other than Dissatisfied.

My issue is that I can't seem to get a count of days where there was no dissatisfied customer. I can't find a solution to counting the days. Essentially this is what it should do. If there was a record that day with a dissatisfied customer, don't count it. If there was no dissatisfied customers, tally it.

This will need to be done for the current year to date, and also for the year before.

I would really appreciate any help with this expression!

Thanks

UPDATE MORE INFO:

dataset structure is like this:

_______________________________________
| satisfaction_level     | Date       |
---------------------------------------    
| Satisfied              | 07/20/2020 |
| dissatisfied           | 07/20/2020 |
| Satisfied              | 07/20/2020 |
| Highly Satisfied       | 07/20/2020 |
| Satisfied              | 07/20/2020 |
| Satisfied              | 07/21/2020 |
| Satisfied              | 07/21/2020 |
| Highly Satisfied       | 07/21/2020 |

expected functionality - for the day of 7/20/2020 there was 1 dissatisfied customer (do not tally), for the day of 7/21/2020 there were NO dissatisfied customers (tally). Resulting in a total number of days where there were NO dissatisfied customers. I hope this helps further explain the outcome needed.

Answer 1

Put SSRS to the side for now, the problem with counting days of anything is that its hard to count a row that is not there. For instance if I have a number of response records spread out over a week, but they only fall on 4 of the days, when we group by day, the query can only return results for the days that existed in the recordset:

DECLARE @Responses as Table 
(
    ENTRY_TIME DateTimeOffset, SATISFACTION_LEVEL VARCHAR(20)
)
INSERT INTO @Responses
VALUES 
('2020-01-4', 'Dissatisfied'),
('2020-01-4', 'Dissatisfied'),
('2020-01-1', 'Satisfied'),
('2020-01-5', 'Dissatisfied'),
('2020-01-5', 'Satisfied'),
('2020-01-2', 'Dissatisfied')

SELECT 
  fn.DATE
  , DATENAME(WEEKDAY, fn.DATE) as [Day]
  , SUM(CASE SATISFACTION_LEVEL WHEN 'Dissatisfied' THEN 1 ELSE 0 END) as [Dissatisfied]
FROM @Responses
CROSS APPLY (SELECT CAST(ENTRY_TIME as Date) as [DATE]) as fn
GROUP BY fn.DATE
ORDER BY fn.DATE

DATE       Day                            Dissatisfied
---------- ------------------------------ ------------
2020-01-01 Wednesday                      0
2020-01-02 Thursday                       1
2020-01-04 Saturday                       2
2020-01-05 Sunday                         1

(4 rows affected)

We can solve this problem by generating a series record set that we can join our real world data against that will ensure that we have a row for each day.

This can be achieved through the use of a recursive CTE, in the query below the grouped data result is joined to the series data, you could do this any number of different ways, you could even pivot the SATISFACTION_LEVEL column responses, this is just to illustrate the technique of pre-processing the data in SQL before formatting it in an SSRS report:

DECLARE @From Date = '2019-12-30';
DECLARE @To Date = '2020-01-05';
;
WITH [Sequence] ([Date])
as
(
    SELECT @From
    UNION ALL
    SELECT DATEADD(DAY, 1, [Date]) FROM [Sequence]
    WHERE [Date] < @To 
)
, [GroupedByDay]
as
(
    SELECT 
      fn.DATE
      , SUM(CASE SATISFACTION_LEVEL WHEN 'Dissatisfied' THEN 1 ELSE 0 END) as [Dissatisfied]
      , SUM(CASE SATISFACTION_LEVEL WHEN 'Satisfied' THEN 1 ELSE 0 END) as [Satisfied]
    FROM @Responses
    CROSS APPLY (SELECT CAST(ENTRY_TIME as Date) as [DATE]) as fn
    GROUP BY fn.DATE
)

SELECT 
    c.[Date]
    , DATENAME(WEEKDAY, c.[DATE]) as [Day]
    , ISNull([Dissatisfied],0) as [Dissatisfied]
    , ISNULL([Satisfied],0) as [Satisfied]
FROM [GroupedByDay] g
RIGHT OUTER JOIN [Sequence] c ON g.[DATE] = c.[Date]
ORDER BY c.[Date]

Date       Day                            Dissatisfied Satisfied
---------- ------------------------------ ------------ -----------
2019-12-30 Monday                         0            0
2019-12-31 Tuesday                        0            0
2020-01-01 Wednesday                      0            1
2020-01-02 Thursday                       1            0
2020-01-03 Friday                         0            0
2020-01-04 Saturday                       2            0
2020-01-05 Sunday                         1            1

(7 rows affected)

Without specific information about your schema and current query, that's about the best I can offer, however data by day should be more than enough for you group this into year on year results within SSRS...

• Or you could do it directly in SQL too if you want :)

---

#Update: Example where just the total count of days where there are no dissatisfied customers is returned:

DECLARE @From Date = '2019-12-30';
DECLARE @To Date = '2020-01-05';
;
WITH [Sequence] ([Date])
as
(
    SELECT @From
    UNION ALL
    SELECT DATEADD(DAY, 1, [Date]) FROM [Sequence]
    WHERE [Date] < @To 
)
, [GroupedByDay]
as
(
    SELECT 
      fn.DATE
      , SUM(CASE SATISFACTION_LEVEL WHEN 'Dissatisfied' THEN 1 ELSE 0 END) as [Dissatisfied]
      , SUM(CASE SATISFACTION_LEVEL WHEN 'Satisfied' THEN 1 ELSE 0 END) as [Satisfied]
    FROM @Responses
    CROSS APPLY (SELECT CAST(ENTRY_TIME as Date) as [DATE]) as fn
    GROUP BY fn.DATE
)
, [InjectedMissingDays]
as
(
    SELECT 
        c.[Date]
        , DATENAME(WEEKDAY, c.[DATE]) as [Day]
        , ISNull([Dissatisfied],0) as [Dissatisfied]
        , ISNULL([Satisfied],0) as [Satisfied]
    FROM [GroupedByDay] g
    RIGHT OUTER JOIN [Sequence] c ON g.[DATE] = c.[Date]
)
--Overall 
SELECT COUNT(1) as [Days with No Dissatisfied Customers] FROM [InjectedMissingDays] WHERE Dissatisfied = 0

Days with No Dissatisfied Customers
-----------------------------------
4

(1 row affected)