Separate DHIS2 database to 2

Support - Assistance technique
1

Dear all,
Recently Vietnam team has been working on the reports of Vietnam and design them in DHIS2. And we see that there is a problem.
The levels in Vietnam are set as below:
Ward => District=> Province=>MOH

There are 3 levels of reports.
1st : Ward level reports, these reports are entered by ward level user. These reports display the data values of ward level.
2nd: District level reports, these reports display data values of list of wards level.

3rd: Province reports, these reports display data values from some of org units at district level and some org unit at ward level to submit to MOH.
So when the user from province level generate report, the report can’t reach the data values from ward but it’s calculated and display only in the district level.

But as I know DHIS only support for display data values of the next 1 lower level when we generate in any org unit level. So we are going to separate the data base to twice.
1st database is for national level which includes only reports from province level…

and the 2nd one is for province level which includes only reports of ward and district level.
In this way, the users from province level may generate report from the 2nd database, and then import to the 1st database for submit report to MOH.

Currently we only know this solution. And we don’t know if it is right and good solution. If you have any suggestion, or any experience about this problem, please share and consult to us. We really need your ideas.

Looking forward to your responses.
Thanks and Regards,

···

–

Thuy
HISP Vietnam
+84902079126

2

Hi Thuy,

When you say reports, which module are you referring to? There are many ways to set up reports in DHIS2, and my first thought is that it should be possible to generate all your reports from the same database, and that splitting up into 2 databases sounds like al lot of extra work for you guys (maintenance, import/export etc.).

Using report tables as your data source for your reports you should be able to generate more or less anything you want, you can e.g combine orgunits from any level. You should then be able to use report tables (with report parameters) in any of the report modules whether it is Jasper, BIRT, Excel or some other local variant you are using.

You say, some orguits from district level and some orhgunits from ward level, does that mean not all districts in one province and not all wards, or all districts and some wards of a particular type maybe?

If you create one report table per province you can select exactly the orgunits you want from district and ward level, and then not select orgunit as report parameter (all orgunits in the report are fixed). This will work, but since you have 84 (?) provinces this easily becomes very messy.

So the problem relates to making these reports generic and reusable for all your provinces, for any orgunit at level 2. I assume that the orgunits you want to display at ward level are of a special type, like a health center more than just the normal ward (phoung/xa). Is that right? If you have assigned a common orgunit group for all these ward level units that you want to see in the report then it should be possible to modify the report tables to generically list all these units even for a province report.

My point is that if the report logic is something like this:

For any selected orgunit at level 2 (this is your report parameter) populate a report table with ALL children (districts) and the grandchildren (ward level) of orgunit type X.

That should be doable to add to report tables I think.

I would rather try to improve the report table functionality than splitting up into two databases just to solve a report problem.

Does this make sense?

Ola

···

2009/12/17 Thuy Nguyen thuy.hispvietnam@gmail.com

Dear all,
Recently Vietnam team has been working on the reports of Vietnam and design them in DHIS2. And we see that there is a problem.
The levels in Vietnam are set as below:
Ward => District=> Province=>MOH

There are 3 levels of reports.
1st : Ward level reports, these reports are entered by ward level user. These reports display the data values of ward level.
2nd: District level reports, these reports display data values of list of wards level.

3rd: Province reports, these reports display data values from some of org units at district level and some org unit at ward level to submit to MOH.
So when the user from province level generate report, the report can’t reach the data values from ward but it’s calculated and display only in the district level.

But as I know DHIS only support for display data values of the next 1 lower level when we generate in any org unit level. So we are going to separate the data base to twice.
1st database is for national level which includes only reports from province level…

and the 2nd one is for province level which includes only reports of ward and district level.
In this way, the users from province level may generate report from the 2nd database, and then import to the 1st database for submit report to MOH.

Currently we only know this solution. And we don’t know if it is right and good solution. If you have any suggestion, or any experience about this problem, please share and consult to us. We really need your ideas.

Looking forward to your responses.
Thanks and Regards,

–
Thuy
HISP Vietnam
+84902079126

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

3

Hi Thuy,

When you say reports, which module are you referring to? There are many ways to set up reports in DHIS2, and my first thought is that it should be possible to generate all your reports from the same database, and that splitting up into 2 databases sounds like al lot of extra work for you guys (maintenance, import/export etc.).
**Yes, I really don’t like this seperating. **

Using report tables as your data source for your reports you should be able to generate more or less anything you want, you can e.g combine orgunits from any level. You should then be able to use report tables (with report parameters) in any of the report modules whether it is Jasper, BIRT, Excel or some other local variant you are using.

You say, some orguits from district level and some orhgunits from ward level, does that mean not all districts in one province and not all wards, or all districts and some wards of a particular type maybe?

**Yes, I mean that. Only some Org unit belong to district level, but it is created in the ward level in DHIS. And another some org units in upper level. **

If you create one report table per province you can select exactly the orgunits you want from district and ward level, and then not select orgunit as report parameter (all orgunits in the report are fixed). This will work, but since you have 84 (?) provinces this easily becomes very messy.
**Actually I created org unit groups which included org units which I want to display in the report. And in each report has more than 1 org unit group. Such as District org unit(some org unit in the level 4), ward org units (some org unit in the level 4), and province org units(some org unit in level 3). **

**and parameter is selected when generate report. **

in the report at province level (level 2) then parameter is org unit in level 2.

and if the report at district level (level 3) then parameter is org unit in level 3.

So the problem relates to making these reports generic and reusable for all your provinces, for any orgunit at level 2. I assume that the orgunits you want to display at ward level are of a special type, like a health center more than just the normal ward (phoung/xa). Is that right?
Yes that is right.

If you have assigned a common orgunit group for all these ward level units that you want to see in the report then it should be possible to modify the report tables to generically list all these units even for a province report.

**Yes I already assigned the common org units to org unit groups. **

My point is that if the report logic is something like this:

For any selected orgunit at level 2 (this is your report parameter) populate a report table with ALL children (districts) and the grandchildren (ward level) of orgunit type X.

That should be doable to add to report tables I think.

**I will discuss with Tri about this to see if he can do the report table to excel report module. Because I am using the Excel report module. And it doesn’t desplay the org units as the requirement of report. And Tri said it’s impossible to list the ‘grandchildren’ org units. **

I would rather try to improve the report table functionality than splitting up into two databases just to solve a report problem.

Does this make sense?

Ola

**Thank you very much. **

···

On Thu, Dec 17, 2009 at 4:33 PM, Ola Hodne Titlestad olatitle@gmail.com wrote:

–

Thuy
HISP Vietnam
+84902079126

4

Hi Thuy,

When you say reports, which module are you referring to? There are many ways to set up reports in DHIS2, and my first thought is that it should be possible to generate all your reports from the same database, and that splitting up into 2 databases sounds like al lot of extra work for you guys (maintenance, import/export etc.).

**Yes, I really don’t like this seperating. **

Using report tables as your data source for your reports you should be able to generate more or less anything you want, you can e.g combine orgunits from any level. You should then be able to use report tables (with report parameters) in any of the report modules whether it is Jasper, BIRT, Excel or some other local variant you are using.

You say, some orguits from district level and some orhgunits from ward level, does that mean not all districts in one province and not all wards, or all districts and some wards of a particular type maybe?

**Yes, I mean that. Only some Org unit belong to district level, but it is created in the ward level in DHIS. And another some org units in upper level. **

If you create one report table per province you can select exactly the orgunits you want from district and ward level, and then not select orgunit as report parameter (all orgunits in the report are fixed). This will work, but since you have 84 (?) provinces this easily becomes very messy.

**Actually I created org unit groups which included org units which I want to display in the report. And in each report has more than 1 org unit group. Such as District org unit(some org unit in the level 4), ward org units (some org unit in the level 4), and province org units(some org unit in level 3). **

**and parameter is selected when generate report. **

in the report at province level (level 2) then parameter is org unit in level 2.

and if the report at district level (level 3) then parameter is org unit in level 3.

As an example let’s say your report needs to list all orgunits of the types “Province hospital” and “District hospital” (at any level) below any selected orgunit. These two types are orgunit groups and you have assigned the orgunits you need to these groups already.

If we could then add to the report table functionality:

  1. a selection box where we can list any number of orgunit groups to filter on
  2. a new report parameter “All below this parent” which is similar to orgunit parent, but one that includes all orgunits below, not just 1 level below

Combined those two additions would give you a list of the orgunits you need right?

This needs to be built into the report table functionality of course, and if it sounds like a godo idea to you then Lars needs to comment on whether this is doable or not.

So the problem relates to making these reports generic and reusable for all your provinces, for any orgunit at level 2. I assume that the orgunits you want to display at ward level are of a special type, like a health center more than just the normal ward (phoung/xa). Is that right?

Yes that is right.

If you have assigned a common orgunit group for all these ward level units that you want to see in the report then it should be possible to modify the report tables to generically list all these units even for a province report.

**Yes I already assigned the common org units to org unit groups. **

My point is that if the report logic is something like this:

For any selected orgunit at level 2 (this is your report parameter) populate a report table with ALL children (districts) and the grandchildren (ward level) of orgunit type X.

That should be doable to add to report tables I think.

**I will discuss with Tri about this to see if he can do the report table to excel report module. Because I am using the Excel report module. And it doesn’t desplay the org units as the requirement of report. And Tri said it’s impossible to list the ‘grandchildren’ org units. **

OK, do that. Not sure how you select data for an excel report now, but if it could build on a report table (which already has quite advanced selections and relative preiods etc.) it would be easier to solve this and other future report requirements.

Ola

···

2009/12/17 Thuy Nguyen thuy.hispvietnam@gmail.com

On Thu, Dec 17, 2009 at 4:33 PM, Ola Hodne Titlestad olatitle@gmail.com wrote:

I would rather try to improve the report table functionality than splitting up into two databases just to solve a report problem.

Does this make sense?

Ola

**Thank you very much. **

–

Thuy
HISP Vietnam
+84902079126

5

Hi, I agree with what Ola says, you should definitely not split your database because of this. I am not sure if it is feasible to involve report tables in excel reporting. In any case there is a method on OrganisationUnitService which allows you to get all children of a specific orgunit on a specific orgunit level:

/**
 * Returns all OrganisationUnits which are children of the given unit and are
 * at the given hierarchical level. The root OrganisationUnits are at level 1.
··· 
 *
 * @param level the hierarchical level.

 * @param parent the parent unit.
 * @return all OrganisationUnits which are children of the given unit and are
 *         at the given hierarchical level.
 * @throws IllegalArgumentException if the level is illegal.

 */
Collection<OrganisationUnit> getOrganisationUnitsAtLevel( int level, OrganisationUnit parent );

Lars

6

Hi Thuy,

Have you seen the dataSetReport module? There you have two options to generate reports - one for the selected orgUnit only and the other one for the selected orgUnit and all its children, grandchildren, grandgrand… or generally all orgUnits below it. I am not sure whether this logic fits into your requirement. But one thing is clear - without splitting your database you can aggregate values from a collection of orgUnits and generate a report. The collection of orgUnits can be obtained using the method Lars suggested or even you can handpick any and make a group.

Thank you
Abyot.

···

2009/12/17 Lars Helge Øverland larshelge@gmail.com

Hi, I agree with what Ola says, you should definitely not split your database because of this. I am not sure if it is feasible to involve report tables in excel reporting. In any case there is a method on OrganisationUnitService which allows you to get all children of a specific orgunit on a specific orgunit level:

/**
 * Returns all OrganisationUnits which are children of the given unit and are
 * at the given hierarchical level. The root OrganisationUnits are at level 1.
 *
 * @param level the hierarchical level.


 * @param parent the parent unit.
 * @return all OrganisationUnits which are children of the given unit and are
 *         at the given hierarchical level.
 * @throws IllegalArgumentException if the level is illegal.


 */
Collection<OrganisationUnit> getOrganisationUnitsAtLevel( int level, OrganisationUnit parent );

Lars

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

7

Thank you very much for your suggestions. Now Tri start to think and research about this. I hope this problem will be solved soon.

···

–

Thuy
HISP Vietnam
+84902079126

8

Dear all,
Happy to tell that from your suggestions, Tri solved that problem. Now excel report module can generate the ‘grandchildren’ org units.

···

On Fri, Dec 18, 2009 at 10:52 AM, Thuy Nguyen thuy.hispvietnam@gmail.com wrote:

Thank you very much for your suggestions. Now Tri start to think and research about this. I hope this problem will be solved soon.

–

Thuy
HISP Vietnam
+84902079126

–

Thuy
HISP Vietnam
+84902079126