dhis2-devs team mailing list archive

[Jason Pickering <jason.p.pickering@xxxxxxxxx>]

Indeed, I have essentially the same issue in Zambia, and I was only
able to accomplish this through a custom view with some stored
procedures.  (See the email thread from a few weeks ago
http://groups.google.com/group/dhis-users/t/1896d5a6e44d3a00) There is
a rather long thread , along with some SQL code that I used to
construct a report. I assume it could be modified your purposes.

The other possibility would be to use a custom SELECT statement in a
reporting query (through BIRT), using LEFT OUTER JOINS to return NULL
values, but coercing them to zeros instead of NULLS in the report. Of
course there would be an obvious performance penalty if you are using
a view, but you could always materialize the view into a table,
following essentially the same procedure as a data mart export does
with Java, but relying on the database to create a reporting table. Of
course the downside of this is that there is no way in the DHIS 2
interface to  refresh such a table (AFAIK). There might be better
workarounds like a scheduled pgagent task to do this job for you on a
regular basis if performance is an issue.

The queries that I describe in that thread execute fairly quickly
though, so it might be best to start with a view and then see if
materialization would be required. Of course, doing this through the
UI would probably be a better solution. Can we setup a DHIS2 hackers
group? :)

Regards,
JPP





On Tue, May 19, 2009 at 10:03 PM, Ola Hodne Titlestad <olati@xxxxxxxxxx> wrote:
> My feeling is that if there are no values registered (in datavalue table)
> then there should be none in data mart either, simply leave it blank/NULL.
> This '0' in datamart seems misleading when we know there are no values
> entered. I think Jason requested the same (in another mail on this list a
> few weeks back) based on his experience in Zambia.
>
> best regards,
> Ola Hodne Titlestad
> HISP
> University of Oslo
>
>
> On Tue, May 19, 2009 at 5:29 PM, <johansa@xxxxxxxxxx> wrote:
>>
>> Another issue from Sierra Leone:
>>
>> When looking at indicators in pivot tables, even those orgunit/period
>> combinations with missing numerator is displayed (as 0%). The problem is
>> that these disturb the totals, as we at the current stage of
>> incompleteness of data submission would like to estimate the values based
>> on the data we actually have, not counting those months missing. Let me
>> illustrate with an example:
>>
>> For four months, the raw data for a facility would be
>> 4, ,5,5
>>
>> So one month missing. The output numxfactor from the data mart export for
>> this percentage indicator would be 400, 0, 500, 500, though the missing
>> month is most probably NOT zero. We would like it to be excluded from the
>> data mart export (as is done with raw data export), since unticking
>> orgunits month by month in the pivot table is a rather long and tedious
>> process.
>>
>> I think this is done in the data mart export where the numerator is
>> factored with the indicator type factor, always returning an integer.
>> Something like "null"x100=0, while in this case it makes sense to return a
>> "null" (which leads to not exporting the denominator too). However, this
>> must be an option, since many countries do not enter zeros but leave those
>> fields blank. Any thoughts on this?
>>
>> Johan
>>
>>
>> _______________________________________________
>> Mailing list: https://launchpad.net/~dhis2-devs
>> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~dhis2-devs
>> More help   : https://help.launchpad.net/ListHelp
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~dhis2-devs
> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~dhis2-devs
> More help   : https://help.launchpad.net/ListHelp
>
>