dhis2-users team mailing list archive

[Jason Pickering <jason.p.pickering@xxxxxxxxx>]

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@xxxxxxxxxx> 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<https://launchpad.net/~dhis2-users>
>> Post to     : dhis2-users@lists.launchpad.**net<dhis2-users@xxxxxxxxxxxxxxxxxxx>
>> Unsubscribe : https://launchpad.net/~dhis2-**users<https://launchpad.net/~dhis2-users>
>> More help   : https://help.launchpad.net/**ListHelp<https://help.launchpad.net/ListHelp>
>>
>
>
> ______________________________**_________________
> Mailing list: https://launchpad.net/~dhis2-**users<https://launchpad.net/~dhis2-users>
> Post to     : dhis2-users@lists.launchpad.**net<dhis2-users@xxxxxxxxxxxxxxxxxxx>
> Unsubscribe : https://launchpad.net/~dhis2-**users<https://launchpad.net/~dhis2-users>
> More help   : https://help.launchpad.net/**ListHelp<https://help.launchpad.net/ListHelp>
>