dhis2-devs team mailing list archive

[Ola Hodne Titlestad <olatitle@xxxxxxxxx>]

Hi Roger,

Lot's of good questions and reminders that our documentation on reports is
not completed.

I don't have a lot of time right now, but will try to give you some pointers
anyway. I'll try to find more time tomorrow to continue, and hopefully
others like Edem and Jason could join in.

See my text inline..

On 29 April 2010 14:07, Friedman, Roger (CDC/OID/NCHHSTP) (CTR) <
rdf4@xxxxxxx> wrote:

>  Listers --
>
>             I am trying to build some reports, and this part of the user
> manual has no text.  Here are some of the questions I have:
>
>             1.         What triggers a roll-up of data through the org
> hierarchy?
>
Aggregation of data takes place either as a stand-alone data mart process
which populates two database tables (aggregateddatavalue and
aggregatedindicatorvalue), or as a built in service to some of the other
tools (report tables and charts, and perhaps more...).

You can read more on how to set up your datamart exports here:
http://dhis-developers-list.1563109.n2.nabble.com/Aggregation-in-the-datamart-and-orgunit-level-specification-tt4627202.html#a4627378

but that part definitely lacks in the manual. A more conceptual introduction
to how DHIS aggregates data up the hierarchy and across time can be found in
section 9.1 of the manual.

Datamart is mainly needed for external reporting tools that are not
integrated into the DHIS2, like Excel pivot tables. The mapping component
also still requires manual datamart exports I think (although that needs to
change).

Report tables (Service->Reports->report Table) are a bit like the datamart,
but more specified reporting data sources with names written out( not only
IDs), with cross tab options (periods, data elements, indicators, or
categories) on columns, and they can be set up with relative periods
(reporting month) and orgunit parameters to be easily reusable with e.g.
the same monthly report template design from BIRT every month.

Report tables can be linked to report design templates in the integrated
BIRT or Jasper reports module (Service->Reports->Standard Report).

I would guess that either BIRT reports or Jasper (designed in iReport)
combined with report tables, and sometimes to your custom sql datasets would
meet many of your reporting needs.


>             2.         How can I get a "most recent" period for reporting
> site survey data?  (I find much about relative periods but no way to
> select/specify)
>
> I am not sure I understand your question, could you clarify a bit more or
use an example?
When you run a predefined report table that has reporting month as a
parameter then the report table gets populated with the month selected by
the user, would that help? A BIRT report design linked to a report table
would then get the same parameter popup before generating the report, if set
up as a standard report in DHIS with reference to the design template and
the report table(s) needed.



>             3.         How can I partially roll up disaggregations (e.g. if
> numerator is disaggregated sex-age and denominator is only disaggregated
> sex; or a data element is disaggregated sex-age and I want to display it by
> year by sex)?
>
The report table supports categories crosstabbed on columns and include
subtotals and totals for those categories. Have a look at the report table
type called Data Element Dimensions Table. When defining indicators you must
add together the various categoryoptioncombos to form your partial roll-ups,
this right now is a bit tedious if you have lots of options combined, and
for 2.0.5 there will be support for picking subtotals and totals for data
elements and categories directly when defining indicator formulas.



>             4.         How can I get disaggregations in a "banked" way
> (e.g. like default input format) for reports?
>
The default input format (data collection form) can be printed with raw or
aggregated data using the dataset report functionality. You could also use
the report tables for data element dimensions (categories).



>             5.         How can I get disaggregations x orgunitgroupset?
>
Right now that is only possible in external tools and using your custom sql,
but will be supported in report tables from 2.0.5. At least to have report
tables aggregated by orgunit groups within one group set, e.g by facility
type in stead of the individual facilities themselves.


>             6.         Can I create org groups/data elements based on
> indicators (e.g. high incidence/low incidence districts)?
>
> Sounds like a great idea, but not supported. We should write up a blueprint
for this.
For now you could set up Excel pivot tables that would give you this kind of
data analysis view to the data.



>             7.         How can I get data on a period per column basis?
> How can I get both period per column and cumulative or year-to-date ?  How
> can I get period per column with corresponding period of previous years?
>
Report tables with the period dimension ticked as one of the cross tab
dimensions. Under relative periods you could select both reporting month and
this_month_last_year, and if periods are crosstabbed and you select
reporting month as a parameter then you would get these two columns in your
report table, and combined with a few selected indicators on columns if you
tick indicators as crosstab, or simply list indicators on the rows of one
indicator column if not set as cross tab.

>             8.         If I have to build SQL for some of these, can I
> store this SQL for later reuse?  Can I trigger the SQL to run during
> roll-ups?
>
Jason has been working on something like this I think. I know that there are
plans for 2.0.5 to build in a few so called pivot views (sql queries) into
the system, but don't know the details, and I'm not sure they will be
completely custom for the 2.0.5 release.


>             9.         What are the mechanics of locking for roll-up and
> report generation?  Do I have to worry about people running reports during
> monthly peak data entry load?
>
I guess you could block the datamart module for some of the users and make
sure a system admin generates the necessary datamarts for all users. For
reports it is possible to run the reports from existing data source, so if
one users has already generated a report for March 2010 then the report
table is already populated and can be reused by other users later. We could
think of ways to block the option to run the report with new data source
(which triggers the aggregation process), but that sounds complicated. For
now what you could do is to train users to use the right button for running
reports.



> I was wondering if somebody would have some time for a phone call or some
> type of internet work sharing to help me with some of this stuff.
>
Not today, but maybe tomorrow. I'm a bit rushed now preparing for my Sierra
Leone trip on Sunday. I will be there all next week.

WE really need to get our acts together and document all of this... sorry.


Ola
---------


> Thanks, Roger
>
>
>
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~dhis2-devs<https://launchpad.net/%7Edhis2-devs>
> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~dhis2-devs<https://launchpad.net/%7Edhis2-devs>
> More help   : https://help.launchpad.net/ListHelp
>
>