dhis2-devs team mailing list archive

[Bob Jolliffe <bobjolliffe@xxxxxxxxx>]

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.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
>>> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
>>> Unsubscribe : https://launchpad.net/~dhis2-devs
>>> More help   : https://help.launchpad.net/ListHelp
>>
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~dhis2-devs
> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~dhis2-devs
> More help   : https://help.launchpad.net/ListHelp
>
>