Hi Jim, Great, query worked perfectly ! Thanks a lot,
Pepe Ken Mvulu 2015-12-17 16:28 GMT+01:00 Jim Grace <jimgr...@gmail.com>: > Hi Pepe, > > The resource table _orgunitstructure can help you out. There is one row in > this table for every organisation unit in the system, and it has references > to all the higher-level organisation units (if any) above that org unit. > > Just make sure that the resource tables are refreshed after you add, > rename, or reorganize any organisation units, and before you run the query. > The resource tables are refreshed as part of the analytics refresh, or they > can be refreshed separately by Apps -> Data Administration -> Resource > Table -> Generate tables, or through a Web API call (see section 1.30 of > the developer manual). Then your query can be something like: > > select o1.name, o2.name, o3.name, o4.name, o5.name, o6.name, > o6.coordinates > from _orgunitstructure o > join organisationunit o1 on o1.organisationunitid = o.idlevel1 > join organisationunit o2 on o2.organisationunitid = o.idlevel2 > join organisationunit o3 on o3.organisationunitid = o.idlevel3 > join organisationunit o4 on o4.organisationunitid = o.idlevel4 > join organisationunit o5 on o5.organisationunitid = o.idlevel5 > join organisationunit o6 on o6.organisationunitid = o.idlevel6 > where o.level = 6 > > In this case the 'where' clause may not even be needed. The inner joins on > idlevel1-6 will filter out any table records that apply to higher-level org > units, because these will have a null value in one or more of these columns. > > Cheers, > Jim > > > On Thu, Dec 17, 2015 at 8:06 AM, Pepe ken mvulu <pepeken...@gmail.com> > wrote: > >> Hi Jim, >> >> Currently we are using version 2.20, we have 7 levels of organisation >> unit, a map is available at Level 4 (Zone de Santé), level 6 contains the >> coordinates of the villages that I want to export as CVS or Excel format >> using a query or other procedure available. >> >> Here is a screenshot of Level 6 >> >> Here is the picture I want to download for all villages >> >> >> >> *Name level 1* >> >> *Name level 2* >> >> *Name level 3* >> >> *Name level 4* >> >> *Name level 5* >> >> *Name level 6* >> >> *Coordinates level 6 (decimal)* >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> Regards, >> >> Pepe Ken Mvulu >> >> >> >> *De :* Jim Grace [mailto:jimgr...@gmail.com] >> *Envoyé :* mercredi 16 décembre 2015 19:09 >> *À :* Pepe Ken Mvulu >> *Cc :* Dhis2-User; DHIS 2 developers >> *Objet :* Re: [Dhis2-users] Export hierarchy coordinates >> >> >> >> Hi Pepe, >> >> >> >> Can you say more about what you mean by "hierarchy coordinates"? Are you >> talking about the GIS location of the organisation units? Or the path of >> ancestor organisation units from each organisation unit up to the root? >> What exactly would you like to see and, ideally, in what format? (An >> example could help.) >> >> >> >> Which version of DHIS 2 are you using. (We've been adding some new >> features relating to organisation unit hierarchy.) >> >> >> >> Cheers, >> >> Jim >> >> >> >> On Wed, Dec 16, 2015 at 9:11 AM, Pepe Ken Mvulu <pepeken...@gmail.com> >> wrote: >> >> Hi All, >> I need to export the hierarchy coordinates of organisation units, but >> I do not know what to do. Any ideas? any query ? >> Best Regards >> >> Pepe Ken Mvulu >> >> _______________________________________________ >> 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
