Any iSAMS users with in-house SQL skills...

[CyBeRkId2002]

Dear all,

 

We are currently investigating iSAMS and I am keen to understand the complexities involved in making tweaks to the reports (both small and large) in-house using SSRS or similar.  I wondered if anyone who is currently doing this would be kind enough to let me visit (more than happy to travel a few hours) and see how it all works and have a chat to the data team so I have a better understanding of the process and skillset needed.

 

I have used SQL very basically before, and more recently PowerBI, but not touched SSRS yet.

 

Any offers most gratefully received.

 

 

[dhicks]

23 hours ago, CyBeRkId2002 said:

We are currently investigating iSAMS and I am keen to understand the complexities involved in making tweaks to the reports (both small and large) in-house using SSRS or similar.

We get data from iSAMS via their API, rather than directly as an SQL query. Their "batch" API is actually more like a query language anyway - you get a bunch of XML data back that you can then process into whatever format you prefer, or put directly into something like PowerBI. If starting a project, I would probably aim to go with a tool like PowerBI (although, myself, I would probably choose a more open tool that allowed me more control over what I did with the resulting reports), SSRS is a bit more in the previous generation of data analysis tools.

[CyBeRkId2002]

Sorry  for not replying sooner - I totally missed the notification!  @dhicks - Is there any chance you could spare 30 minutes for a Teams call at ANY point over the summer break to show us a little bit about how this looks... how the data is pulled, organised etc.  Would be massively beneficial to us and hugely appreciated.

Regarding the tooling - I am fairly familiar with PowerBI and reasonably adept at manipulating data with it, producing dashboards etc.  What I am less familiar with is using it as a tool to produce more traditional reports for dissemination.  I suspect this is what you mean regarding a more Open Tool... are there recommendations on others out there?

[dhicks]

15 hours ago, CyBeRkId2002 said:

@dhicks - Is there any chance you could spare 30 minutes for a Teams call at ANY point over the summer break to show us a little bit about how this looks... how the data is pulled, organised etc.

 

Yes, of course. I should be available most of today or next week - I'll send you a separate private message.

 

With our setup, we get data via the iSAMS "batch" API once per night, then process that data into various places (class / yeargroup lists, etc). We also use the data for account creation and so on. We have a set of Python scripts do that - the script that gets the data in XML format from iSAMS is fairly simple, it should convert to other languages easily enough if wanted, or i suspect Power BI might be able to get and parse the XML data directly itself these days.

 

For reporting, we have a separate group-wide project that is hauling data out of various systems to produce reports, so we're aiming not to reproduce any of that functionality just for our school. I've tended to use Python for reporting with the ReportLab library, which produces PDF output - snazzier Javascript-based report libraries are available, but that's not something I've looked into much myself.

[zapbrannigan]

We have ISAMS and I use direct SQL queries for everything. Some things we automate through powershell (eg. daily teacher absenteeism emailed to SLT) or python (in house dev stuff), and for reports its direct querying using Visual Studio. ISAMS db schema is very complicated because it has been modified and added to over the years by various different people with different visions and different technical ability. If you are looking to improve your SQL skills then there is no better database to cut your teeth on because there is nothing more chaotic or Frankenstein that that db.

Once you figure it out though youre good.

[vikpaw]

On 05/07/2025 at 17:09, zapbrannigan said:

We have ISAMS and I use direct SQL queries for everything. Some things we automate through powershell (eg. daily teacher absenteeism emailed to SLT) or python (in house dev stuff), and for reports its direct querying using Visual Studio. ISAMS db schema is very complicated because it has been modified and added to over the years by various different people with different visions and different technical ability. If you are looking to improve your SQL skills then there is no better database to cut your teeth on because there is nothing more chaotic or Frankenstein that that db.

Once you figure it out though youre good.

Agree, the schema is complex. We have some dedicated report writers in-house as it's a lot for a school to pick up unless you have someone very interested/dedicated.

 

Interested to know how you're running the powershell routine. Is it fully cloud, or does it need to be on a pc / vpc?

 

We're looking at how to automate some emails because the built in method is ok, but you have to get them to set it up, and for every change, so it's not really self-service.