SSRS/SQL possibility

[TechMonkey]

I'm creating a report for the office and one thing they want is some data from multiple years. I can do that easily as seperate queries but I wondered if there is a more efficient way.

 

So does anyone know if there is a way to bring data into SRSS and then filter columns of a table or matrix by a variable? The columns would be current academic year (which I have as a Parameter), Academic year +1, +2, +3.

 

Ta.

[garbage46]

what does your current query output look like? Can you paste in the column header / field names here?

[TechMonkey]

what does your current query output look like? Can you paste in the column header / field names here?

 

Well this is an example

 

SELECT txtAdmissionsStatus, COUNT(txtAdmissionsStatus) AS statusCount
 FROM [TblPupilManagementPupils]
 WHERE txtAdmissionsStatus !='' AND intSystemStatus=0 AND intEnrolmentSchoolYear=@AcademicYear
 Group BY txtAdmissionsStatus

 

So where it has intEnrolmentSchoolYear=@AcademicYear I would like that to be different for each of the columns, which would be 2020, 2021, 2022, 2023, etc. The column names I can see how to do easily, I have a current academic year variable already so it is just a matter of +1, +2, +3 etc. The issue is can I change the data in that column based on that variable?

The simplest way would be to replicate that query x times and do the calculation in the SQL. It just seems a waste to carry out 5 queries when I could do one and filter the result in the table.

 

I was looking at using Column groups, but I had to adjust the SQL and currently this then creates a massive grid and each count is in a different section of the table.

 

Any ideas?

[garbage46]

I might have not understood correctly, but how about using the query to "filter" the years:

 

DECLARE @AcademicStartYear int = '2018', @AcademicEndYear int = '2021'

SELECT txtAdmissionsStatus, COUNT(txtAdmissionsStatus) AS statusCount, intEnrolmentSchoolYear

FROM [TblPupilManagementPupils]

WHERE txtAdmissionsStatus !=''

AND intSystemStatus=0

AND intEnrolmentSchoolYear BETWEEN @AcademicStartYear AND @AcademicEndYear

Group BY txtAdmissionsStatus, intEnrolmentSchoolYear

 

This should output

txtAdmissionsStatus, statusCount, intEnrolmentSchoolYear

Single, 500, 2018

Single, 400, 2019

Dual, 10, 2018

...

 

 

Then in the report matrix, columns grouped by intEnrolmentSchoolYear

Edited October 9, 2020 by garbage46

[TechMonkey]

Thank you!! It was because I was trying to cram it all in a table, not using a Matrix. The between is a good call and much more elegant.

 

Many thanks!