April 14, 2016 at 10:47 am
All:
We are using a report model for this and not a direct query.
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.
Would appreciate any help on how we can limit the report to only displaying the latest date record from Table B.
April 14, 2016 at 12:17 pm
Should typically work if its a date datatype.
April 14, 2016 at 12:37 pm
Unfortunately, it is not. I am getting an aggregate error trying to add a filter or it simply doesn't work.
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.
April 15, 2016 at 11:17 am
I couldn't find a solution to the report for filtering the data, so I removed "TableB" and created this as a view that only returns the most recent record. It fixes the issue for the present, but have a feeling in the future they will want a report listing all possible records from TableB, but will worry about that then 😀
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply