dhis2-devs team mailing list archive

[johansa@xxxxxxxxxx]

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.name AS
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;