dhis2-users team mailing list archive

[Murod Latifov <mlatifov@xxxxxxxxx>]

Hi Obare,

You also need to join dataelement table and add the followoing in your
WHERE clause:
domaintype='aggregate' and aggregationtype= bla bla to reduce in away that
non numeric values do not get into query. Hope this helps.

best,
murod



On Fri, Nov 29, 2013 at 4:16 PM, Felix Obareh <fobareh@xxxxxxxxxxxxxx>wrote:

> Thank Jason,
> I have decided to go with the SQL Query. I am using the SQL Query example
> i the user manual together with the demo database for Sierra Leone.
>
> It is working fine for elements of integer type and i can run the query
> from my pgAdmin and i get them fine. I have a problem with displaying data
> elements of text and varchar type.
>
> Here is what am running as querry:-
>
> ------Trancated--------
>
> left outer join organisationunit Sublocation
>   on (ous.idlevel6=sublocation.organisationunitid)
> left outer join (
>   *select sourceid, sum(cast(value as double precision)) as value*
>   from datavalue
>   where dataelementid=8305
>
>  group by sourceid) as *triggerstate* on
> triggerstate.sourceid=ou.organisationunitid
> where ous.level=7
>
> ****If i run the same query for a data element of date of text type or
> yes/no
> ***NB the triggerstate store a true or false value in the the datavalue
> table
>
> *HERE is the SQL error i get*
>
> ERROR:  invalid input syntax for type double precision: "false"
>
> ********** Error **********
>
> ERROR: invalid input syntax for type double precision: "false"
> SQL state: 22P02
>
>
> ----------------------------------
> Please assist
>
>
> On Wed, Nov 27, 2013 at 5:44 PM, Jason Pickering <
> jason.p.pickering@xxxxxxxxx> wrote:
>
>> Hi Obare,
>> The reason for this is because you will only be able to see the
>> aggregated data in DHIS2. In your case, it does not seem you need the
>> aggregated data, but instead the raw data. One way to do this is with a
>> custom report, which would use a JDBC data source, and then an SQL query to
>> retreive the data. You might need some paramaters like the period and the
>> particular orgunit. You can read about custom reports in the DHIS2
>> documentation. Another possible solution would be an SQL Query, which would
>> allow you to pull out the raw data into a CSV file for subsequent analysis.
>>
>> Hope this helps.
>>
>> Regards,
>> Jason
>>
>>
>>
>> On Wed, Nov 27, 2013 at 2:34 PM, Felix Obareh <fobareh@xxxxxxxxxxxxxx>wrote:
>>
>>> Hi,
>>>
>>> I a form similar to the Facility Assessment Form in the demo.dhis2.org. I have done some data entry but i don't know how to get the report for
>>> that form
>>>
>>> Among my Elements are:-
>>>
>>> 1) Date Facility was commissioned
>>> 2) Facility state on Commissioning with options set of ( Ready, Lacking
>>> Some Departments, Not ready)
>>> 3) Date of Certification
>>> 4) Date of Verification
>>> 5) Source of Funds with option sets (Government, Donor)
>>> 6) Is facility well maintained (Yes/No)
>>> 7) Key issues identified & suggested solutions (Text)
>>> 8) Number of Practitioners in the Facility
>>>
>>> I can the above data on the entry form by i cant create a single report
>>> about the facility. The pivot tables on show the value of element 8. The
>>> value of the number of practitioners.
>>>
>>> Any assistance will be highly appreciated. I have three more Facility
>>> based forms which i want to get reports for across the country
>>>
>>> Cheers
>>>
>>>
>>>
>>> --
>>>
>>> _______________________________________________
>>> 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
>>>
>>>
>>
>
> _______________________________________________
> 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
>
>