question regarding view/SQL

Support - Assistance technique
1

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;

2

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@ifi.uio.no 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@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

3

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@ifi.uio.no 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@ifi.uio.no 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@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

4

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@ifi.uio.no>:

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@ifi.uio.no> > 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@ifi.uio.no> 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@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp

_______________________________________________
Mailing list: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp

5

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@gmail.com 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@ifi.uio.no:

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@ifi.uio.no > > > 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@ifi.uio.no 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@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

6

Its very hard for me to test this functionality without a better
understanding of the data :frowning: But I will give it a go.

Currently I have a dummy South African data set from Lars here:
http://folk.uio.no/larshelg/files/dhis2sample.zip

Can anyone suggest a meaningful datamart to set up with this
particular data to test with?

Relating to a previous discussion on testing, is there a standard
dummy dataset which we can be sure everyone has access to and which is
reasonably representative of useful data? Is this it?

Regards
Bob

···

2009/4/24 Ola Hodne Titlestad <olati@ifi.uio.no>:

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@gmail.com> > 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@ifi.uio.no>:
> 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@ifi.uio.no> >> > 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@ifi.uio.no> 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@lists.launchpad.net
>>> Unsubscribe : https://launchpad.net/~dhis2-devs
>>> More help : https://help.launchpad.net/ListHelp
>>
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~dhis2-devs
> Post to : dhis2-devs@lists.launchpad.net
> Unsubscribe : https://launchpad.net/~dhis2-devs
> More help : https://help.launchpad.net/ListHelp
>
>

7

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@ifi.uio.no> 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&lt;https://launchpad.net/~dhis2-devs&gt;
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe :
https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
More help : https://help.launchpad.net/ListHelp

8

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@ifi.uio.no 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@ifi.uio.no 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@lists.launchpad.net

Unsubscribe :

https://launchpad.net/~dhis2-devs<https://launchpad.net/%7Edhis2-devs>

More help : https://help.launchpad.net/ListHelp

9

Hi Bob,

I think maybe Johan can give some more input and possibly also help you to test this as he as lots of experience from using Excel pivots with DHIS.

Some comments and backgrouns on the use of pivots with DHIS data:

Normally a pivot table contains all data (data elements or indicators) for all months for a certain orgunit level.
The reason for splitting them up based on orgunit level is to get much faster access to data at higher levels using the aggregated data as the data source base level for the pivot in stead of the lowest level (values in the datavalue table).

With large amounts of data over many years it also makes sense to split the data into pivot tables according to calendar or financial years.

Data element and indicator groups are often then used as filters within a pivot table to organise the data. With very large datasets you could also split up pivots based on data elements/indicator groups with one group per table, which you can set up in a view in the DHIS2 database.

So based on this a datamart export for the SA sample db could be all data elements + all indicators + all districts + all district municipalities (OU level 4).

This would then require 4 pivot tables:

  • routine data (data elements) for OU3

  • indicator data for OU3

  • routine data for OU4

  • indicator data for OU4

All these values will be put in the two datamart tables called aggregateddatavalue and aggregatedindicatorvalue.

Each of these pivot tables will have their corresponding view (with descriptive self-explanatory names) in the file Johan mentioned:

http://208.76.222.114/confluence/download/attachments/8096/PivotSourceViewsOU2-5.sql

The views have been set up on the DHIS 2 side to make the pivot table design easier. The views (queries) in the DHIS database combine the datamart tables (either data values or indicator values) with the orgunit hierarchy (orgunitstructure table) and replace id with readable names as identifiers. Groups and groupsets can also be joined in and filters on years or groups created in where clauses.

In Excel the data source for the pivot table will then simply be an select * from pivot_view_XX which simplifies things a lot for the implementers or superusers defining their own pivot tables. Of course alos pivot table designs can be reused across contexts by manually editing the data connections in excel to replace database names, server URLs, and login info.

A typical setup we have applied in both Sierra Leone and Tanzania recently is a district office with 1 DHIS server (preferrably running on linux) and multiple Windows+Office workstations in a local network. Each windows machine can then set up an odbc connection to the database running on the server and then use Excel pivot tables that retrieves data directly from the server. Excel keeps all its data in memory (in the excel file) and only requries server access when pivots are refreshed (typically once a month) so this works out quite well.

best regards,
Ola Hodne Titlestad
HISP
University of Oslo

···

On Fri, Apr 24, 2009 at 1:13 PM, Bob Jolliffe bobjolliffe@gmail.com wrote:

Its very hard for me to test this functionality without a better

understanding of the data :frowning: But I will give it a go.

Currently I have a dummy South African data set from Lars here:

http://folk.uio.no/larshelg/files/dhis2sample.zip

Can anyone suggest a meaningful datamart to set up with this

particular data to test with?

Relating to a previous discussion on testing, is there a standard

dummy dataset which we can be sure everyone has access to and which is

reasonably representative of useful data? Is this it?

Regards

Bob

2009/4/24 Ola Hodne Titlestad olati@ifi.uio.no:

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@gmail.com > > > 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@ifi.uio.no:

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@ifi.uio.no > > >> > 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@ifi.uio.no 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@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

10

Hi Bob,

I think maybe Johan can give some more input and possibly also help you to test this as he as lots of experience from using Excel pivots with DHIS.

Some comments and backgrouns on the use of pivots with DHIS data:

Normally a pivot table contains all data (data elements or indicators) for all months for a certain orgunit level.
The reason for splitting them up based on orgunit level is to get much faster access to data at higher levels using the aggregated data as the data source base level for the pivot in stead of the lowest level (values in the datavalue table).

With large amounts of data over many years it also makes sense to split the data into pivot tables according to calendar or financial years.

Data element and indicator groups are often then used as filters within a pivot table to organise the data. With very large datasets you could also split up pivots based on data elements/indicator groups with one group per table, which you can set up in a view in the DHIS2 database.

So based on this a datamart export for the SA sample db could be all data elements + all indicators + all districts + all district municipalities (OU level 4).

Small correction: All district municipalities (OU4) + all facilities (OU5) is probably better as there is only one orgunit at OU3 level.

The sample database has been pruned down (by Calle, the DHIS 2 version is a copy of the 1.4) to include only one District municipality so it might be to small for proper testing.
But with data for all periods + all facilities you can still get a reasonable amount of values I think. I’m on a very slow connection in Zanzibar right now so I can’t check on the demo, but if you go to data administration and data browser you can quickly find out for which periods you have good data coverage. I think its at least two years of data there.

···

On Fri, Apr 24, 2009 at 1:59 PM, Ola Hodne Titlestad olati@ifi.uio.no wrote:

This would then require 4 pivot tables:

  • routine data (data elements) for OU3

  • indicator data for OU3

  • routine data for OU4

  • indicator data for OU4

All these values will be put in the two datamart tables called aggregateddatavalue and aggregatedindicatorvalue.

Each of these pivot tables will have their corresponding view (with descriptive self-explanatory names) in the file Johan mentioned:

http://208.76.222.114/confluence/download/attachments/8096/PivotSourceViewsOU2-5.sql

The views have been set up on the DHIS 2 side to make the pivot table design easier. The views (queries) in the DHIS database combine the datamart tables (either data values or indicator values) with the orgunit hierarchy (orgunitstructure table) and replace id with readable names as identifiers. Groups and groupsets can also be joined in and filters on years or groups created in where clauses.

In Excel the data source for the pivot table will then simply be an select * from pivot_view_XX which simplifies things a lot for the implementers or superusers defining their own pivot tables. Of course alos pivot table designs can be reused across contexts by manually editing the data connections in excel to replace database names, server URLs, and login info.

A typical setup we have applied in both Sierra Leone and Tanzania recently is a district office with 1 DHIS server (preferrably running on linux) and multiple Windows+Office workstations in a local network. Each windows machine can then set up an odbc connection to the database running on the server and then use Excel pivot tables that retrieves data directly from the server. Excel keeps all its data in memory (in the excel file) and only requries server access when pivots are refreshed (typically once a month) so this works out quite well.

best regards,
Ola Hodne Titlestad
HISP
University of Oslo

On Fri, Apr 24, 2009 at 1:13 PM, Bob Jolliffe bobjolliffe@gmail.com wrote:

Its very hard for me to test this functionality without a better

understanding of the data :frowning: But I will give it a go.

Currently I have a dummy South African data set from Lars here:

http://folk.uio.no/larshelg/files/dhis2sample.zip

Can anyone suggest a meaningful datamart to set up with this

particular data to test with?

Relating to a previous discussion on testing, is there a standard

dummy dataset which we can be sure everyone has access to and which is

reasonably representative of useful data? Is this it?

Regards

Bob

2009/4/24 Ola Hodne Titlestad olati@ifi.uio.no:

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@gmail.com > > > > > 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@ifi.uio.no:

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@ifi.uio.no > > > > >> > 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@ifi.uio.no 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@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

11

Hi Bob,

I think maybe Johan can give some more input and possibly also help you to test this as he as lots of experience from using Excel pivots with DHIS.

Some comments and backgrouns on the use of pivots with DHIS data:

Normally a pivot table contains all data (data elements or indicators) for all months for a certain orgunit level.
The reason for splitting them up based on orgunit level is to get much faster access to data at higher levels using the aggregated data as the data source base level for the pivot in stead of the lowest level (values in the datavalue table).

With large amounts of data over many years it also makes sense to split the data into pivot tables according to calendar or financial years.

Data element and indicator groups are often then used as filters within a pivot table to organise the data. With very large datasets you could also split up pivots based on data elements/indicator groups with one group per table, which you can set up in a view in the DHIS2 database.

So based on this a datamart export for the SA sample db could be all data elements + all indicators + all districts + all district municipalities (OU level 4).

Small correction: All district municipalities (OU4) + all facilities (OU5) is probably better as there is only one orgunit at OU3 level.

The sample database has been pruned down (by Calle, the DHIS 2 version is a copy of the 1.4) to include only one District municipality so it might be to small for proper testing.
But with data for all periods + all facilities you can still get a reasonable amount of values I think. I’m on a very slow connection in Zanzibar right now so I can’t check on the demo, but if you go to data administration and data browser you can quickly find out for which periods you have good data coverage. I think its at least two years of data there.

This would then require 4 pivot tables:

  • routine data (data elements) for OU3

  • indicator data for OU3

  • routine data for OU4

  • indicator data for OU4

sorry, and these will then be for OU4 and OU5 based on my change to use these levels in stead.

···

On Fri, Apr 24, 2009 at 2:09 PM, Ola Hodne Titlestad olati@ifi.uio.no wrote:

On Fri, Apr 24, 2009 at 1:59 PM, Ola Hodne Titlestad olati@ifi.uio.no wrote:

All these values will be put in the two datamart tables called aggregateddatavalue and aggregatedindicatorvalue.

Each of these pivot tables will have their corresponding view (with descriptive self-explanatory names) in the file Johan mentioned:

http://208.76.222.114/confluence/download/attachments/8096/PivotSourceViewsOU2-5.sql

The views have been set up on the DHIS 2 side to make the pivot table design easier. The views (queries) in the DHIS database combine the datamart tables (either data values or indicator values) with the orgunit hierarchy (orgunitstructure table) and replace id with readable names as identifiers. Groups and groupsets can also be joined in and filters on years or groups created in where clauses.

In Excel the data source for the pivot table will then simply be an select * from pivot_view_XX which simplifies things a lot for the implementers or superusers defining their own pivot tables. Of course alos pivot table designs can be reused across contexts by manually editing the data connections in excel to replace database names, server URLs, and login info.

A typical setup we have applied in both Sierra Leone and Tanzania recently is a district office with 1 DHIS server (preferrably running on linux) and multiple Windows+Office workstations in a local network. Each windows machine can then set up an odbc connection to the database running on the server and then use Excel pivot tables that retrieves data directly from the server. Excel keeps all its data in memory (in the excel file) and only requries server access when pivots are refreshed (typically once a month) so this works out quite well.

best regards,
Ola Hodne Titlestad
HISP
University of Oslo

On Fri, Apr 24, 2009 at 1:13 PM, Bob Jolliffe bobjolliffe@gmail.com wrote:

Its very hard for me to test this functionality without a better

understanding of the data :frowning: But I will give it a go.

Currently I have a dummy South African data set from Lars here:

http://folk.uio.no/larshelg/files/dhis2sample.zip

Can anyone suggest a meaningful datamart to set up with this

particular data to test with?

Relating to a previous discussion on testing, is there a standard

dummy dataset which we can be sure everyone has access to and which is

reasonably representative of useful data? Is this it?

Regards

Bob

2009/4/24 Ola Hodne Titlestad olati@ifi.uio.no:

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@gmail.com > > > > > > > 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@ifi.uio.no:

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@ifi.uio.no > > > > > > >> > 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@ifi.uio.no 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@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

Mailing list: https://launchpad.net/~dhis2-devs

Post to : dhis2-devs@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-devs

More help : https://help.launchpad.net/ListHelp

12

Bob,

Ola gave a good description here. I'm working on a very detailed guide to
(MS Excel) pivot table creation, expanding the one Ola has put on
launchpad. Hopefully it will be ready in a few days, depending on how much
time I get to spend on it.

Calle, currently here in Geneva, says that OO was tested a while ago with
a 15-20 MB pivot from South Africa (that's small for them, regular pivot
size will be up to 200 MB). It did not work very well (did not start up in
15 minutes). Smaller pivots might work, but it seems like OO doesnt handle
load very well (and splitting the data up into 20 files takes away the
whole point of pivot tables)

Johan

···

Hi Bob,

I think maybe Johan can give some more input and possibly also help you to
test this as he as lots of experience from using Excel pivots with DHIS.

Some comments and backgrouns on the use of pivots with DHIS data:

Normally a pivot table contains all data (data elements or indicators) for
all months for a certain orgunit level.
The reason for splitting them up based on orgunit level is to get much
faster access to data at higher levels using the aggregated data as the
data
source base level for the pivot in stead of the lowest level (values in
the
datavalue table).

With large amounts of data over many years it also makes sense to split
the
data into pivot tables according to calendar or financial years.

Data element and indicator groups are often then used as filters within a
pivot table to organise the data. With very large datasets you could also
split up pivots based on data elements/indicator groups with one group per
table, which you can set up in a view in the DHIS2 database.

So based on this a datamart export for the SA sample db could be all data
elements + all indicators + all districts + all district municipalities
(OU
level 4).

This would then require 4 pivot tables:
- routine data (data elements) for OU3
- indicator data for OU3
- routine data for OU4
- indicator data for OU4

All these values will be put in the two datamart tables called
aggregateddatavalue and aggregatedindicatorvalue.

Each of these pivot tables will have their corresponding view (with
descriptive self-explanatory names) in the file Johan mentioned:
http://208.76.222.114/confluence/download/attachments/8096/PivotSourceViewsOU2-5.sql

The views have been set up on the DHIS 2 side to make the pivot table
design
easier. The views (queries) in the DHIS database combine the datamart
tables
(either data values or indicator values) with the orgunit hierarchy
(orgunitstructure table) and replace id with readable names as
identifiers.
Groups and groupsets can also be joined in and filters on years or groups
created in where clauses.

In Excel the data source for the pivot table will then simply be an select
*
from pivot_view_XX which simplifies things a lot for the implementers or
superusers defining their own pivot tables. Of course alos pivot table
designs can be reused across contexts by manually editing the data
connections in excel to replace database names, server URLs, and login
info.

A typical setup we have applied in both Sierra Leone and Tanzania recently
is a district office with 1 DHIS server (preferrably running on linux) and
multiple Windows+Office workstations in a local network. Each windows
machine can then set up an odbc connection to the database running on the
server and then use Excel pivot tables that retrieves data directly from
the
server. Excel keeps all its data in memory (in the excel file) and only
requries server access when pivots are refreshed (typically once a month)
so
this works out quite well.

best regards,
Ola Hodne Titlestad
HISP
University of Oslo

On Fri, Apr 24, 2009 at 1:13 PM, Bob Jolliffe <bobjolliffe@gmail.com> > wrote:

Its very hard for me to test this functionality without a better
understanding of the data :frowning: But I will give it a go.

Currently I have a dummy South African data set from Lars here:
http://folk.uio.no/larshelg/files/dhis2sample.zip

Can anyone suggest a meaningful datamart to set up with this
particular data to test with?

Relating to a previous discussion on testing, is there a standard
dummy dataset which we can be sure everyone has access to and which is
reasonably representative of useful data? Is this it?

Regards
Bob

2009/4/24 Ola Hodne Titlestad <olati@ifi.uio.no>:
> 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@gmail.com> >> > 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@ifi.uio.no>:
>> > 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@ifi.uio.no> >> >> > 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@ifi.uio.no> 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&lt;https://launchpad.net/~dhis2-devs&gt;
>> >>> Post to : dhis2-devs@lists.launchpad.net
>> >>> Unsubscribe :
https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> >>> More help : https://help.launchpad.net/ListHelp
>> >>
>> >
>> >
>> > _______________________________________________
>> > Mailing list:
https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> > Post to : dhis2-devs@lists.launchpad.net
>> > Unsubscribe :
https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> > More help : https://help.launchpad.net/ListHelp
>> >
>> >
>
>

13

Bob,

Ola gave a good description here. I'm working on a very detailed guide to
(MS Excel) pivot table creation, expanding the one Ola has put on
launchpad. Hopefully it will be ready in a few days, depending on how much
time I get to spend on it.

Great. Look forward to looking at it.

Calle, currently here in Geneva, says that OO was tested a while ago with
a 15-20 MB pivot from South Africa (that's small for them, regular pivot
size will be up to 200 MB). It did not work very well (did not start up in
15 minutes). Smaller pivots might work, but it seems like OO doesnt handle
load very well (and splitting the data up into 20 files takes away the
whole point of pivot tables)

Yes this seems to be a known performance problem with OOO calc.
Partly the xml file format and partly other issues. Its not really
very optimal for large data statistical work. gnumeric is apparently
much better but doesn't yet support pivot tables :slight_smile:

Regards
Bob

···

2009/4/24 <johansa@ifi.uio.no>:

Johan

Hi Bob,

I think maybe Johan can give some more input and possibly also help you to
test this as he as lots of experience from using Excel pivots with DHIS.

Some comments and backgrouns on the use of pivots with DHIS data:

Normally a pivot table contains all data (data elements or indicators) for
all months for a certain orgunit level.
The reason for splitting them up based on orgunit level is to get much
faster access to data at higher levels using the aggregated data as the
data
source base level for the pivot in stead of the lowest level (values in
the
datavalue table).

With large amounts of data over many years it also makes sense to split
the
data into pivot tables according to calendar or financial years.

Data element and indicator groups are often then used as filters within a
pivot table to organise the data. With very large datasets you could also
split up pivots based on data elements/indicator groups with one group per
table, which you can set up in a view in the DHIS2 database.

So based on this a datamart export for the SA sample db could be all data
elements + all indicators + all districts + all district municipalities
(OU
level 4).

This would then require 4 pivot tables:
- routine data (data elements) for OU3
- indicator data for OU3
- routine data for OU4
- indicator data for OU4

All these values will be put in the two datamart tables called
aggregateddatavalue and aggregatedindicatorvalue.

Each of these pivot tables will have their corresponding view (with
descriptive self-explanatory names) in the file Johan mentioned:
http://208.76.222.114/confluence/download/attachments/8096/PivotSourceViewsOU2-5.sql

The views have been set up on the DHIS 2 side to make the pivot table
design
easier. The views (queries) in the DHIS database combine the datamart
tables
(either data values or indicator values) with the orgunit hierarchy
(orgunitstructure table) and replace id with readable names as
identifiers.
Groups and groupsets can also be joined in and filters on years or groups
created in where clauses.

In Excel the data source for the pivot table will then simply be an select
*
from pivot_view_XX which simplifies things a lot for the implementers or
superusers defining their own pivot tables. Of course alos pivot table
designs can be reused across contexts by manually editing the data
connections in excel to replace database names, server URLs, and login
info.

A typical setup we have applied in both Sierra Leone and Tanzania recently
is a district office with 1 DHIS server (preferrably running on linux) and
multiple Windows+Office workstations in a local network. Each windows
machine can then set up an odbc connection to the database running on the
server and then use Excel pivot tables that retrieves data directly from
the
server. Excel keeps all its data in memory (in the excel file) and only
requries server access when pivots are refreshed (typically once a month)
so
this works out quite well.

best regards,
Ola Hodne Titlestad
HISP
University of Oslo

On Fri, Apr 24, 2009 at 1:13 PM, Bob Jolliffe <bobjolliffe@gmail.com> >> wrote:

Its very hard for me to test this functionality without a better
understanding of the data :frowning: But I will give it a go.

Currently I have a dummy South African data set from Lars here:
http://folk.uio.no/larshelg/files/dhis2sample.zip

Can anyone suggest a meaningful datamart to set up with this
particular data to test with?

Relating to a previous discussion on testing, is there a standard
dummy dataset which we can be sure everyone has access to and which is
reasonably representative of useful data? Is this it?

Regards
Bob

2009/4/24 Ola Hodne Titlestad <olati@ifi.uio.no>:
> 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@gmail.com> >>> > 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@ifi.uio.no>:
>> > 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@ifi.uio.no> >>> >> > 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@ifi.uio.no> 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&lt;https://launchpad.net/~dhis2-devs&gt;
>> >>> Post to : dhis2-devs@lists.launchpad.net
>> >>> Unsubscribe :
https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> >>> More help : https://help.launchpad.net/ListHelp
>> >>
>> >
>> >
>> > _______________________________________________
>> > Mailing list:
https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> > Post to : dhis2-devs@lists.launchpad.net
>> > Unsubscribe :
https://launchpad.net/~dhis2-devs&lt;https://launchpad.net/~dhis2-devs&gt;
>> > More help : https://help.launchpad.net/ListHelp
>> >
>> >
>
>