dhis2-users team mailing list archive

[Felix Obareh <fobareh@xxxxxxxxxxxxxx>]

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