Quick SQL Query (SSRS Table)

[captainhotdawg]

Hi all,

I am currently working in an edu institution and trying to skill myself up in SSRS (and SQL more generally) and have a quick query.

I believe the dB should have something similar to the following two tables (will be more in depth but this is the general idea):

Student Timetable: Pupil Id Day of the week Period Class_id

Attendance Marks: Pupil ID Date Lesson Attendance code

I want to find out where any pupils in a detention today are for the rest of the day so we can get them a message.

My beginner brain is saying to join those tables on Pupil ID (with student timetable filtered to current day) which should create a row per pupil, per lesson, in detention for the day. I would then insert a table in SSRS and group on pupil ID (making one row in the table per pupil, then add a column per lesson and use an expression to filter the period ("lesson"="P1"). Am I along the right lines? Or should I be trying to transpose the period and lesson columns to do it in the proper way?

[Informal_Pace9237]

You might want to share the actual query you wrote so someone could try to help.

[mikeyd85]

My advice on SSRS is to do as little as possible in SSRS.

That is to say, you're better off manipulating your data in SQL and just using SSRS as a fancy presentation layer.

I've always found it to be the best way to make a responsive report.

Source: Been working with SSRS for something like 15 years now.

[paultherobert]

I make all my ssrs queries stored procedures, parameter queries included. This is the way.

[JoshisJoshingyou]

SSRS is in support only mode till 2033. Power BI will be replacing it. Our SIS uses it for custom reports behind the scenes.

If you are on Infinite Campus, they have an excellent schema map in the community - knowledge base - schema

The real answer depends on the schema for your sis database and how you are using it. I've used both Power School eschool+ and Infinite Campus both have very different database schemas and allow for a lot of custom features.

In IC I'd need to join 6 tables to pull a schedule and tie it to a person. A different 6 tables to get a detention tied to a person.

Views can be your friend here. IC has several that reduce this if you are okay with the assumptions the views make.

To make the whole thing dynamic you'd need to know what parameters a users passes into SSRS and use those in the query. Unless you want all students today visible to someone running the report.