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.picker...@gmail.com> 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 <foba...@softcall.co.ke>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@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