dhis2-users team mailing list archive

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

Hi Tantely,

Are you limiting the data to a single data set at a time?  You might try to
limit the result set by putting "limit 100" at the end of the query.  That
way you can at least see if the query is working.  There are often limits
set on the postgres server for the number of records that can be returned,
especially on cloud servers.  You might need to change a parameter in the
config.

Randy

On Mon, Apr 4, 2016 at 5:04 AM, Raminosoa Rabemanantsoa, Tantely <
traminosoa@xxxxxxxxxx> wrote:

> Thank you Randy and Knut for the scripts. While I am running the sample
> script and customizing with the dataset name, I am facing another issue
> with a timed out connection due to a busy server. I cannot output any
> result yet. Do you know if it is a server memory issue?
>
> Best Regards.
>
> Tantely.
>
> On Sat, Apr 2, 2016 at 8:39 AM, Wilson, Randy <rwilson@xxxxxxx> wrote:
>
>> 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
>>
>


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