dhis2-devs team mailing list archive

[Ola Hodne Titlestad <olati@xxxxxxxxxx>]

Hi,

We have tried to get pivot tables (data pilots) in OpenOffice to work with
DHIS data, but so far our experiences are that it doesn't work out well.

The main issue is performance as the data pilot operations (pivoting,
zooming in/out) become very slow with average amounts of DHIS data.

Setting them up works well and is just like in Excel (when you have first
registered the database).

Personally I have not tested this for at least a year so maybe things have
changed, but at least I haven't heard anything about improvements in this
regard.

Would be great if someone could test data pilot performance again on the
latest OO version and report back to the list.

So far, properly working desktop pivot tables with DHIS data have been
available only to MS Office users.

The web based pivots (accessible from Reports menu in DHIS 2) are very basic
compared to the functionality available in Excel.

best regards,
Ola Hodne Titlestad
HISP
University of Oslo


On Fri, Apr 24, 2009 at 12:46 PM, Bob Jolliffe <bobjolliffe@xxxxxxxxx>wrote:

> And here's a simple tutorial on doing pivot tables (Data Pilot) in
> openoffice calc: http://www.learnopenoffice.org/CalcTutorial33.htm
>
> Regards
> Bob
>
> 2009/4/24 Ola Hodne Titlestad <olati@xxxxxxxxxx>:
> > If you want to play around with these views and make changes without
> having
> > to write the SQL I recommend using OpenOffice query editor which is just
> > like the interface in MS Access.
> > You can register (link to) your postgres database in OpenOffice and
> create
> > queries there.
> >
> >
> > best regards,
> > Ola Hodne Titlestad
> > HISP
> > University of Oslo
> >
> >
> > On Fri, Apr 24, 2009 at 12:34 PM, Ola Hodne Titlestad <olati@xxxxxxxxxx>
> > wrote:
> >>
> >> Hi Johan,
> >>
> >> You're right, there is a bug there. I can see I have fixed it locally,
> but
> >> didn't update the attachment on the wiki, sorry.
> >>
> >> Will upload and replace with my new version now.
> >>
> >> best regards,
> >> Ola Hodne Titlestad
> >> HISP
> >> University of Oslo
> >>
> >>
> >> On Fri, Apr 24, 2009 at 11:53 AM, <johansa@xxxxxxxxxx> wrote:
> >>>
> >>> Hi, I've downloaded and run the SQL creating views found here:
> >>> https://answers.launchpad.net/dhis2/+faq/371
> >>>
> >>> I'm trying to understand the SQL here. Not an expert, but I don't see
> how
> >>> the indicatorgroup is retrieved by the following code, for indicators
> >>> OU2.
> >>>
> >>> I see that indicatorgroupmembers i mentioned in the FROM and WHERE
> >>> statements, but not in the SELECT. What should I add to include the
> >>> groups, which I want to pull through to the pivot tables? If somehow
> the
> >>> indicatorgroups are retrieved by this view, what SQL would I need to
> add
> >>> in the pivottable (using Pivot Play)
> >>>
> >>> Johan
> >>>
> >>>
> >>> CREATE OR REPLACE VIEW pivotsource_indicator_ou2 AS
> >>>
> >>> SELECT indicator.name AS indicator, organisationunit_1.name AS
> orgunit1,
> >>> organisationunit_1.shortname AS ou1, organisationunit_2.name AS
> orgunit2,
> >>> organisationunit_2.shortname AS ou2, period.periodid, periodtype.nameAS
> >>> periodtype, to_char(period.startdate::timestamp with time zone,
> >>> 'YYYY'::text) AS year, to_char(period.startdate::timestamp with time
> >>> zone,
> >>> 'Mon'::text) AS month, (rtrim(to_char(period.startdate::timestamp with
> >>> time zone, 'Mon'::text)) || '-'::text) ||
> >>> to_char(period.startdate::timestamp with time zone, 'YY'::text) AS
> >>> period,
> >>> aggregatedindicatorvalue.factor *
> aggregatedindicatorvalue.numeratorvalue
> >>> AS numxfactor, aggregatedindicatorvalue.denominatorvalue,
> >>> aggregatedindicatorvalue.annualized, aggregatedindicatorvalue.level
> >>>
> >>> FROM indicator indicator, indicatorgroupmembers indicatorgroupmembers,
> >>> indicatorgroup indicatorgroup, organisationunit organisationunit_1,
> >>> organisationunit organisationunit_2, period period, periodtype
> >>> periodtype,
> >>> aggregatedindicatorvalue aggregatedindicatorvalue, orgunitstructure
> >>> orgunitstructure
> >>>
> >>> WHERE indicator.indicatorid = aggregatedindicatorvalue.indicatorid AND
> >>> indicatorgroupmembers.indicatorid = indicator.indicatorid AND
> >>> indicatorgroupmembers.indicatorgroupid =
> indicatorgroup.indicatorgroupid
> >>> AND period.periodid = aggregatedindicatorvalue.periodid AND
> >>> period.periodtypeid = periodtype.periodtypeid AND periodtype.name::text
> =
> >>> 'Monthly'::text AND orgunitstructure.organisationunitid =
> >>> aggregatedindicatorvalue.organisationunitid AND
> orgunitstructure.idlevel1
> >>> = organisationunit_1.organisationunitid AND orgunitstructure.idlevel2 =
> >>> organisationunit_2.organisationunitid AND
> aggregatedindicatorvalue.level
> >>> =
> >>> 2
> >>>
> >>> ORDER BY period.startdate;
> >>>
> >>> ALTER TABLE pivotsource_indicator_ou2 OWNER TO dhis;
> >>>
> >>>
> >>> _______________________________________________
> >>> 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
> >>
> >
> >
> > _______________________________________________
> > 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
> >
> >
>