SQL query for DataSets

Support - Assistance technique
1

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.

2

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@mikolo.org 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@lists.launchpad.net

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

3

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

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.

···

On Tue, Feb 21, 2017 at 1:31 PM, Jim Grace jim@dhis2.org 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

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

Timothy Harding
Sr. Systems Analyst, BAO Systems

+1 202-536-1541 | tharding@baosystems.com | http://www.baosystems.com | Skype: hardingt@gmail.com | 2900 K Street, Suite 406, Washington D.C. 20007

On Tue, Feb 21, 2017 at 12:53 PM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org 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@lists.launchpad.net

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

–

1 Like
4

…and if you also want the datasets that are NOT assigned to ANY orgunits you could change the two JOINs to LEFT OUTER JOIN…

This message and any attachments are subject to a disclaimer published at http://www.hisp.org/policies.html#comms_disclaimer. Please read the disclaimer before opening any attachment or taking any other action in terms of this electronic transmission. If you cannot access the disclaimer, kindly send an email to disclaimer@hisp.org and a copy will be provided to you. By replying to this e-mail or opening any attachment you agree to be bound by the provisions of the disclaimer.

···

On 21 February 2017 at 20:31, Jim Grace jim@dhis2.org 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

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

–

On Tue, Feb 21, 2017 at 12:53 PM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org 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@lists.launchpad.net

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

–

Ant Snyman

Cell: 0824910449

Landline: 028 2713242

Health Information Systems Program - SA

5

Dear Jim,

I have tested both scripts and they are all working. The second one is more straightforward for the analyzes that I will run with external list.

Thank you for your support!

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.

···

Le 21 févr. 2017 9:32 PM, “Jim Grace” jim@dhis2.org a écrit :

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@mikolo.org 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@lists.launchpad.net

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

6

Thank you Ant, that is straightforward! We need that sometimes to counter check all things.

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.

···

On Wed, Feb 22, 2017 at 7:16 AM, Ant Snyman ant@hisp.org wrote:

…and if you also want the datasets that are NOT assigned to ANY orgunits you could change the two JOINs to LEFT OUTER JOIN…

This message and any attachments are subject to a disclaimer published at http://www.hisp.org/policies.html#comms_disclaimer. Please read the disclaimer before opening any attachment or taking any other action in terms of this electronic transmission. If you cannot access the disclaimer, kindly send an email to disclaimer@hisp.org and a copy will be provided to you. By replying to this e-mail or opening any attachment you agree to be bound by the provisions of the disclaimer

On 21 February 2017 at 20:31, Jim Grace jim@dhis2.org 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

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

–

Ant Snyman

Cell: 0824910449

Landline: 028 2713242

Health Information Systems Program - SA

On Tue, Feb 21, 2017 at 12:53 PM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org 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@lists.launchpad.net

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

–

7

And to tag onto Tim’s message, if you know the particular OU of interest, you can all datasets associated with it via:
https://play.dhis2.org/demo/api/dataSet?filter=organisationUnits.id:eq:THEOUUIDGOESHERE

Greg

···

On Tue, Feb 21, 2017 at 1:41 PM, Timothy Harding tharding@baosystems.com wrote:

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

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.

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

–

Timothy Harding
Sr. Systems Analyst, BAO Systems

+1 202-536-1541 | tharding@baosystems.com | http://www.baosystems.com | Skype: hardingt@gmail.com | 2900 K Street, Suite 406, Washington D.C. 20007

On Tue, Feb 21, 2017 at 1:31 PM, Jim Grace jim@dhis2.org 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

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

On Tue, Feb 21, 2017 at 12:53 PM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org 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@lists.launchpad.net

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

–

Greg Wilson
BAO Systems

8

Hi Greg, Tim,

Thank you for sharing the alternative way using API. Is it possible to go backward using the API, if I would like to go beyound getting the list of assigned dataSets and this time assign a set of dataSets to a group of OU? That will also be helpful!

Kind 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.

···

On Tue, Feb 21, 2017 at 10:54 PM, Greg Wilson gwilson@baosystems.com wrote:

And to tag onto Tim’s message, if you know the particular OU of interest, you can all datasets associated with it via:
https://play.dhis2.org/demo/api/dataSet?filter=organisationUnits.id:eq:THEOUUIDGOESHERE

Greg

On Tue, Feb 21, 2017 at 1:41 PM, Timothy Harding tharding@baosystems.com wrote:

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

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.

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

–

Greg Wilson
BAO Systems

Timothy Harding
Sr. Systems Analyst, BAO Systems

+1 202-536-1541 | tharding@baosystems.com | http://www.baosystems.com | Skype: hardingt@gmail.com | 2900 K Street, Suite 406, Washington D.C. 20007

On Tue, Feb 21, 2017 at 1:31 PM, Jim Grace jim@dhis2.org 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

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

On Tue, Feb 21, 2017 at 12:53 PM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org 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@lists.launchpad.net

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

–

9

No problem Tantely,

You can assign datasets to an OU group here:

https://play.dhis2.org/demo/dhis-web-maintenance/#/edit/dataSetSection/dataSet/lyLU2wR22tC

image

Or if you wanted to see what datasets are currently assigned to Org Units of a specific group, I’ll build the api query below like I when working in the browser:

···

On Wed, Feb 22, 2017 at 12:49 AM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org wrote:

Hi Greg, Tim,

Thank you for sharing the alternative way using API. Is it possible to go backward using the API, if I would like to go beyound getting the list of assigned dataSets and this time assign a set of dataSets to a group of OU? That will also be helpful!

Kind 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.

Timothy Harding
Sr. Systems Analyst, BAO Systems

+1 202-536-1541 | tharding@baosystems.com | http://www.baosystems.com | Skype: hardingt@gmail.com | 2900 K Street, Suite 406, Washington D.C. 20007

On Tue, Feb 21, 2017 at 10:54 PM, Greg Wilson gwilson@baosystems.com wrote:

And to tag onto Tim’s message, if you know the particular OU of interest, you can all datasets associated with it via:
https://play.dhis2.org/demo/api/dataSet?filter=organisationUnits.id:eq:THEOUUIDGOESHERE

Greg

On Tue, Feb 21, 2017 at 1:41 PM, Timothy Harding tharding@baosystems.com wrote:

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

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.

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

–

Greg Wilson
BAO Systems

Timothy Harding
Sr. Systems Analyst, BAO Systems

+1 202-536-1541 | tharding@baosystems.com | http://www.baosystems.com | Skype: hardingt@gmail.com | 2900 K Street, Suite 406, Washington D.C. 20007

On Tue, Feb 21, 2017 at 1:31 PM, Jim Grace jim@dhis2.org 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

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

On Tue, Feb 21, 2017 at 12:53 PM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org 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@lists.launchpad.net

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

–

10

Hi Tim,

Thank you for those information, they are helpful! I will be back in case I have further questions.

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.

image

···

On Wed, Feb 22, 2017 at 4:23 PM, Timothy Harding tharding@baosystems.com wrote:

No problem Tantely,

You can assign datasets to an OU group here:

https://play.dhis2.org/demo/dhis-web-maintenance/#/edit/dataSetSection/dataSet/lyLU2wR22tC

Or if you wanted to see what datasets are currently assigned to Org Units of a specific group, I’ll build the api query below like I when working in the browser:

Timothy Harding
Sr. Systems Analyst, BAO Systems

+1 202-536-1541 | tharding@baosystems.com | http://www.baosystems.com | Skype: hardingt@gmail.com | 2900 K Street, Suite 406, Washington D.C. 20007

On Wed, Feb 22, 2017 at 12:49 AM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org wrote:

Hi Greg, Tim,

Thank you for sharing the alternative way using API. Is it possible to go backward using the API, if I would like to go beyound getting the list of assigned dataSets and this time assign a set of dataSets to a group of OU? That will also be helpful!

Kind 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.

On Tue, Feb 21, 2017 at 10:54 PM, Greg Wilson gwilson@baosystems.com wrote:

And to tag onto Tim’s message, if you know the particular OU of interest, you can all datasets associated with it via:
https://play.dhis2.org/demo/api/dataSet?filter=organisationUnits.id:eq:THEOUUIDGOESHERE

Greg

On Tue, Feb 21, 2017 at 1:41 PM, Timothy Harding tharding@baosystems.com wrote:

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

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.

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

–

Greg Wilson
BAO Systems

Timothy Harding
Sr. Systems Analyst, BAO Systems

+1 202-536-1541 | tharding@baosystems.com | http://www.baosystems.com | Skype: hardingt@gmail.com | 2900 K Street, Suite 406, Washington D.C. 20007

On Tue, Feb 21, 2017 at 1:31 PM, Jim Grace jim@dhis2.org 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

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

On Tue, Feb 21, 2017 at 12:53 PM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org 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@lists.launchpad.net

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

–