Person Aggregation Query Builder

-Person Aggregation Query Builder -This is the core functionality for linking data between tracker module and aggregate management module by defining the linking/aggregating rules. -This provides two functions - - - - Person aggregation query builder management is a tool for defining formulas, expressions, rules for aggregation data from tracker to aggregation module. - - - Person aggregation is used for generating routine data values from individual data by period and organization unit. - - -

- Person aggregation query builder management - To access Aggregation Query Builder module, from the Maintenance menu, go to the Persons and Programs option. Select Aggregation Query Builder option on left menu to proceed. - - Figure 2.5.4.2 - - - - - - - After click the Add new button, the following will appear: - - Figure 2.5.4.3 - - - - - - - - Name: - Provide name of query, this name is usually same with the name of aggregate data element. For example Number of mothers got VAT, Number of children got BCG vaccine,... - - - Dataset: - - Select dataset contains data elements which will be used to link with individual data. - - Data element: - Select aggregate data element which will be used to link with individual data. - Operator: Select Number of persons if aggregate based on persons, or Number of visits if aggregate data based on times that persons received services. - Program: Select program needed for aggregation. - Program Stage: Select program stage from the selected program above. - Next section included 3 tabs: Data elements, Person attributes and Program. Depend on the requirement of data aggregation, data element, person attributes, and number of programs are selected. - - Data elements tab: - List all available data elements from selected program stage. To create a formula related to each data element, double click on the data element. The formula of the data element is filled on the condition field, like [DE:1.2.3], in with: - - - 1 - id of the selected program. - - - 2 - id of the selected program stage. - - - 3 - id of the selected data element. - - - - The formula can be create without any program stage, like [DE:1.*.3] - - - Person attribute tab: - List all available fixed attributes, such as gender, DOB type, Age(days), .. and user-defined attributes. Besides, provide a formula for calculating number of persons who registered in system. However, if a program without registration is selected, person attributes aren't shown. - - Program tab: - Provide formulas for calculating data value related to the selected program and program stage - - - - The number of persons who enrolled the selected program. - - - The number of persons / times that ( Date of enrollment - Date of incident ) is greater then a certain number. - - - The number of persons who visited the selected program stage OR the number of visits what are registered in the system. - - - The number of persons by age at the report-time. - - - - Condition: - This section contains comparing operations (<, <=, >, >=, =, != ), calculation operation (+), priority operation ( ( ) ), boolean values (Yes, No), status operations (=NULL, != NULL), relation operations (AND, OR). These operations are used for supporting in setting condition for query. - - Description : - Show description for the condition above. - E.g To get number of girls who got Vitamin A dose 6 enrolled Child Health program on January 2012. Then query is created as in the figure below. Note that January 2012 is period parameter which user will select when generate report. It is not set in the query. - - Fig. example_query - - - - - - -

-Sample of person aggregation -E.g. Find the number of the children vaccinated BCG by defining a linking rule between the data elements of aggregate dataset named Number of children - BCG and the data element of a program stage named BCG dose given. - - -From Maintenance menu, go to Data Element and Indicators option and click on it. Select Data Element option to define two new data elements - - - -Define a new data element named Number of children - BCG with Aggregate option for Domain Type. - - -Define a new data element named BCG dose give date with Patient option for Domain Type. - - - - -From Maintenance menu, go to Datasets option and click on it. Select Dataset option to create a new dataset named BCG Report and add Number of children - BCG data element into the dataset. - - -From Maintenance menu, go to Persons and Programs option and click on it. Select Program option to create a program named Child Health Program with a stage named Birth Details and add the BCG dose given data element into this stage. - - -Create a new person aggregation query builder named Number of children - BCG Builder to linking between the data elements. - - - - - - - - - -From Services menu, go to Individual Records option and click on it. Select Person option below Registration header in the left menu to register three persons named Micheal, Alice and Tom in Ngelehun CHC and enroll them into Child Health Program. - - -For Micheal, open the Birth Details stage into Child Health Program. Enter value for BCG dose give date data element as "2012-01-18" - - -For Alice, open the Birth Details stage into Child Health Program. Enter Enter value for BCG dose give date data element as "2012-01-18" - - -For Tom, no enter any data value for Birth Details stage. - - Before entering data value for the stage, choose Report Date as "2012-01-18" - - - - - -From Services menu, go to Individual Records option and click on it. Select Person aggregation option to run Number of children - BCG Builder to get data value and save it into database. - -Figure 2.5.3.5 - - - - - - - -Figure 2.5.4.6 - Result after running the query builder - - - - - - - - -

+ Person Aggregation Query Builder + To access Aggregation Query Builder module, from the Maintenance menu, go to the Persons and Programs option. Select Aggregation Query Builder option on left menu to proceed. + After click the Add new button, the following will appear: + + Figure 2.5.4.3 + + + + + + + Basic information - + + + Name: Provide name of query, this name is usually same with the name of aggregate data element. For example Number of mothers got VAT, Number of children got BCG vaccine,... + + + Dataset: Select dataset contains data elements which will be used to link with individual data. + + + Data element: Select aggregate data element which will be used to link with individual data. + + + Operator: Select Number of persons if aggregate based on persons, or Number of visits if aggregate data based on times that persons received services. + + + Program: Select program needed for aggregation. + + + Program Stage: Select program stage from the selected program above. + + + Next section included 3 tabs: Data elements, Person attributes and Program. Depend on the requirement of data aggregation, data element, person attributes, and number of programs are selected. + + Data elements tab: + List all available data elements from selected program stage. To create a formula related to each data element, double click on the data element. The formula of the data element is filled on the condition field, like [DE:1.2.3], in with: + + + 1 - id of the selected program. + + + 2 - id of the selected program stage. + + + 3 - id of the selected data element. + + + + The formula can be create without any program stage, like [DE:1.*.3] + + + Person attribute tab: + List all available fixed attributes, such as gender, DOB type, Age(days), .. and user-defined attributes. Besides, provide a formula for calculating number of persons who registered in system. However, if a program without registration is selected, person attributes aren't shown. + + Program tab: + Provide formulas for calculating data value related to the selected program and program stage - + + + The number of persons who enrolled the selected program. + + + The number of persons / times that ( Date of enrollment - Date of incident ) is greater then a certain number. + + + The number of persons who visited the selected program stage OR the number of visits what are registered in the system. + + + The number of persons by age at the report-time. + + + + Condition: + This section contains comparing operations (<, <=, >, >=, =, != ), calculation operation (+), priority operation ( ( ) ), boolean values (Yes, No), status operations (=NULL, != NULL), relation operations (AND, OR). These operations are used for supporting in setting condition for query. + + Description : + Show description for the condition above. + E.g To get number of girls who got Vitamin A dose 6 enrolled Child Health program on January 2012. Then query is created as in the figure below. Note that January 2012 is period parameter which user will select when generate report. It is not set in the query. + + Fig. example_query + + + + + + +

+ Sample of person aggregation + E.g. Find the number of the children vaccinated BCG by defining a linking rule between the data elements of aggregate dataset named Number of children - BCG and the data element of a program stage named BCG dose given. + + From Maintenance menu, go to Data Element and Indicators option and click on it. Select Data Element option to define two new data elements - + + + Define a new data element named Number of children - BCG with Aggregate option for Domain Type. + + + Define a new data element named BCG dose give date with Patient option for Domain Type. + + + + + From Maintenance menu, go to Datasets option and click on it. Select Dataset option to create a new dataset named BCG Report and add Number of children - BCG data element into the dataset. + + + From Maintenance menu, go to Persons and Programs option and click on it. Select Program option to create a program named Child Health Program with a stage named Birth Details and add the BCG dose given data element into this stage. + + + Create a new person aggregation query builder named Number of children - BCG Builder to linking between the data elements. + + + + + + + + + + From Services menu, go to Individual Records option and click on it. Select Person option below Registration header in the left menu to register three persons named Micheal, Alice and Tom in Ngelehun CHC and enroll them into Child Health Program. + + + For Micheal, open the Birth Details stage into Child Health Program. Enter value for BCG dose give date data element as "2012-01-18" + + + For Alice, open the Birth Details stage into Child Health Program. Enter Enter value for BCG dose give date data element as "2012-01-18" + + + For Tom, no enter any data value for Birth Details stage. + + Before entering data value for the stage, choose Report Date as "2012-01-18" + + + + + + From Services menu, go to Individual Records option and click on it. Select Person aggregation option to run Number of children - BCG Builder to get data value and save it into database. + + Figure 2.5.3.5 + + + + + + + + Figure 2.5.4.6 - Result after running the query builder + + + + + + + + +

Program and Program stage -Program section provides a mechanism for defining a health program. All name based case entry is organised through the use of programs. -To access the program maintenance module, from Maintenance menu, go to Persons and Programs option and click on it. Select Program option to create a new program. -To create a new program, click the Add new button. Fill all fields with read asterisk, and then click the "Add" button. +

+Program +Program section provides a mechanism for defining a health programme. +To access the program maintenance module, from Maintenance menu, go to Persons and Programs option and click on it. Select Program option. +To define a new program, click the Add new button on the main page. Fill all fields with read asterisk, and then click the "Add" button. Basic information - Name: The name of the program -Description: Descriptions, meanings, explanation, etc. of/about the program +Description: Descriptions, meanings, explanation, etc. of/about the program. -Type: There are three type of program: Multi events with registration, single event with registration and single event without registration. +Type: There are three type of program: multi events with registration, single event with registration and single event without registration. Show provided elsewhere: Set show/hide status of provided elsewhere column in data entry form. Description of enrollment date: The date in which a person is registered to the system. -For example: the date in which a person enrols to the system. +For example: the date in which a person enrolls to the system. Description of incident date: The date when the person got the disease or he/she has just been committed to an equivalent program for being followed-up. E.g. In the Immunization program for child under 1yr old, the incident date is the birthday; in the maternal program, the incident date is the date of last menstrual period (LMP). -Duration in days. +Duration in days: System gets the days to calculate the activity plan of each person in mobile project ( refer mobile chapter ).

Program stage -To define a program stage, click the Program Stage Management icon (Fig. 2.5.1.2a) and then Add new button (Fig. 2.5.1.2b). Fill in the Name and Description fields and then select the data elements that should belong to the group from the left panel. Click the Move selected button to add the selected data elements to the right panel. Click the Remove selected button to remove data elements that have been selected in the right panel. Finally, click the Add button to save changes, or the Cancel button to discard any changes. +To define a program stage, click the Program Stage Management icon and then Add new button. Fill in the Name and Description fields and then select the data elements that should belong to the group from the left panel. Click the Move selected button to add the selected data elements to the right panel. Click the Remove selected button to remove data elements that have been selected in the right panel. Finally, click the Add button to save changes, or the Cancel button to discard any changes. Basic information - @@ -6327,7 +6326,7 @@ Description: The description of the program stage -Scheduled days from start: The minimum number of days to wait for starting the program. +Scheduled days from start: The minimum number of days to wait for starting the program stage. Repeatable: specify the program stage as repeatable or not. @@ -6337,14 +6336,6 @@

-Other functionalities -The function Define Program Validation is designed for defining validations of a program in which you would like to modify after entering patient data values for program stages. - -For instance, Parity, Previous live birth male, Previous live birth female, Previous still birth are patient data elements. They belong to many stages. And if data value of Parity is 0, then ( Previous live birth female + Previous still birth ) = 0. Besides, if a patient data element exists into many program stages, the system will check whether the values of the patient data element are in all program stages of the program or not. - -For instance, TT1 is a patient data element. It exists into program stages as follows Checkup 1, Checkup 2, Checkup 3. If the data value of this data element into Checkup 1 is null, the data values into Checkup 2, Checkup 3 should be null. -

Validation criteria