News for hackers. Stuff that matters.
Re:Show HN: My Single-File Python Script I Used to Replace S... by adamrezich on Thursday September 21, 2023 @03:03PM (#37602438)
I work for a school district. we use (an old version of) Skyward, a popular school management system. it's very common for various people in the district to want "reports" from Skyward's database. the way this works in Skyward is, you build the report file in a separate piece of software called Crystal Reports. you write an SQL query, and use Crystal Reports to format the report output. then, you go into Skyward, and create a new "Custom Report", and upload the Crystal Reports file. then you have to run it once (for some reason)—once it's been run, you can then schedule the Custom Report to be run periodically, and emailed out to whatever users you specify.

Crystal Reports is a robust piece of software that lets you do whatever you want when it comes to designing a layout. for example, I used it to design the new high school report cards for the district. but it also lets you output to CSV instead of PDF, which allows other secretarial staff to use it in Excel, and do whatever they want with it, like filter for values and such. when designing a to-be-output-to-CSV report, you don't do anything design-wise but drag the columns from the SQL query in that you want to export.

I found this process to be very tedious, plus time-consuming to maintain—if you want to update a query or adjust which email addresses receive the report, you have to make a bajillion clicks. so, I made a simple PowerShell script on one of our servers, which looks in a Rules folder, which contains subfolders of arbitrary depth, and, at the end, has a SQL file and a config file. the config file contains the schedule (daily, weekly, monthly), and the list of email addresses who should receive the report. the script takes a parameter for which schedule it's going to run, and Task Scheduler tasks are set up to run each different parameterized version of the script daily, weekly, and monthly. the script looks at all the Rules, determines which ones it should evaluate, evaluates them, saves the results to a CSV file, and emails them to the relevant staff. updating a "rule" is as easy as modifying either the SQL file or the config file. adding a new rules is as easy as making a new subfolder, and making a SQL and config file. the script has a parameter to do a "dry run", outputting the results in table form to the console, without sending an email. (though, for now, I still have to test the queries in SQL Server Management Studio first, because I can't figure out how to get Invoke-Sqlcmd to output linked server errors.)

this has been a huge boon for productivity and maintainability of CSV reports, allowing myself and my coworkers to easily create and manage these "data validation rules", such that our data analysts and HR people can get periodic updates on all sorts of various data validation issues, without having to do things the complicated Skyward way—and all it took was about a day of work setting up and testing the PowerShell script.

this is just one of many such PowerShell scripts I've written to automate things at work—PowerShell is great. I didn't know how to use it before taking this job, and none of my coworkers did, either. but for simple stuff like this, it's fantastic.

There are no comments on this comment yet.