CODEWITHSUNDEEP
reporting-servicesssrs-2008ssrs-2008-r2
Kocheese
Kocheese

Reputation: 25

Display Only the Most Current Record on Report

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.

The problem is, we only want to show on the report the latest record of Table B, based on "creation date".

I tried to set a MAX(!fields.CreationDate)

I found information such as: https://social.msdn.microsoft.com/forums/sqlserver/en-US/2bc16c90-21d6-4c03-a17f-4a5922db76fe/displaying-records-by-max-date-in-ssrs

But when I do something like this, I get a "cannot use aggregate function......" error.

If this was a SQL Statement for TableB, it would be along these lines to display only the most recent record:

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

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.

Upvotes: 0

Views: 3286

Answers (2)

Kocheese
Kocheese

Reputation: 25

I couldn't find a resolution to filtering the data in the report itself, so I removed TableB from the report model and replaced it with a view of TableB that only returns the most recent record as linked to TableA. Not ideal, as I know at some point they will ask for a report to "show all entries" from TableB, but will cross that bridge later :-)

Upvotes: 0

Hannover Fist
Hannover Fist

Reputation: 10860

For your table B, set a FILTER for the CreationDate based on the MAX date over the dataset.

enter image description here

This will only display the records where the CreationDate matches the MAX CreationDate from your dataset.

Upvotes: 1

Related Questions