0

I have a dataset that consists of CommentDateTime, CommentCode, Comment and CommentKey. There are multiple comments per a CommentCode but I only want the first comment created for each CommentCode to show up in my report.

Is there a way to do this?

I am new to SSRS so I am sorry if I didn't explain my question super well.

user17482884
  • 3
  • 1

1 Answers1

0

Unless I'm missing something, it would be better to do this in your dataset query so that only the first comment is returned rather then sending lots of data to the report and then writing expressions to filter it out.

Something like

SELECT a.CommentDateTime, a.CommentCode, a.Comment, a.CommentKey 
    FROM (
        SELECT 
                CommentDateTime, CommentCode, Comment, CommentKey,
                ROW_NUMBER() OVER(PARTITION BY CommentCode ORDER BY CommentDateTime) as RowN
        FROM @myTable
        ) a 
    WHERE a.RowN = 1

This assumes CommentKey is unique to each comment. WHERE a.RowN = 1

Alan Schofield
  • 19,839
  • 3
  • 22
  • 35
  • This worked perfectly. Thank you so much. If I wanted to add this to an existing query within Query Designer would I make it an Inner JOIN? – user17482884 Nov 23 '21 at 00:34
  • It depends on what you want to achieve but you will need to ask a new question to get an answer for this. Just remember to include your existing queries, sample data and expected outcome, if you do that, somebody will provide an answer quite quickly. – Alan Schofield Nov 23 '21 at 09:14
  • Thank you so much! – user17482884 Nov 23 '21 at 16:46