I am trying to write a report for SSRS where a user will slect multiple Fiscal Years (2020, 2019, 2016) and I have to show the data for each of these years in as many rows as Fiscal Years selected. I am trying to figure out a way to do a FOR loop so I can get one row for each Fiscal Year. What's the best way to do so in SQL or SSRS? I don't want to use a cursor.
0
votes
1 Answers
0
votes
Create a matrix and use a row group, grouping on fiscal year??
Another approach would be to next a matrix inside a matrix grouping by fiscal year and filter the nested matrix by the parent fiscal year.
So, if using the matrix wizard, you would have fiscal year as first row group, then ethnicity, no column group and your measure in values.
That will then allow you to total by both year and by ethnicity. Keep your SQL simple, counting at the smallest granularity and use SSRS to aggregate,
