elJukes

Hi, Not sure this is the right forum anymore, apologies if not! A few years back I wrote a PowerQuery function to pull IMD data from postcodes. Sadly shortly after sharing it with everyone the OS data service shutdown causing it to stop working - but I have written a new version! The script is a PowerQuery function that can pull the score, rank or decile of any of the 2019 (latest) IMD domains - including IDACI. All you need to use it is a column in one of your data tables which contains postcodes. When added to PowerBI report the function will run every time the report data is refreshed, meaning as more postcode data is added to your data tables, the function will collect the corresponding IMD information you asked for - saving you doing it by hand. It pulls the data directly from ONS and DLUHC services so you don't need to host a huge lookup table yourself. The script, along with instructions for using it can be found here: https://github.com/olicatschools/IMD-Lookup-Script/tree/main I hope it's of some help!

Hi Jon When you're in PowerBI click on the Edit Query button to bring up the query editor. If you right click on the left hand pane, select New Query and then Blank Query to create a new query with nothing in it. Once you've done that find the Advanced Editor button along the top bar to open a code editor for the blank query. Just copy and paste the code from GitHub into the query editor to get things rolling. @Marci has spotted a potential issue with automatic refreshes that we're going to look at once the start of term madness has simmered down a bit. Hope they help!

I've adapted a SPARQL query I found on StackOverflow that looks up either the Decile, Rank or Score for any of the 10 IMD domains including IDACI for a postcode, and turned it into a PowerBI function. Using the Invoke Custom Function option to add a column into a dataset such as student address details you can use the function to quickly get the IDACI ranking for each student in your school. More details can be found here As before, if anyone does anything innovative or interesting with this please share it with the community. I'm going to have a play with maps and disadavantaged funding adjustments!

Some of the datasets published via the Compare School Performance website are only available as Excel files. To accomodate that the Performance Tables script has been updated to support sources in Excel format.

I've found a bug with the Performance Tables script when trying to download certain data sources. Need to check something with the DfE website, but will post here when it's all fixed.

Hi Not sure which forum to post this in, but seeing as PowerBI is mentioned here a lot I went with this one. I put together a couple of Power Query scripts that can be used in either PowerBI or Excel, to connect to the Get Information About Schools and Compare School Performance websites and download their latest national datasets. The scripts are here and a freely available to anyone that wants to use them. There's a brief description in the ReadMe about how to change the PerformanceTables Data script to pull different datasets (ks2, ks4, ks5, census, spend per pupil, destinations etc) Both of these work at the time of posting. All I ask is if you find any innovative uses for these or adapt them, then share what you've done. It's great seeing what others come up with!

We moved from SIMS to Bromcom a year ago. The physical transition of information was very smooth, we had almost daily contact with the mirgation and support teams throughout. They gave each of our schools a test system using a sample of their actual data so they could train and explore for a couple of months before the switch over. One of the reasons we decided to look at other MIS packages was because we saw that SIMS were building a new product in the cloud & so knew the current system had a limited self life. We thought if we were going to have to move data and more importantly retrain a lot of staff on a new SIMS MIS why not see what other products were out there. Our Secondary behaviour lead has found the alerts and auto-escalation features really useful in keeping staff and parents up to date with goings on. Assessment is a bit of a step up too, the build tools are very familiar but we make one template and tag it for X subjects a voilá we have a whole school monitoring system in half the time it took us in SIMS. I find the company to be very open to discussion, we're developing our own PowerBI dashboards using live feeds from the MIS and they've been really helpful in not only getting data out but working with PBI to get what we want. And of course they put us in touch with @deshills ( not sure that's a good thing from his perspective!! ).

We are starting to use Power BI for various analysis, from attendance to behaviour and assessment. I found a lot of resources online, including the PBI community of the Microsoft site, to be quite helpful. In terms of setting up a data model I got a copy of the Kimball Group's data warehousing book, which goes through Dimensional data structures. We use Bromcom as our MIS which has a Power BI viewer embedded into it so we can publish reports to staff through the MIS. The report refreshes itself once every hour from data in Bromcom so people get quick access to analysis. One of the languages Power BI uses is called DAX and is very similar to Excel formulae. You can even use DAX in Excel through Power Pivot. There's lots of advantages to using Power BI, once you've set up a report the system can deal with extracting the data from your source, cleaning it and presenting it. It is by no means the end of Excel, ad hoc analysis and quick figure production are generally a lot quicker in a spreadsheet rather than setting up a dimensional table model. Our aim is to leverage the two. Power BI to provide a set of standardised reports that are self-sustaining, to provide quick and on demand analytics to the masses. This frees up time for me to focus more on data quality, turning analytics into insight and exploring other avenues of interest.

Hi ajp233 Yes we got the PDF as well, but the PDF is one file with everyone's report in one after the other. I can't then print those as individual booklets per student.

Hi ajp233 Thanks for your reply. We've had the system split documents out to the DMS before, our problem was then downloading them all to print en mass. Do you know how we can download a year groups worth of documents at once from the DMS?

Hi DODICT When working with single page reports everything works fine, you get a single document with everyone's data in with a page per student. I've not had much cause to use the advanced reporting tool so don't know if that would work, but for making multi-page documents of varying length per student this was how we do it. Best of luck with the migration. If you want to talk a bit about it feel free to drop me a DM. Hi Arthur Thanks for that, I tried changing those before but it didn't seem to work. Perhaps I've missed a step, will have another look soon. Thanks!

Hi Every year our Secondary school produces written reports in the form of A5 booklets. We used to do this in SIMS via profiles which collated the reports and produced one document per person. In the last year we moved to Bromcom and found the process of collecting the written report information rather straightforward, however we ran into an issue when we tried to print the reports. Instead of producing a document per student, the MIS produces a single document containing all of the reports for the students we've asked for. The document does collate one report after the other so students pages aren't mixed up. Initially we considered trying to programme the printer to collate every X pages into a booklet, but the variety of needs and abilities at the school meant some students took a different number of subjects making their reports different lengths. We also considered running the collate routine for each student individually however that was considered to be too time consuming. In the end we developed a different solution. Cobbling together code snippets and help guides from the internet we managed to build two macros for Word. The first looked for a tag which we've hidden at the top of each students report and would split everything from that tag to the next into a separate document. The second macro overcomes a limitation with Windows which only allows somewhere in the region of 15 documents to be batch printed at a time. Using the second macro we have been able to send 200 odd word documents to the printer at once. In case anyone else is looking to do some batch printing from Bromcom like us, I thought I'd share the macro code here. I make no claim on these, they are stitched together from a number of websites (too many to remember unfortunately). To use the splitter include at the top of your document and be sure to check the end of the last document split out - for some reason it appears to sometimes add the tag on to the end of the last page of that final document. Hope it helps ReportingTools.txt