SSRS for iSAMS... beginners guide?!?!

[CyBeRkId2002]

Hi all,

 

We are just migrating to iSAMS and hoping to up-skill a bit on SSRS to be able to pull some reports that we cannot find already included in iSAMS.

 

I have tried starting on what I thought would be an easy-ish one.  Our attendance team would like a simple spreadsheet that shows 'N' for the AM reg, and which lessons pupils should be in following that for the day (so the attendance team can quickly visit classrooms to check they are not marked incorrectly),.  

 

I have found the VwRegistrationSchoolRegistrationAttendance and filtered on N which shows who has received an N for the day but am really struggling to find how to bring in Timetable information.

 

Could anyone help with the below:

• What are some of the best tables to pull timetable information in?  Am I right in thinking it is going to be a really complicated lookup of the below (hoping there is a much easier way):
  • Finding the period identifiers for the current day
  • Getting a list of all classes for pupils with an N code
  • Cross referencing these two to find out a unique session id that relates to that very specific lesson
  • Looking up the room in another table
  • Somehow presenting the above in SSRS
• What I am really struggling with on the above is that there seems very little consistency to Primary Keys within the database?  I keep finding what I think are unique identifiers (and they must be somewhere to make it all work!!!) but everytime i cross-reference these to another table where logically they should match it is not correct and I assume there is another matching table somewhere... for someone with limited database knowledge it really is like a spiders web!

 

More widely, can anyone recommend any SQL / SSRS e-learning so I can really get to grips with things?

 

Thanks,

Michael

[TechMonkey]

I would recommend the iSAMS training courses.  It isn't going to get you a certificate or gain you a new report developer gig anywhere else, but they go through what is best practice, how things are generally organised and can help you make your first report.

 

One thing I would say is that for office staff, using the UI would probably be better.

 

Having just done a similar exercise all the registration tables start TblRegistationSchoolRegistrationXXXX, with the main one being TblRegistationSchoolRegistrationPupils. You then link this to TblPupilManagementPupils to get pupil names linked by txtSchoolID. There is also TblRegistationSchoolRegistrationRegister, for the actual register details (date/time/friendly name etc) and TblRegistrationSchoolRegistrationDateTime that has the individual slots.  Below are my joins for a report to find the registers for a single set within a time period.  Not going to claim it is super efficient or a perfect example but it is doing what we need so far.

 

FROM [TblRegistrationSchoolRegistrationPupils] as Registrations
JOIN [iSAMS_Woodbridge].[dbo].[TblPupilManagementPupils] as Pupils ON Registrations.txtSchoolID=Pupils.txtSchoolID
JOIN [TblRegistrationSchoolRegistrationRegister] as registers ON Registrations.intRegister=registers.[TblRegistrationSchoolRegistrationRegisterID]
JOIN [TblRegistrationSchoolRegistrationDateTime] as regSlots ON registers.[intRegistrationDateTime]=regslots.[TblRegistrationSchoolRegistrationDateTimeID]
JOIN [TblStaff] as staff ON staff.User_Code = Registrations.txtSubmitBy

 

[CyBeRkId2002]

Thanks TechMonkey... some really useful tips and pointers.  Unfortunately I am still struggling and think this report writing malarkey may be beyond me.

 

All I am trying achieve is the below:

 

Name	P1	P2	P3
Bob Smith	7Ar1	7Te3	7Fr1

 

At the minute my report brings in multiple rows for pupils with an N for the current day, each listing the additional lessons they are in.  This is great and I will filter the output to the correct periods in the report view (or transpose the periods... not decided yet.

 

SELECT TOP (1000) RegNCodes.[intRegistrationDateTimeID]
      ,CONVERT(Date,RegNCodes.[dtRegistrationDateTime]) As Date
      ,RegNCodes.[txtRegistrationName]
      ,RegNCodes.[intRegistrationPupilsID]
      ,RegNCodes.[txtRegistrationGroupName]
      ,RegNCodes.[txtRegistrationStatus]
      ,RegNCodes.[txtCode]
      ,RegNCodes.[txtSchoolID]
      ,PupilDetails.[txtPreName]
      ,PupilDetails.[txtPreferredSurname]
      ,RegAllPeriods.txtRegistrationGroupName
      ,RegAllPeriods.txtRegistrationName
  FROM [iSAMS_LGST].[dbo].[VwRegistrationSchoolRegistrationAttendance] AS RegNCodes
  --This Joins Pupil Information to pull in name --
  JOIN [iSAMS_LGST].[dbo].[TblPupilManagementPupils] AS PupilDetails ON PupilDetails.[txtSchoolID] = RegNCodes.[txtSchoolID]
  --This is a self-join on Registration Attendance to pull in the additional classes for the day
  JOIN [iSAMS_LGST].[dbo].[VwRegistrationSchoolRegistrationAttendance] AS RegAllPeriods ON PupilDetails.[txtSchoolID] = RegNCodes.[txtSchoolID]
 -- This filters to ensure only N codes are for AM Registration.  GetDate needs tidying to reformat the date field and remove date/time for a better comparison not relying on -1 --
  WHERE RegNCodes.txtCode= 'N'  AND RegNCodes.dtRegistrationDateTime > GETDATE() - 1  AND RegAllPeriods.dtRegistrationDateTime > GETDATE() - 1 AND RegNCodes.txtRegistrationName = 'AM Register' 

 

What I am really struggling with is matching up the classcode that is brought out with any kind of timetable information to show where the lesson is happening!  Is there a table that holds ALL class information, a row for every period along with rooms and teachers?  The closest I have found is called something like TimetableImport but I worry that this isn't the information used by the system and if manual changes to the timetable happen from within iSAMS this wont be 100% correct.

 

I may put this down for now and come back to it over half-term!!!

[DTVincent]

This is an adventurous project and not one I would tackle as a beginner...!

 

Your biggest difficulty here is that the Registration data is held entirely independently of the Timetable data.

 

There's an easyish way to achieve your example output (Name, P1, P2, P3) with set names in the columns, but tying that to room information is trickier.

 

You might normally think to tie various tables together based on the txtRegistrationGroupName in vwRegistrationSchoolRegistrationAttendance, and look up which set code matches that in a table like TblTimetableManagerSchedule (e.g. txtCode) .. but classes can be timetabled into different rooms over the course of a week, so that won't work.

 

I've not got a solution for you, but I can see many of the hurdles you are facing. Whatever the solution is, I'm afraid it extends far beyond any beginners guide on SSRS/SQL.

 

Have you had any further luck this half term?

[CyBeRkId2002]

On 20/02/2026 at 13:49, DTVincent said:

This is an adventurous project and not one I would tackle as a beginner...!

 

Your biggest difficulty here is that the Registration data is held entirely independently of the Timetable data.

 

There's an easyish way to achieve your example output (Name, P1, P2, P3) with set names in the columns, but tying that to room information is trickier.

 

You might normally think to tie various tables together based on the txtRegistrationGroupName in vwRegistrationSchoolRegistrationAttendance, and look up which set code matches that in a table like TblTimetableManagerSchedule (e.g. txtCode) .. but classes can be timetabled into different rooms over the course of a week, so that won't work.

 

I've not got a solution for you, but I can see many of the hurdles you are facing. Whatever the solution is, I'm afraid it extends far beyond any beginners guide on SSRS/SQL.

 

Have you had any further luck this half term?

Managed to get it sorted a few days after I posted this.  I couldn't explain what the code does not, but if you are interested here is the query that pulled in everything I needed

 

[CyBeRkId2002]

Sorry -  for some reason it won't let me post it... even as code