dhis2-devs team mailing list archive

[Ola Hodne Titlestad <olati@xxxxxxxxxx>]

Hi Johan,

As in DHIS 1.4 pivots the indicator value should not be retrieved directly,
but in stead set up in Excel as a calculated field with teh formula
numxfactor / denominatorvalue.
This is to make Excel aggregation (of percentages) work properly when you
collapse levels.

best regards,
Ola Hodne Titlestad
HISP
University of Oslo


On Fri, Apr 24, 2009 at 1:39 PM, <johansa@xxxxxxxxxx> wrote:

> Thanks Ola.
>
> Also, I see the columns "numxfactor" (being
> aggregatedindicatorvalue.factor * aggregatedindicatorvalue.numeratorvalue)
> and "denominatorvalue" are retrieved, but not the "value" from the
> aggregatedindicatorvalue table. Do you set up the pivot tables based on
> the denominatorvalue and numxfactor, in some behind-the-scenes formula, or
> should the "value" column also be pulled through and dumped in the data
> field of pivot table?
>
> Johan
>
>
>
> > 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>
> <https://launchpad.net/%7Edhis2-devs>
> >> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
> >> Unsubscribe :
> >> https://launchpad.net/~dhis2-devs <https://launchpad.net/%7Edhis2-devs>
> <https://launchpad.net/%7Edhis2-devs>
> >> More help   : https://help.launchpad.net/ListHelp
> >>
> >
>
>
>