School Accreditation Form B Report RMI JasperReport

[Ghislain Hachey]

Two new reports to go in folder “School” called:

• School Form B Latest Report
• School Form B All Reports

They are very similar. One contains only the latest (best hopefully) Form B report. The other one contains all the Form B reports available in the system.

The specifications for Form B Report for RMI

https://drive.google.com/open?id=1M6CGpJPVB1Rho7A6wOsh3bWavbNxOAaC (details about the report)

https://drive.google.com/open?id=18zBeuBRTxahxgfO_pv68M2CxmLtwpo0l (formulas, etc. shown in an excel spreadsheet)

Please carefully review all this and let me know how you do.

Here are the key warehouse tables/view

• warehouse.accreditations
• warehouse.BestInspection

You may also find the following views useful.

• pInspectionRead.SchoolStandardAssessment
• pInspectionRead.SSACriteria
• pInspectionRead.SSAStandard
• pInspectionREad.SSACategoryType
• pInspectionRead.SSATally
• pInspectionRead.SSATallyByStandard
• pInspectionRead.SSATallyByCategoryType

[Shraddha Tambe]

@Ghislain Hachey Two sections of the reports are completed as shared in PDF, but there are few thing we need to know.

1. The first section of the report “Summary of School Evaluation/Classroom Observation Scores“ was done with the available table from the database.
2. And the second section of the report is also done, but to test it dynamically we need data related to the “School survey tool“ to check further reports sections dynamically. Please share updated database.
3. And give little brief about the Standard level’s logic for all sections.

[Shraddha Tambe]

@Ghislain Hachey Survey done on Android app (Education Survey Tool) on following context.

• FedEMIS 2 Schools (School No :CHK001,CHK002)
• MIEMIS 2 Schools (School No :AIL100,MAJ102)

Please share updated database.

[Shraddha Tambe]

Hi @Ghislain Hachey .

Please provide us with the latest RMI database dump to test FORM-B Report for RMI, we have currently tested with the FEDEMIS data dump that you shared.

All sections of the FORM-B report are completed. Please review and let us know your comments.

Attached the updated pdf file of the report.

[Ghislain Hachey]

@Shraddha Tambe in addition to the comments discussed over skype please edit the below title and description in the school’s reports.

Report Title: School Accreditation Form B Report

Report Description: A hard copy printable report of the School Accreditation Form B for any given year there is one available

[Shraddha Tambe]

Report has been deployed on the production server (named as per the above comments) and it is working with correct parameters from the MIEMIS Application UI too.

Tested with various older surveys. Please check.

Attached a document showing the results for some use-cases for old survey data .

Please check the same. We have some concerns, especially about the “Classroom Observation” data.

[Shraddha Tambe]

for School No : MAJ121 and Year 2019, the detailed score might have duplicate values. The report is not as expected please review

[Shraddha Tambe]

Report generated with Latest Survey submitted by us through the app. Please review all calculation changes as discussed on the call

[Shraddha Tambe]

Hi @Ghislain Hachey ,

Did you get a time to review the reports which we have shared. Please let us know your feedback so that we can start working on the reports.

[Shraddha Tambe]

Hi @Ghislain Hachey ,

In the latest android survey's data they are capturing school accreditation details at sub criteria level so we have yes/no option for each question but in older survey's data they have captured data at criteria level so it means that each criteria had aggregated value of sub criteria. In such case how can we get sub criteria details which are entered as no

Please see the screenshots attached for new survey and old survey

‌

[Shraddha Tambe]

Hi @Ghislain Hachey ,

As we do not have data for recommendations section in database we have created temp views to get standard, criteria and sub criteria details. We have shared those views through an email. Please create the same views in your database so the recommendation section will work

We have committed the jasper reports jrxml files and sql view scripts in Bitbucket in below branch

https://bitbucket.org/shraddha_tambe/helical_rmi/branch/Issue685

Please let us know once you have created the views so we can publish the report

[Ghislain Hachey]

@Shraddha Tambe The old survey do not have sub-criteria so you can’t use that as previously explained. The Form B report for thos old ones will be much smaller and simpler with no recommendations, etc.

I will look at the temp views and put into production for report to work until Brian has time to look at it and we decide the best way to go forward with this.

[Shraddha Tambe]

Hi ,

For one of the school(eg. MAJ121) we have multiple survey’s in year 2019 so as you said we have considered the latest survey for that school and updated the report. we have tested the data comparing with MIEMIS application also.

Yesterday we have shared the temp views for recommendations section. Once those are created in MIEMIS database then we can publish the report.

Please confirm once the views are created.

Please find the attached report pdf file

Note: In the attached pdf there is no recommendations section part in the end as we do not have temp views created in MIEMIS database

[Brian Lewis]

@Shraddha Tambe @Ghislain Hachey IShraddha the view is fine as it stands - but, I have just changed the name because it goes back to the operational data rather using using cached warehouse data. It is pInspectionRead.SchoolStandardAssessmentEx. Hopefully this won;t give you a permissions problem.

‌

/*    ==Scripting Parameters==

    Source Server Version : SQL Server 2012 (11.0.6251)
    Source Database Engine Edition : Microsoft SQL Server Enterprise Edition
    Source Database Engine Type : Standalone SQL Server

    Target Server Version : SQL Server 2012
    Target Database Engine Edition : Microsoft SQL Server Enterprise Edition
    Target Database Engine Type : Standalone SQL Server
*/

USE [FEDEMIS]
GO

/****** Object:  View [pInspectionRead].[SchoolStandardAssessment]    Script Date: 2/12/2019 3:20:21 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- ==================================================
-- Author:      Shraddha Tambe
-- Create date: Dec 2019
-- Description: Normalised view of SchoolStandardAssessment, with subcriteria/criteria descriptions
-- ==================================================
-- -- 3 views are available of 'Accreditation' objects:
-- 1) the XMl representation of the object - contans the full text of each criteria description etc
--    this may be loaded from the survey tool, or else fabricated from earlier SchoolAccreditation objects
--
-- 2) A normalised view showing 1 subcriteria per row, or, if subcriteria are not avilable
--      1 criteria per row. This forms the basis of aggregations going to the warehouse.
--      This view provides the underlying parse of the Xml object.
--      It is not dependent on any particular values for criteria or subcriteria numbers.
--      It requires only that the Xml is structured in the category/standard/criteria/subcriteria hierarchy.
--      We name assessment instruments in this hierarchy 'SchoolStandardAssessment' or SSA for short
--      pInspectionRead.SchoolStandardAssessment
--      also pInspectionRead.SchoolStandardAssessmentEx - which includes descriptions at each level as well
--
-- 3) a denormalised view, with 1 column per criteria. this is equivalent to the original School Accredtion object that was stored in
--      its own table. This makes specific assumptions about the naming of criteria in the Xml file
--      e.g. SE.1.2 School Evaluation Standard `1 Criteria 2
--
--  The Stored Procedure pInspectionREad.ReadInspection returns a record whose structure is dependent
--  on the inspection type of the inspection. It does this by executing a query
--  pInspectionRead.<inspectiontype> if such a query exists (Otherwise it executes pInspectionRead.SchoolInspections)
--  for example, to read a SCHOOL_ACCREDITATION the procedure uses pInspectionRead.SCHOOL_ACCREDITATION
--  Such procedures are intended to shred the Xml stored in inspXml on SchoolInpection to generate the returned record.
--
-- =============================================
ALTER VIEW [pInspectionRead].[SchoolStandardAssessmentEx]
AS
-- totals by subcriteria
Select inspID
, v.value('(../../../../version)[1]', 'int') Version
, v.value('(../../../name)[1]', 'nvarchar(50)') Category
, v.value('(../../../type)[1]', 'nvarchar(50)') CategoryType
, v.value('(../../id)[1]', 'nvarchar(50)') Standard
, v.value('(../../name)[1]', 'nvarchar(max)') StandardName
, v.value('(../id)[1]', 'nvarchar(50)') Criteria
, v.value('(../name)[1]', 'nvarchar(max)') CriteriaName
, v.value('id[1]', 'nvarchar(50)') SubCriteria
, v.value('name[1]', 'nvarchar(max)') SubCriteriaName
, v.value('interviewQuestions[1]', 'nvarchar(max)') SubCriteriaQuestion
, v.value('(answer/state)[1]', 'nvarchar(50)') Answer
, convert(decimal(5,1)
    , case v.value('(answer/state)[1]', 'nvarchar(50)')
        when 'POSITIVE' then 1
        when 'NEGATIVE' then 0
        else null
        end
    ) Score
, 1 MaxScore
from dbo.SchoolInspection SI
CROSS APPLY SI.inspXml.nodes('/survey/category/standard/criteria/subcriteria') as V(v)

UNION ALL
-- totals by criteria
Select inspID
, v.value('(../../../../version)[1]', 'int') Version
, v.value('(../../../name)[1]', 'nvarchar(50)') Category
, v.value('(../../../type)[1]', 'nvarchar(50)') CategoryType
, v.value('(../../id)[1]', 'nvarchar(50)') Standard
, v.value('(../../name)[1]', 'nvarchar(max)') StandardName
, v.value('(../id)[1]', 'nvarchar(50)') Criteria
, v.value('(../name)[1]', 'nvarchar(max)') CriteriaName
, null SubCriteria
, null SubCriteriaName
, null SubCriteriaQuestion
, null Answer
, v.value('.', 'decimal(4,1)') Score
, v.value('(../maxscore)[1]', 'int') MaxScore
from dbo.SchoolInspection SI
CROSS APPLY SI.inspXml.nodes('/survey/category/standard/criteria/score') as V(v)

UNION ALL
-- totals by standard
Select inspID
, v.value('(../../../version)[1]', 'int') Version
, v.value('(../../name)[1]', 'nvarchar(50)') Category
, v.value('(../../type)[1]', 'nvarchar(50)') CategoryType
, v.value('(../id)[1]', 'nvarchar(50)') Standard
, v.value('(../name)[1]', 'nvarchar(max)') StandardName
, null Criteria
, null CriteriaName
, null SubCriteria
, null SubCriteriaName
, null SubCriteriaQuestion
, null Answer
, v.value('(.)', 'decimal(5,1)') Score
, v.value('(../maxscore)[1]', 'int') MaxScore
from dbo.SchoolInspection SI
CROSS APPLY SI.inspXml.nodes('/survey/category/standard/score') as V(v)

UNION ALL
-- totals by category
Select inspID
, v.value('(../../version)[1]', 'int') Version
, v.value('(../name)[1]', 'nvarchar(50)') Category
, v.value('(../type)[1]', 'nvarchar(50)') CategoryType
, null Standard
, null StandardName
, null Criteria
, null CriteriaName
, null SubCriteria
, null SubCriteriaName
, null SubCriteriaQuestion
, null Answer
, v.value('(.)[1]', 'decimal(4,1)') Score
, v.value('(../maxscore)[1]', 'int') MaxScore
from dbo.SchoolInspection SI
CROSS APPLY SI.inspXml.nodes('/survey/category/score') as V(v)



GO
Select * from  [pInspectionRead].[SchoolStandardAssessmentEx]
Select * from  [pInspectionRead].[SchoolStandardAssessment]

‌

[Shraddha Tambe]

@Brian Lewis @Ghislain Hachey Thanks Brain. In addition to this we have created another view “[warehouse].[TempSchoolAccreditationDetails]“ which is similar to “[warehouse].[AccreditationDetails]“. And this view was pulling data from warehouse.TempSchoolAccreditationAssessment view.

Please review the TempSchoolAccreditationDetails view also.

[Shraddha Tambe]

@Ghislain Hachey @Brian Lewis

Hi Ghislain, we are trying to access MIEMIS web application and jasper server but both applications are not working

Please check

[Ghislain Hachey]

@Shraddha Tambe I have notified Newton, I am currently in FSM. It should be back on soon.

[Ghislain Hachey]

@Shraddha Tambe the power has been off at their office since Sunday. I assume they are working on that problem.

[Ghislain Hachey]

@Shraddha Tambe the two views you need are the following:

• pInspectionRead.SchoolStandardAssessment
• pInspectionRead.SchoolStandardAssessmentEx

‌

[Shraddha Tambe]

@Ghislain Hachey We have extracted the xml data question and its responses into “[warehouse].[TempSchoolAccreditationDetails]“ which is similar to “[warehouse].[AccreditationDetails]“. And this view was pulling data from warehouse.TempSchoolAccreditationAssessment view.

Please share the modified view for TempSchoolAccreditationDetails also.

[Ghislain Hachey]

@Shraddha Tambe The view pInspectionRead.SchoolStandardAssessment is the one that replaces both you temp views.

[Shraddha Tambe]

Hi @Ghislain Hachey ,

We have imported the latest database dump and we have seen additional columns in the view 'pInspectionRead.SchoolStandardAssessment'.

And we have observed that the same columns were added in warehouse.AccreditationDetails table also but all values are coming Null. so I have checked the warehouse procedure to load data into warehouse.AccreditationDetails table and in that these new columns were missing in the SQL script. I have added those columns, executed the procedure and we have got data in warehouse.AccreditationDetails table.

Now we can use warehouse.AccreditationDetails table for recommendations section in the report.

Please update the SQL procedure in production database also so we can see data in the report after publishing report in prod server.

‌

[Ghislain Hachey]

Well not exactluy no. You must used the pInspectionRead.SchoolStandardAssessment version. Refer to Brian’s comment in issue #766 that explains why.

[Shraddha Tambe]

Hi @Ghislain Hachey , we have modified Form-B RMI report with the pInspectionRead.SchoolStandardAssessment and published in JasperServer and even tested in MIEMIS application. Working good.

Please review.

[Ghislain Hachey]

@Shraddha Tambe make sure any adjustment like this is also corrected in FSM

[Ghislain Hachey]

@Shraddha Tambe what is all these different Form B reports? Which one is good?

‌

[Shraddha Tambe]

Hi @Ghislain Hachey , report with the name “School Accreditation Form B Report.jrxml“ is the actual report. Please do not consider other reports

[Ghislain Hachey]

@Shraddha Tambe please cleanup reports not suppose to be there

[Ghislain Hachey]

feat(JasperReports): works from Helical as of 25 March 2020

Resolves #736, #685, #686, #341, #678, #687, #765, #617, #656, #662, #777, #616

→ <<cset 8805ba9df3c5>>

[Ghislain Hachey]

feat(JasperReports): works from Helical as of 25 March 2020

Resolves #736, #685, #686, #341, #678, #687, #765, #617, #656, #662, #777, #616

→ <<cset b8641eaa78eb>>