title: Display Only the Most Current Record on Report
body: <p>We have a report that outputs Table A. We added Table B to the report model (we added some fields to the report from Table B), which is a many-to-one relationship to Table A. Now when we run the report, we are getting multiple rows, which is to be expected because of the relationship between Table A and Table B.</p>
  
  <p>The problem is, we only want to show on the report the latest record of Table B, based on "creation date".</p>
  
  <p>I tried to set a MAX(!fields.CreationDate)</p>
  
  <p>I found information such as: <a href="https://social.msdn.microsoft.com/forums/sqlserver/en-US/2bc16c90-21d6-4c03-a17f-4a5922db76fe/displaying-records-by-max-date-in-ssrs" rel="nofollow">https://social.msdn.microsoft.com/forums/sqlserver/en-US/2bc16c90-21d6-4c03-a17f-4a5922db76fe/displaying-records-by-max-date-in-ssrs</a></p>
  
  <p>But when I do something like this, I get a "cannot use aggregate function......" error.</p>
  
  <p>If this was a SQL Statement for TableB, it would be along these lines to display only the most recent record:  </p>
  
  <p>SELECT DISTINCT
      [ID], [PID], [InputDate], [Changed_Date]
  FROM
      (SELECT
          [ID], [PID], [InputDate], [Changed_Date],
      dense_rank() over (partition by PID order by Changed_Date desc) as MyRank
      from TableB
     ) as RankSelect
  WHERE MyRank = 1
  ORDER BY PID</p>
  
  <p>This gives me the most recent record for TableB.  I know technically I could add a view or something to the report model, but I do not want to do this, as another report ran might want a historical of all records in TableB.  So I am hoping to somehow incorporate the above results into the report without touching the report model.  In which only the latest record from TableB is incorporated into the report. 
  Would appreciate any help on how we can limit the report to only displaying the latest date record from Table B.</p>