SSRS Expression to hide all rows containing a field with a matching value based on the value of a di-CodePudding

I'm attempting to create a report that only displays currently inactive accounts and the services they used. The issue is that these accounts are identified by their location ID and the table contains historic data for them. Filtering out accounts with an active status will still return the old data where a previous account at that location was disabled and set to inactive. The locationIDs may not all have the same number of rows due to enrolled services.

Example Data:

LocationID	Account#	Service	Status
1234	1	Service 1	Inactive
1234	1	Service 2	Inactive
2345	2	Service 1	Inactive
2345	3	Service 1	Active
2345	3	Service 2	Active

In this sample the desired results would only include the first two rows, showing the inactive services for Account# 1. Account numbers are unique to a customer and would not be duplicated across other locations. Is there any method I can use to hide ALL of the rows for a locationID if ANY of them contain a Status of Inactive?

CodePudding user response：

I'd be inclined to do a correlated query. Perhaps something similar to the following?

drop table #test
create table #test (location varchar(100), account varchar(100), service varchar(100), status varchar(100))

insert into #test values ('1234', '1', 'Service 1', 'Inactive')
insert into #test values ('2345', '2', 'Service 2', 'Active') 
insert into #test values ('2345', '3', 'Service 3', 'Active') 


select location
    , account
    , service
    , status
    , case when (select count(*) 
                    from #test b
                    where b.account = a.account 
                    and b.status = 'Inactive'
                    )  >= 1 
                    then 'Yes' 
                    else 'Ok' 
                end as suppress_row
from #test a

CodePudding user response：

I'm not sure if you have an error in your question. You said the desired results would only show the first two rows (which are both 'Inactive') but then go on to say "hide ALL of the rows for a locationID if ANY of them contain a Status of Inactive".

Assuming you meant to say

"hide ALL of the rows for a locationID if ANY of them contain a Status of Active"

Then the following will do the job.

SELECT * FROM myTable 
    WHERE LocationID NOT IN (SELECT DISTINCT LocationID FROM myTable  WHERE Status = 'Active')