dhis2-devs team mailing list archive

[Timothy Harding <tharding@xxxxxxxxxxxxxx>]

An alternative way to do this is to use an API call to the
/organisationUnits/ and have it also return the names of the dataSets
assigned:

https://play.dhis2.org/dev/api/organisationUnits/?fields=name,id,dataSets[name,id]&paging=false

Replace the "https://play.dhis2.org/dev"; with your DHIS 2 instance

[image: Inline image 1]

Also note that paging=false will return all the org units, depending on how
many you have, this can take a while to return to your browser.






*Timothy Harding*
Sr. Systems Analyst, BAO Systems
+1 202-536-1541 | tharding@xxxxxxxxxxxxxx | http://www.baosystems.com | Skype:
hardingt@xxxxxxxxx | 2900 K Street, Suite 406, Washington D.C. 20007

On Tue, Feb 21, 2017 at 1:31 PM, Jim Grace <jim@xxxxxxxxx> wrote:

> If you want to show each pair of assigned orgUnit and dataSet, try
> something like:
>
> SELECT ou.name AS orgunit, ds.name AS dataset
> FROM dataset ds
> JOIN datasetsource dss ON dss.datasetid = ds.datasetid
> JOIN organisationunit ou ON ou.organisationunitid = dss.sourceid
> ORDER BY ou.name, ds.name;
>
> If you want only one row per orgUnit and a column that concatenates all
> assigned dataset names, try (in Postgresql):
>
> SELECT ou.name AS orgunit, string_agg(ds.name, ' | ' ORDER BY ds.name) AS
> datasets
> FROM dataset ds
> JOIN datasetsource dss ON dss.datasetid = ds.datasetid
> JOIN organisationunit ou ON ou.organisationunitid = dss.sourceid
> GROUP BY ou.name
> ORDER BY ou.name;
>
> Cheers,
> Jim
>
>
> On Tue, Feb 21, 2017 at 12:53 PM, Raminosoa Rabemanantsoa, Tantely <
> traminosoa@xxxxxxxxxx> wrote:
>
>> Dear Community,
>>
>> I am trying to pull out the list of all orgUnits and the name of the
>> questionnaire assigned to each of them via SQL View. In which table should
>> I base the SQL query in order to have the list of dataSet assigned to an
>> orgUnit?
>>
>> Regards,
>>
>> Tantely.
>>
>> *This message and its attachments are confidential and solely for the
>> intended recipients. If received in error, please delete them and notify
>> the sender via reply e-mail immediately.*
>>
>> _______________________________________________
>> Mailing list: https://launchpad.net/~dhis2-devs
>> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~dhis2-devs
>> More help   : https://help.launchpad.net/ListHelp
>>
>>
>
>
> --
> Jim Grace
> Core developer, DHIS 2
> HISP US Inc.
> http://www.dhis2.org <https://www.dhis2.org/>
>
> _______________________________________________
> 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
>
>