dhis2-users team mailing list archive

[Lars Helge Øverland <larshelge@xxxxxxxxx>]

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@xxxxxxxxx> 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@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
>>> Post to     : dhis2-users@xxxxxxxxxxxxxxxxxxx
>>> Unsubscribe : https://launchpad.net/~dhis2-users
>>> More help   : https://help.launchpad.net/ListHelp
>>
>>
>>
>> _______________________________________________
>> Mailing list: https://launchpad.net/~dhis2-users
>> Post to     : dhis2-users@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~dhis2-users
>> More help   : https://help.launchpad.net/ListHelp
>
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~dhis2-users
> Post to     : dhis2-users@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~dhis2-users
> More help   : https://help.launchpad.net/ListHelp
>