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.
Regards,
Jason
···
--
Jason P. Pickering
email: jason.p.pickering@gmail.com
tel:+260968395190