dhis2-devs team mailing list archive

[johansa@xxxxxxxxxx]

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.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;
>>
>>
>> _______________________________________________
>> 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
>>
>