Need some help

Support - Assistance technique
1

Hi All,

I have been asked if it is possible to have a Pivot Table like that of
the DHIS1.4 in which I could run a query and generate data of
supporting partners to the MOH.

Example
If IRC is supporting 10 facilities in Margibi County can I generate a
PT with only those 10 facilities supported by IRC? It that can work
can I please be guided.

Thanks

Stephen

2

Stephen,

Hi All,

I have been asked if it is possible to have a Pivot Table like that of
the DHIS1.4 in which I could run a query and generate data of
supporting partners to the MOH.

This is possible, see below

Example
If IRC is supporting 10 facilities in Margibi County can I generate a
PT with only those 10 facilities supported by IRC? It that can work
can I please be guided.

Yes, but I suspect you have to write a new pivotsource/"view" for it. If you are using the MyDatamart to fetch data to your (or IRCs) local machine, you could get all facility data from Margibi County downloaded quite easily. However, since you just want a subset, you would probably have to make this new pivot view (e.g. write some query, in your DHIS2 database I think). Since I'm no expert on neither queries nor MyDatamart, I leave it to others to guide you here, but I suspect it makes sense to keep record of the "IRC facilities" inside DHIS2, using orgunit groups. This way you can probably write your query based on matching orgunit groups, and you don't have to worry about your query again as long as you keep list of IRC facilities updated in DHIS2.

Regards.
Johan

···

On 14.02.2012 17:59, Stephen Gbanyan wrote:

Thanks

Stephen

_______________________________________________
Mailing list: DHIS 2 Users in Launchpad
Post to : dhis2-users@lists.launchpad.net
Unsubscribe : DHIS 2 Users in Launchpad
More help : ListHelp - Launchpad Help

3

Hi Stephen,

I think the key question is whether this is a more general issue. If you only need to generate for IRC, then as Johan outlines, it is just matter of creating a specific pivot source view and filtering out the desired orguint group.

However, creating views directly in the DHIS2 database is not so straightforward, as it conflicts with the regeneration of resource tables (such as _orgunitstructure) which are normally used in producing views for the pivot queries. I have opted for Postgresql stored procedures which materialize the view and can be triggered with a cron job instead. You can use the SQL View function of DHIS2, but there is no easy way to trigger/regenerate these on a regular basis.

If this is a more general issue, then I think it should be fairly simple to create a procedure to materialize a table for each orgunitgroup, which could then be copied to a CSV file, and then provided to IRC (for instance).

Either way, I would discourage the use of a Postgresql view, as this is going to cause problem if you link the view to any of the resource tables (which are destroyed and recreated upon regeneration).

I have no time to look at this right now, but once I get around to it, I can update you on the approach I will take with this problem.

Regards,

Jason

···

On Tue, Feb 14, 2012 at 9:19 PM, Johan Sæbø johansa@ifi.uio.no wrote:

Stephen,

On 14.02.2012 17:59, Stephen Gbanyan wrote:

Hi All,

I have been asked if it is possible to have a Pivot Table like that of

the DHIS1.4 in which I could run a query and generate data of

supporting partners to the MOH.

This is possible, see below

Example

If IRC is supporting 10 facilities in Margibi County can I generate a

PT with only those 10 facilities supported by IRC? It that can work

can I please be guided.

Yes, but I suspect you have to write a new pivotsource/“view” for it. If you are using the MyDatamart to fetch data to your (or IRCs) local machine, you could get all facility data from Margibi County downloaded quite easily. However, since you just want a subset, you would probably have to make this new pivot view (e.g. write some query, in your DHIS2 database I think). Since I’m no expert on neither queries nor MyDatamart, I leave it to others to guide you here, but I suspect it makes sense to keep record of the “IRC facilities” inside DHIS2, using orgunit groups. This way you can probably write your query based on matching orgunit groups, and you don’t have to worry about your query again as long as you keep list of IRC facilities updated in DHIS2.

Regards.

Johan

Thanks

Stephen

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

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

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

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

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

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

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

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

4

Hi, quick comment, if you save your views through the "Sql View"
management in data administration module they will be dropped and
recreated with the resource tables.

Lars

···

On Tue, Feb 14, 2012 at 8:46 PM, Jason Pickering <jason.p.pickering@gmail.com> wrote:

Hi Stephen,

I think the key question is whether this is a more general issue. If you
only need to generate for IRC, then as Johan outlines, it is just matter of
creating a specific pivot source view and filtering out the desired orguint
group.

However, creating views directly in the DHIS2 database is not so
straightforward, as it conflicts with the regeneration of resource tables
(such as _orgunitstructure) which are normally used in producing views for
the pivot queries. I have opted for Postgresql stored procedures which
materialize the view and can be triggered with a cron job instead. You can
use the SQL View function of DHIS2, but there is no easy way to
trigger/regenerate these on a regular basis.

If this is a more general issue, then I think it should be fairly simple to
create a procedure to materialize a table for each orgunitgroup, which could
then be copied to a CSV file, and then provided to IRC (for instance).

Either way, I would discourage the use of a Postgresql view, as this is
going to cause problem if you link the view to any of the resource tables
(which are destroyed and recreated upon regeneration).

I have no time to look at this right now, but once I get around to it, I can
update you on the approach I will take with this problem.

Regards,
Jason

On Tue, Feb 14, 2012 at 9:19 PM, Johan Sæbø <johansa@ifi.uio.no> wrote:

Stephen,

On 14.02.2012 17:59, Stephen Gbanyan wrote:

Hi All,

I have been asked if it is possible to have a Pivot Table like that of
the DHIS1.4 in which I could run a query and generate data of
supporting partners to the MOH.

This is possible, see below

Example
If IRC is supporting 10 facilities in Margibi County can I generate a
PT with only those 10 facilities supported by IRC? It that can work
can I please be guided.

Yes, but I suspect you have to write a new pivotsource/"view" for it. If
you are using the MyDatamart to fetch data to your (or IRCs) local machine,
you could get all facility data from Margibi County downloaded quite easily.
However, since you just want a subset, you would probably have to make this
new pivot view (e.g. write some query, in your DHIS2 database I think).
Since I'm no expert on neither queries nor MyDatamart, I leave it to others
to guide you here, but I suspect it makes sense to keep record of the "IRC
facilities" inside DHIS2, using orgunit groups. This way you can probably
write your query based on matching orgunit groups, and you don't have to
worry about your query again as long as you keep list of IRC facilities
updated in DHIS2.

Regards.
Johan

Thanks

Stephen

_______________________________________________
Mailing list: DHIS 2 Users in Launchpad
Post to : dhis2-users@lists.launchpad.net
Unsubscribe : DHIS 2 Users in Launchpad
More help : ListHelp - Launchpad Help

_______________________________________________
Mailing list: DHIS 2 Users in Launchpad
Post to : dhis2-users@lists.launchpad.net
Unsubscribe : DHIS 2 Users in Launchpad
More help : ListHelp - Launchpad Help

_______________________________________________
Mailing list: DHIS 2 Users in Launchpad
Post to : dhis2-users@lists.launchpad.net
Unsubscribe : DHIS 2 Users in Launchpad
More help : ListHelp - Launchpad Help