Use dropdown for district parameter in reports parameter dialog [2020:H]

[Ghislain Hachey]

Instead of having the user type the district which is unclear whether we need to type the code or full district name, Make this and maybe other similar Jasper parameter a dropdown

[Ghislain Hachey]

fix(JasperReports): updated all reports to support dropdown of Districts in EMIS

Resolves #652, #653

→ <<cset f597c5410d4d>>

[Brian Lewis]

Just reviewing this to remember how it works , so in summary:

Each Jasper repository contains an InputControls folder to store reusable input controls. Within this there is a Lookups folder, to store InputControls that provide dropdown lists based on EMIS lookup tables.

In the Lookups folder there is a folder Queries, to store the particular SQL relating to that lookup.

Suggestion: the id of the InputControl and the id of the query match, and are the same as the name assigned to the lookup in EMIS.

These lookup names are seen here (reading api/lookups/collection/core):

and are defined in DSLookups.cs in Pineapples.Data

‌

       public DSLookups(DB.PineapplesEfContext cxt) : base(cxt)
        {
            lkpCommands = new Dictionary<string, string>();
            lkpCommands.Add("schoolTypes", "SELECT stCode C, stDescription N from TRSchooltypes;");
            lkpCommands.Add("schoolCodes", "SELECT schNo C, schName N FROM Schools;");
            lkpCommands.Add("districts", "SELECT dID C, [dName] N from Districts ORDER BY dName;");
            lkpCommands.Add("authorities", "SELECT authCode C, authName N, authType T from TRAuthorities;");
            lkpCommands.Add("authorityTypes",
                            "SELECT [codeDescription] N, [codeCode] C, codeGroup G from TRAuthorityType;");
            lkpCommands.Add("authorityGovt",
                            "SELECT [codeDescription] N, [codeCode] C from TRAuthorityGovt;");
            lkpCommands.Add("islands", 
                            "SELECT [iName] N, [iCode] C, [iGroup] D, [iOuter] R FROM lkpIslands ORDER BY [iName];");
            lkpCommands.Add("regions", "SELECT [codeCode] C, [codedescription] N FROM lkpRegion ORDER BY [codedescription];");
            lkpCommands.Add("educationLevels", 
                            @"Select codeCode C, codeDescription N, edlMinYear MinY, edlMaxYear MaxY
                                from TREducationLevels ORDER By edlMinYear");
            lkpCommands.Add("educationLevelsAlt",
                            @"Select codeCode C, codeDescription N, edlMinYear MinY, edlMaxYear MaxY
                                from TREducationLevelsAlt ORDER By edlMinYear");
            lkpCommands.Add("educationLevelsAlt2",
                            @"Select codeCode C, codeDescription N, edlMinYear MinY, edlMaxYear MaxY
                                from TREducationLevelsAlt2 ORDER By edlMinYear");
            lkpCommands.Add("educationSectors",
                           @"Select secCode C, secDesc N 
                                from TREducationSectors ORDER By secSort");

Now these SQL statements that define the lookup can be the SQL set by the query in Inputcontrols/Lookups/Queries in the Jasper repository.

Example - dropdown to select Authority Govt ( this is in FEDEMIS).

First create the query, matching the name and SQL from DSLookups:

Now the input control linked to that query:

‌

[Brian Lewis]

feat(Reports): in Report Parameters dialog, translate each prompt label using vocab

Resolves #653

→ <<cset 650853a6811e>>

[Brian Lewis]

As of the above commit, the Prompt Text set up for the Input Control in the Jasper repository is run through the Vocab when displayed in the report Parameters dialog. Hopefully in the long term this will make it easier to reuse and maintain Input controls between sites.

[Ghislain Hachey]

@Brian Lewis This is fine. But I had resolved this issue before. So do I need to change anything jasperserver side with this new suggestion? It seems more like a small syntactic improvement which can hopefully wait…

[Brian Lewis]

@Ghislain Hachey No , you don't need to change anything : what you have done fits with this in the most important respect; ie you have used a global Inputcontrol to prompt for the ‘State’. This can stay where it is. Purpose of this is to document how this can work going forward, and the small improvement of using the vocab translate in the dialog box.

Just offers a bit more generalisation perhaps and opportunity to reuse stuff from site to site.

[Brian Lewis]

and ps I just did ‘Resolve’ again to get the link to the new commit - I understand it was already Resolved.

[Ghislain Hachey]

@Brian Lewis all good and I have included your useful summary above the in the Pacific EMIS developer guide for posterity.