Extract Student Assessment Grades from BROMCOM to Excel?

[SMAS59]

Hello,

 

Please can anyone tell me how to extract student assessment grades (that would have been done on marksheets in SIMS), to Excel? I cannot seem to find a specific help sheet through Bromcom itself.

 

Thank you in advance.

[noelmm]

Off the top of my head you can either export the data from assessment sheets if you only need one or two, or you can do this via the View/Amend Assessment data in the Assessment module.

[SMAS59]

Thank you for your quick reply. At set times in the year Staff enter grades into the marksheet for their specific classes. I then would use one marksheet to pull all data for a particular year group and export to Excel ready for SISRA. All of course in SIMS. We have swapped to Bromcom since half term and although we have written our new templates, it's my job to extract it all to Excel as usual. Is that a different process to what you first mentioned please? I am a complete novice and left to train myself. Regards

[Linfit]

We are also new to Bromcom and doing our first data drop in it now.

 

I have created a summary marksheet which shows all the assessments for each year. Once this is populated its very easy to pull the data out as its the exact same process as in SIMS.

Setting up a summary marksheet is not as straightforward though - as each template must have an associated base subject we used a dummy subject called Summary to create the marksheet and then pull each actual subjects assessment as a column.

This template is then associated with the relevant year group.

 

You can get data out through Manage Data - apply a filter for the relevant assessment/subject combo and it appears in the grid and you can export it, but this only allows you to see one subject at a time, not all the subjects.

[SMAS59]

Many thanks for your help. I will give it a try.

 

Kind regards

[noelmm]

It sounds like you may be better off writing a report to extract the data. We run a custom report for our data extract and then use that to upload into Sisra.

[SMAS59]

I think you're right actually. I will try this. Many thanks.

[Marci]

The "best" (subjectively) way to do this is to connect to the oData feed via Excel > Data > Get Data > From Other Sources > From oData...

 

URL: https://cloudmis.bromcom.com/nucleus/odata

 

Basic Auth...

Username: schoolid_number

Password: API Key from the "My Account" screen in Bromcom.

 

Connect and it'll grab the metadata from the feed and present the table list.

 

 

• Tick "select multiple..." at top of list.
  
• Tick Students table, & AssessmentResults table (and if you want Bromcom's calculated P8 data from the KS4 Performance Dashboard, select the Headline Figures table - but doubtful for SISRA purposes)
  
• Click "Transform Data" button.
  
• Filter AssessmentResults to the term / subject / datapoint / daterange as appropriate (you can use Parameters to set up date ranges / terms etc that feed into your filters) - note as you do this each step you perform appears in a list on righthand side. You can select each step and go back and forth...
  
• Filter Students table to the yeargroup / cohort as appropriate.
  
• Merge AssessmentResults with Students table on StudentID. Remove any excess columns you don't need.
  

You can then split the merged table on assessmenttype (eg: current & prediction) into two separate tables (right click and duplicate your now-merged students table, filter one to one assessment type, the other to another)

Do all your formatting changes etc in Transform Data (PowerQuery) to fit your SISRA upload structure (ie: pivot Subject column using Result as value, no aggregation, to make a Broadsheet)

Now next time you need assessment data, you can open the same file, head to Data and click "Refresh All" and it'll connect to Bromcom and pull thru latest data (including student characteristics - eg: change to preferred name, change of FSM / PP status or SEN status etc), it will step thru and perform all the transform steps, and give you a set of sheets ready to save as CSV (one for each assessmenttype) and upload / select & copy-paste / however whatever analysis suite ingests your data.

 

We do this MAT-wide (same process but connecting to the VISION oData feed instead) to harvest all assessment data for all our schools and pull them thru for reporting / building analysis packs / whatever etc (majority of this is automated using PowerAutomate Desktop)

 

This is also the same way that PowerBI works when it comes to ingesting datasources (ie: PowerQuery)

Edited March 23, 2023 by Marci