Enrollment by Age and Grades Jasper Report

[Ghislain Hachey]

National, by District, by national electorate, by local electorate and by schools. (with national/local electorate as lower priority)

This should be done in three separate reports:

• One with national, district (grouping), and electorates aggregations (grouping). This should be in the jasper folder "Students" (and in the Students -> Dashboard for the web app)
• One for specific districts (district parameter). This should be in the jasper folder "Students" (and in the Students -> Dashboard for the web app)
• One for individual schools located in the folder "Student" (and in the Students -> School -> Dashboard for the web app)

Crosstab with age on the vertical axis and grades on the horizontal axis. Includes totals.

Used as a data quality tool to compare with workbook pivot table.

Attached below some example.

[Ghislain Hachey]

See warehouse.tableEnrol table

[Ghislain Hachey]

This is an example showing Chuuk enrolments. In the three reports needed that grid will show different version depending on the report.

One with national, district (grouping), and electorates aggregations (grouping). This should be in the jasper folder "Students". This one will show multiple grids for each states in a single national report, each local electorate and national electorate.

One for specific districts (district parameter). This should be in the jasper folder "Schools". This one would show a single grid for the actual state. And all a section with grouping by schools for each school in that state.

One for individual schools located in the folder "School". This one would the single grid for that school. Also include that same grid in the School Report Card somewhere in Basic Statistics.

[Ghislain Hachey]

Data for this report can be found in warehouse.TableEnrol

[Rupam Bhardwaj]

Hi @ghislain ,

I have gone through the table provided [warehouse].[TableEnrol] but did not get Electorates information in the table. Please let me know how can we get the Electorate information.

[Brian Lewis]

Hello @rupam_bhardwaj

perhaps I can jump in here, @ghachey is in workshops this week. (PS I don't think user 'ghislain' is our @ghachey)

warehouse.tableEnrol provides enrolment information aggregated up on the most commonly used attributes of schools -

• district ( also known as 'province' or 'state')

• authority (the organisation that runs the school)

• school type ( the levels/ curriculum taught in that school)

If you need to aggregate on some other characteristic of the school, such as Electorate you need to go back to warehouse.enrol to get the data by school, then aggregate the school data yourself.

So you have the school on warehouse.enrol and I would recommend that the best practice for getting all the attributes of that school in the survey year is to use the table warehouse.dimensionSchoolSurvey.

It's done this way for thoroughness in historical reporting - while many of these attributes simply live on the table Schools, many attributes can and do change over time - a government department may take over a church school ( change of authority), a school may change type by adding new class levels, electorate boundaries may even change.

So to get the record in warehouse.dimensionSchoolSurvey that defines a given school in a given survey year, use the field surveyDimensionID to join:

Select surveyYear
, L.codeDescription ClassLevel
, GenderCode
, DSS.[Local Electorate]
, sum(Enrol) Enrol
 from warehouse.Enrol E
    LEFT JOIN warehouse.dimensionSchoolSurvey DSS
        on E.SurveyDimensionID = DSs.[Survey ID]
    LEFT JOIN lkpLevels L
        ON E.classLevel = L.codecode
 GROUP BY 
 surveyYear
, L.codeDescription
, GenderCode
, DSS.[Local Electorate]

Note that Local Electorate is the electorate for state/district/provincial government, while National Electorate is the electorate for federal/national government.

As another observation, in the design of the warehouse we make decisions aboout how to present data, and there are really three choices of strategy:

1. the warehouse holds the data as a table

2. you can get the data you need by running a query on warehouse tables (as in this case)

3. you get the data from a warehouse view, that is built on warehouse tables.

The decision is made on -

• how expensive ( ie time-consuming) is it to calculate this data?

• how complicated is the query needed to get it?

• how big is the resulting dataset?

• how frequently used will this data be?

So at this stage we monitor how the warehouse is being used and what type of data is needed; and some changes between these three strategies may follow. For example, we could consider in this case introducing a view equivalent to the query above .... but for now, to get moving, please just use that sql as your data source.

[Rupam Bhardwaj]

We have small concern on the below requirement : "Also include that same grid in the School Report Card somewhere in Basic Statistics."

This grid is in A4 Landscape page whereas school report card is in A4 Portrait page.

Please advise

[Ghislain Hachey]

This just means this report should be available as a standalone report in School with SchoolNo parameter. And also as part of the big School Report Card.

Play around to see it possible to make into portrait.

[Brian Lewis]

@rupam_bhardwaj - in Jasper how difficult is it to take the row-based data (e.g. from warehouse.Enrol) and turn it into a cross-tabulation?

Can you do this in the report - or would you set up the Sql so that 1 record in the output corresponds to one row of the grid - ie a single year of age?

If the answer is that it needs to be done in the Sql, then @ghachey I'm thinking perhaps this logic should be part of the warehouse - probably a stored procedure, making it reusable.

This is not an issue in angularjs pages as you know because we can nest ng-repeat horizontally and vertically and in fact we have this for a single school already.

For higher level aggregations in online dashboards, we'll use crossfilter to do the cross-tabulation - so a potential cross-tabulation in Sql would be targetted for use in reports only. Still, may be better to have it as a part of the warehouse than locked inside one report?

[Shraddha Tambe]

Brian - Cross-tabulation of the data can be done in the report, we don't have to write SQL to write out each row that we want to see as in the cross-tab visual. In a Jasper crosstab, we can define multiple row / column groupings and the cross-tab will generated using row-based data as per those groupings.

[Ghislain Hachey]

Can you do this in the report - or would you set up the Sql so that 1 record in the output corresponds to one row of the grid - ie a single year of age? If the answer is that it needs to be done in the Sql, then @Ghislain Hachey I'm thinking perhaps this logic should be part of the warehouse - probably a stored procedure, making it reusable.

@softwords no stored proc for this necessary. This is easily done in Jasper.

[Ghislain Hachey]

feat(JasperReports): Enrolment by Age and Grade

Closes #333

→ <<cset 5947ce52951a>>

[Ghislain Hachey]

feat(JasperReports): various new reports from Helical

Closes #333, #332, #354, #245, #346, #344, #360

→ <<cset f1bfb047f41a>>