2010/11/24 Jason Pickering <[email protected]> > Hi Hieu and Lars. > > After digesting our conversation this morning, I played around with > the code and did this in StatementManagerDataBrowserStore.java > > > public Integer setCountDataElementsForOrgUnitBetweenPeriods( > DataBrowserTable table, Integer orgUnitId, > List<Integer> betweenPeriodIds ) > { > StatementHolder holder = statementManager.getHolder(); > > Integer numResults = 0; > StringBuffer sqlsb = new StringBuffer(); > > int i = 0; > for ( Integer periodId : betweenPeriodIds ) > { > i++; > > sqlsb > .append( " SELECT de.dataelementid, de.name AS > DataElement, dv.value AS counts_of_aggregated_values, p.periodid AS > PeriodId, p.startDate AS ColumnHeader " ); > sqlsb.append( "FROM dataelement AS de " ); > sqlsb.append( "INNER JOIN datavalue AS dv ON > (de.dataelementid = dv.dataelementid) " ); > sqlsb.append( "INNER JOIN organisationunit AS o ON > (dv.sourceid = o.organisationunitid) " ); > sqlsb.append( "JOIN period p ON (dv.periodid = p.periodid) " ); > sqlsb.append( "WHERE o.organisationunitid = '" + orgUnitId + "' > " ); > sqlsb.append( "AND dv.periodid = '" + periodId + "' " ); > > sqlsb.append( i == betweenPeriodIds.size() ? "ORDER BY > PeriodId " : "\n UNION \n" ); > } > ... > > The results look something like this.. > > Data Element January 2010 February 2010 March 2010 > Alive on ART after 12 months (calc) 7 6 9 > Amoxicillin 125mg 5ml suspension 75ml stock out 0 0 0 > Antenatal 1st visit 20 weeks or later 39 36 46 > Antenatal 1st visit before 20 weeks 6 6 34 > Antenatal 1st visit by woman <18 years 4 3 4 > Antenatal 1st visits total (calc) 45 42 80 > Antenatal client collecting HIV test results 45 42 84 > > The numbers here, are actual numbers, not counts. And in just a few > clicks, I can easily extract out data for a given orgunit and time > periods. Much simpler than going through the data mart/report > tables.So using this workflow, we can quickly see what data is > available for a given set of orgunits and time periods, and then > quickly drill down to the actual data. We could probably have two > modes. View data and view summary. Viewing the data would provide the > actual data, while viewing the summary could give you the counts of > the data elements, which is not really as useful once you have > constrained two dimensions dimensions (periodid, orgunit) > > Now, this is not going to work for multidimensional datasets, because > there are not unique data elements, but for the Zambia database, which > does not use McDonalds (category combinations, etc) this is exactly > the sort of functionality we have been needed for a long time. A very > quick way to quickly drill down to raw data for a given orgunit/period > combination. The problem with the data browser at the moment, is that > I only get counts at the end of the drill-down. In our case, I only > get ones and zeros. 1s for anything that has been submitted, a 0 for > anything that has not. Getting to the data through the data entry > forms is painful, and it is not possible to see multiple time periods. > > We might should think if this is desired functionality for others, and > how to generalize it to databases with McDonalds implemented. Does not > seem to be too difficult really, but not sure if it is desirable. > > Would be good to hear with others think. > > Hi, just to clarify, is this stuff now aggregating data in time/space or showing only the "raw" data?
Lars
_______________________________________________ Mailing list: https://launchpad.net/~dhis2-devs Post to : [email protected] Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp
