SSRS Hiding Row if Same Field in Other Row

Question

Hello everyone!

I'm relatively new to SSRS and coding in general but I know there's a way I can get this solved that I just can't seem to figure out.

I have a dataset that's returning with the same information in two rows with only one variation between the two rows--we'll call it field STATUS.

If I have STATUS in the first row that says "ASSIGNED" and the second row that says "UNASSIGNED" I would like to be able to hide both rows from my data altogether. I also would like that data to not show up in the overall count of the returned data. I've tried various things and know in Crystal Reports I was able to use Previous and Next to tell the program not to return information if FieldA was the same as the previous or following field in a dataset but I can't find a way to make this work in SSRS.

I've figured out numerous ways to hide the STATUS row if it says "UNASSIGNED", most helpfully if I filter on the tablix or query selection to not include data with this field entered.

I also don't know if I need to put the coding I use on the group, details row, tablix in general, or query selection.

Any help would be appreciated! Thank you very much.

Answer 1

Hi @DesireeMSN ，
In SSRS, Previous Function is still supported. The difference is that Next Function is not supported. One way is to sort your dataset vise versa (ie if it is sorted by PK descending, then sort it by PK ascending and vise versa), then use Previous function.
I think a more reasonable way is to filter out the data you need when you create a dataset query.
Best Regards,
Joy

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi DesireeMSN-8681,

Problems like this are usually best handled in your SQL. Assuming that you only want to see row id's that have a status = 'Assigned', and only if those id's have not been subsequently 'Unassigned', here's a query example:

select
    a.id,
    a.status
from
    MyTable a
where
    a.status = 'Assigned'
    and a.id not in (select id from MyTable where status = 'Unassigned')
order by
    a.id,
    a.status;

Of course, your query will h have additional columns, etc., but hopefully you get the idea.

The above query will only return rows that have been Assigned that do not have a matching Unassigned row.

I hope this helps.

--Dan

Answer 3

Hi Desiree,

Without seeing your schema, it's hard to pinpoint the problem, but I'm guessing it is due to the statement

AND Reporting.[Case].Number not in (select Number from Reporting.[Case] where Reporting.Investigations_AssignmentStatus.Status = 'Unassigned')

The embedded select statement looks wrong to me ... I'm surprised that it doesn't raise an error, because there is no obvious way that the Case and Investigations_AssignmentStatus tables join together.

I would think it should read:

AND Reporting.[Case].Number not in (select distinct Number from Reporting.Investigations_AssignmentStatus where Reporting.Investigations_AssignmentStatus.Status = 'Unassigned')

Once again, this is just a guess, because I don't know the schema, or the column names in your table.

Good luck.

--Dan