dhis2-devs team mailing list archive

[Lars Helge Øverland <larshelge@xxxxxxxxx>]

2010/11/24 Jason Pickering <jason.p.pickering@xxxxxxxxx>

> 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