dhis2-users team mailing list archive

["Wilson, Randy" <rwilson@xxxxxxx>]

Hi Tantely,

Here is a sample query that will give you what I think you want:
________
select dv.sourceid as organisationunitid,
ou.name, ou.code as facilitycode, de.name, 'Vaccination' as datasetname,
pe.startdate, pe.enddate, coc.categoryoptioncomboname, dv.value

from datavalue dv
inner join organisationunit ou on (dv.sourceid=ou.organisationunitid)
inner join dataelement de on (dv.dataelementid=de.dataelementid)
inner join period pe on (dv.periodid=pe.periodid)
inner join _categoryoptioncomboname coc on
(dv.categoryoptioncomboid=coc.categoryoptioncomboid)
where dv.dataelementid in
(select dataelementid from datasetmembers where datasetid in (
select datasetid from dataset where name='Vaccination'))
_____

Just replace 'Vaccination' with the name of your dataset.  You can also
create a variable for the dataset name  of your SQL view in the newer
versions of DHIS-2.

Good luck.

On Fri, Apr 1, 2016 at 10:40 PM, Knut Staring <knutst@xxxxxxxxx> wrote:

> Hi Tantely,
>
> You are probably looking for the datasetmembers table and the
> datasetsource table (confusingly and historically, orgunits used to be
> referred to as sources, and this is still reflected in a few places).
>
> When you say data elements, I assume you mean data values? If so, then
> something along the lines of the query below (though this didnt work for me
> right now
>
> SELECT de.name, dv.value FROM datavalue dv NATURAL JOIN dataelement de
> NATURAL JOIN datasetmembers dsm NATURAL JOIN datasetsource dss
>
> On Fri, Apr 1, 2016 at 12:29 AM, Raminosoa Rabemanantsoa, Tantely <
> traminosoa@xxxxxxxxxx> wrote:
>
>> Dear DHIS2 users,
>>
>>
>> I would like to export all data elements related to all organization
>> units linked to a data set (questionnaire) on a specific period through the
>> SQL View. Does anyone have some idea of on which table (/tables) should I
>> make the SQL select script in DHIS2? The reason is that I have already
>> exported the data through the “data export” (csv) but it returned me data
>> values along columns and I need to transpose in Excel each Organization
>> Unit to have data values aligned by row.
>>
>>
>> Thank you for your suggestions.
>>
>>
>> Best 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-users
>> Post to     : dhis2-users@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~dhis2-users
>> More help   : https://help.launchpad.net/ListHelp
>>
>>
>
>
> --
> Knut Staring
> Dept. of Informatics, University of Oslo
> Norway: +4791880522
> Skype: knutstar
> http://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
>
>


-- 
*Randy Wilson*
*Team Leader: **Knowledge Management, Data Use and Research*
Rwanda Health System Strengthening Activity
Management Sciences for Health
Rwanda-Kigali
Direct: +250 788308835
E-mail: rwilson@xxxxxxx
Skype: wilsonrandy_us
<http://www.msh.org/>
Stronger health systems. Greater health impact.
<https://www.facebook.com/ManagementSciencesForHealth>
<https://twitter.com/MSHHealthImpact>
<https://www.youtube.com/user/MSHHealthImpact>
www.msh.org

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