SSRS Chart on newest date for each row group

0
votes

I have an SSRS report that contains a group where the row DataSet contains a date-time column in addition to other value columns. I want to place a pie chart at the top of my report that indicates totals from the newest row based on the date-time column within each group. How can I do this?

Here is a simple snippet of what the report looks like. The pie chart would basically total up all of the Outcomes for the newest "Started DT" row for each "Test Case" group. So, in the example report below, the chart would have a total of 1 "Passed" and 2 "Failed" on the pie chart.

I'm really having a hard time figuring out how to do this. I've tried adding a variable to the group that contains the Outcome from the first row, thinking that I could reference the total of the variables in my chart. Problem is..., SSRS won't let me put an aggregate expression on a Group variable.

enter image description here

1
reporting-servicescharts
This would be relatively easy to do in the SQL. Can you add a ROW_NUMBER to your data? ROW_NUMBER()OVER(PARTITION BY TEST_CASE ORDER BY STARTED_DT DESC) AS ROW_NUM Then you could just filter for ROW_NUM = 1. – Hannover Fist
Yeah, I thought about doing that, but I was hoping I didn't have to do it in SQL because the report is using a stored procedure that other reports use. One other idea I had after I posted this question was to maybe try setting a placeholder in an invisible totals row that got the Outcome from the last line in the group, which is sorted by Started DT. I haven't tried it yet, but I will post the results of my attempt when I finish it. – Ken
you could execute your Stored Proc into a temp table then do what you like with the temp table, such as adding the suggestion from Hannover. That way you don't break the SP but can still do the work in SQL – Alan Schofield

1 Answers

0
votes

I came up with a workaround. I added another dataset to my report that calls the same stored procedure, but with different parameters that restrict the number of days being reported to the newest date-time. This solution only worked for my particular situation because the stored procedure had a param that would allow me to get the data that I want.