Rollover from EMIS Web UI [2021:HPR]

[Ghislain Hachey]

Instead of the rollover from the excel workbook we discussed the possibility of rolling over from the EMIS Web UI. This would help with the issue of re-introducing duplicates every year.

Related issues:

• #1029 (high priority to be fixed in the current workbook)

While this is very useful since we already have a rollover feature and this is unlikely to be ready for this coming rollover this work should only be done once all the other works have been completed.

[Brian Lewis]

Documenting here an issue with epplus v4.x.x.x that impacts on how this will be deployed. This relates to problems in handling macro-enabled workbooks so that the macros are usable in Excel.

Findings:

If the VBAproject is initially created by epplus, and the workbook has never been saved by Excel, then
a) the VBA project can be opened, edited and saved in epplus
b) the macros and UDFs in the VBA Project will work in Excel.

If the macro-enabled workbook has been saved in Excel, then VBaProject cannot be accessed in Epplus.

If the macro-enabled workbook has been saved in Excel, and then the workbook is opened and saved by Epplus:
the macros will not function in Excel.

This presents problems if you have a macro-enabled workbook you want to populate or modify using epplus. You can workaround using these steps:

a) open the macro-enabled workbook (.xlsm) in Excel, and export all the VBA modules to text files.

b) Save the .xlsm as .xlsx; ie drop the VBA project.

c) Open the .xlsx project in epplus, and make whatever changes you want.

d) Reinstate the VBAProject: use CreateVbaProject() to create the new VBA project, then import all the modules from the text files you exported in step a)

e) Save the epplus package, to a new file with extension .xlsm.

In the context of the Census, this means we need in ``assets`` folder:

1) the census template - without Vba code - saved as .xlsx. This is the result of tep b) above.

2) a subfolder ``code`` containing all the exported Vba modules. These are loaded back into the output when the census workbook is created.

‌

‌

[Ghislain Hachey]

Can we use the following convention for the filename of exported workbook.

• All_Schools_WORKBOOKVERSION_BOY_SY2021-2022.xlsm (for district version)
• District_WORKBOOKVERSION_BOY_SY2021-2022.xlsm (for district version)
• SchNo_WORKBOOKVERSION_BOY_SY2021-2022.xlsm (for district version)

‌

[Ghislain Hachey]

All lookups should be refreshed at the moment of exporting the workbook ensuring any newly added data is part of it without user intervention.

[Brian Lewis]

Enhancements 25 August 2021:

– conditional formatting from template is applied to all table rows

• fields are locked iff they are calculated
• empty last lines of tables removed
• pivot tables recalculated on open
• some UDFs (calculating District, SchNo and Public/Private) are removed and replaced with VLOOKUPS for better performance.
• performance enhancements to table loading.

Known Outstanding Issues

output workbook name as above

formatting of dates and numerics…

‌

‌

‌

‌

[Brian Lewis]

Issue to consider in student rollover - currently a student is rolled over if:

Completed = 'Y' AND they are not in the highest grade taught by their school.

Outcome = To Repeat

Outcome = Transferred Out and School Transferred To is provided.

Students other - in particular where Completed is null - do not get rolled over. @ghachey In this case , is it better to assume they are continuing?? ie treat Completed is null same as Completed = 'Y' ???

‌

‌

[Ghislain Hachey]

Yes, treat Completed nulls as Completed Yes. We’ll keep on trying to get them to be more thorough in their submissions. Perhaps add a warning when loading this after end of school date. I will add this comment to #1126

[Ghislain Hachey]

Generated filename still not quite as convention above. More importantly, the school year is wrong as reported by Weison.

[Brian Lewis]

@ghachey ready for your (and Weison's) review I think

‌

[Ghislain Hachey]

@Brian Lewis At least one state reports that ECE students a missing in the next year’s rolled over workbook.

[Brian Lewis]

Missing ECE : what is happening in the rollover, is that if a student is enrolled in a Grade X, and that school does not offer Grade X+1 ( ie there are no current enrolments in Grade X+1), then the rollover assumes that the student cannot be at that school in the following year, so it leaves them out.

‌

This will particularly affect ECEs: So an ECE pupil in a kindergarten ( that only teaches ECE) cannot be in the same school the following year when they pass to Grade 1. So as per the above logic they do not appear in the rollover.

‌

But on reflection I think a better approach in this situation would be to:

a) write the rollover record for the pupil in Grade 1 (more generally Grade X+1)

b) leave the school blank

c) put the original school in the ‘transfer from school’

@Ghislain Hachey your thoughts?

‌

‌

[Ghislain Hachey]

Yeah, I think this is better definitely. This way they only need a bit of manual intervention when groups of students promote to a new school.

[Brian Lewis]

Implementing a better idea on this now - with a promotion form ECE, look at previous promotions from that school and class level - ie two StudentEnrolment records in consecutive years for the same student goining from ECE to Grade 1 - and find the most common target school.

This gives very good results - e.g. on YAP we see very plausible matches between the source ECE name and the Elementary school selected as the likely target

[Brian Lewis]

feat(census rollover): improved handling of cross-school promotions; fix missing student language, fix missng 'ECE' as 'From'

Resolves #1088

→ <<cset fa98c3b614c1>>