Datamart

Gentle People,

I'd like to have Elements and indicators in one excel pivot table. How do I do that? The available views I have have either Pivot_Indicator or Pivot_Routinedata.

Kabango Malewezi

SSDI Services

2nd Floor, Pamodzi House, City Center, P.O Box 1091, Lilongwe, Malawi
Tel: +265 (01) 776 412/3/4 | fax: +265 (01) 776 410 | Mobile: +265 (0) 88 4 245 431

kmalewezi@jhpiego.net <mailto:kmalewezi@jhpiego.net > | www.jhpiego.org<http://www.jhpiego.org/>

Skype: kabango.malewezi | Twitter: kabangoM

Hi Kabango,

That is not possible.

You can do it with report tables and standard reports.

Ola

···

On Jul 20, 2012 10:49 AM, “Kabango Malewezi” kmalewezi@jhpiego.net wrote:

Gentle People,

I’d like to have Elements and indicators in one excel pivot table. How do I do that? The available views I have have either Pivot_Indicator or Pivot_Routinedata.

Kabango Malewezi

SSDI Services

2nd Floor, Pamodzi House, City Center, P.O Box 1091, Lilongwe, Malawi

Tel: +265 (01) 776 412/3/4 | fax: +265 (01) 776 410 | Mobile: +265 (0) 88 4 245 431

kmalewezi@jhpiego.net <mailto:kmalewezi@jhpiego.net > | www.jhpiego.org<http://www.jhpiego.org/>

Skype: kabango.malewezi | Twitter: kabangoM


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

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

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

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

Thank you very much.

Kabango Malewezi | Database Specialist

SSDI Services

2nd Floor, Pamodzi House, City Center, P.O Box 1091, Lilongwe, Malawi
Tel: +265 (01) 776 412/3/4 | fax: +265 (01) 776 410 | Mobile: +265 (0) 88 4 245 431

kmalewezi@jhpiego.net <mailto:kmalewezi@jhpiego.net > | www.jhpiego.org<http://www.jhpiego.org/>

Skype: kabango.malewezi | Twitter: kabangoM

···

________________________________
From: olatitle@gmail.com [olatitle@gmail.com] on behalf of Ola Hodne Titlestad [olati@ifi.uio.no]
Sent: Friday, July 20, 2012 1:03 PM
To: Kabango Malewezi
Cc: dhis2-users@lists.launchpad.net
Subject: Re: [Dhis2-users] Datamart

Hi Kabango,

That is not possible.

You can do it with report tables and standard reports.

Ola
-------

On Jul 20, 2012 10:49 AM, "Kabango Malewezi" <kmalewezi@jhpiego.net<mailto:kmalewezi@jhpiego.net>> wrote:
Gentle People,

I'd like to have Elements and indicators in one excel pivot table. How do I do that? The available views I have have either Pivot_Indicator or Pivot_Routinedata.

Kabango Malewezi

SSDI Services

2nd Floor, Pamodzi House, City Center, P.O Box 1091, Lilongwe, Malawi
Tel: +265 (01) 776 412/3/4 | fax: +265 (01) 776 410<tel:%2B265%20%2801%29%20776%20410> | Mobile: +265 (0) 88 4 245 431<tel:%2B265%20%280%29%2088%204%20245%20431>

kmalewezi@jhpiego.net<mailto:kmalewezi@jhpiego.net> <mailto:kmalewezi@jhpiego.net<mailto:kmalewezi@jhpiego.net> > | www.jhpiego.org<http://www.jhpiego.org><http://www.jhpiego.org/>

Skype: kabango.malewezi | Twitter: kabangoM

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

It is possible actually using custom SQL. We have developed such views for MyDatamart in Zambia. The general approach we used is to use UNION statements to bring data elements and indicators together into a single view. If you need more specifics, let me know.

···

Sent from my mobile

On Jul 20, 2012 7:04 AM, “Ola Hodne Titlestad” olati@ifi.uio.no wrote:

Hi Kabango,

That is not possible.

You can do it with report tables and standard reports.

Ola


On Jul 20, 2012 10:49 AM, “Kabango Malewezi” kmalewezi@jhpiego.net wrote:

Gentle People,

I’d like to have Elements and indicators in one excel pivot table. How do I do that? The available views I have have either Pivot_Indicator or Pivot_Routinedata.

Kabango Malewezi

SSDI Services

2nd Floor, Pamodzi House, City Center, P.O Box 1091, Lilongwe, Malawi

Tel: +265 (01) 776 412/3/4 | fax: +265 (01) 776 410 | Mobile: +265 (0) 88 4 245 431

kmalewezi@jhpiego.net <mailto:kmalewezi@jhpiego.net > | www.jhpiego.org<http://www.jhpiego.org/>

Skype: kabango.malewezi | Twitter: kabangoM


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

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

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

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


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

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

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

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

Thank u so much. I'll come back to u.

···

Sent from my BlackBerry® smartphone
________________________________
From: Jason Pickering <jason.p.pickering@gmail.com>
Date: Fri, 20 Jul 2012 12:33:55 -0400
To: Ola Hodne Titlestad<olati@ifi.uio.no>
Cc: dhis2-users@lists.launchpad.net<dhis2-users@lists.launchpad.net>; Kabango Malewezi<kmalewezi@jhpiego.net>
Subject: Re: [Dhis2-users] Datamart

It is possible actually using custom SQL. We have developed such views for MyDatamart in Zambia. The general approach we used is to use UNION statements to bring data elements and indicators together into a single view. If you need more specifics, let me know.

--
Sent from my mobile

On Jul 20, 2012 7:04 AM, "Ola Hodne Titlestad" <olati@ifi.uio.no<mailto:olati@ifi.uio.no>> wrote:

Hi Kabango,

That is not possible.

You can do it with report tables and standard reports.

Ola
-------

On Jul 20, 2012 10:49 AM, "Kabango Malewezi" <kmalewezi@jhpiego.net<mailto:kmalewezi@jhpiego.net>> wrote:
Gentle People,

I'd like to have Elements and indicators in one excel pivot table. How do I do that? The available views I have have either Pivot_Indicator or Pivot_Routinedata.

Kabango Malewezi

SSDI Services

2nd Floor, Pamodzi House, City Center, P.O Box 1091, Lilongwe, Malawi
Tel: +265 (01) 776 412/3/4 | fax: +265 (01) 776 410<tel:%2B265%20%2801%29%20776%20410> | Mobile: +265 (0) 88 4 245 431<tel:%2B265%20%280%29%2088%204%20245%20431>

kmalewezi@jhpiego.net<mailto:kmalewezi@jhpiego.net> <mailto:kmalewezi@jhpiego.net<mailto:kmalewezi@jhpiego.net> > | www.jhpiego.org<http://www.jhpiego.org><http://www.jhpiego.org/>

Skype: kabango.malewezi | Twitter: kabangoM

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

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

Jason,

How do you handle the values in this view? Indicator values should be set up as a calculated field to let excel aggregate numerators and denominators separately for percentage values. If not the values for higher levels will be wrong.

Or are you using only one orgunit level in this view?

I guess it is possible to use two separate value fields for data elements and indicators in the same pivot table?

Sounds confusing, but for a very specific/static table maybe… but then I would recommend standard reports.

Ola

···

On Jul 20, 2012 7:24 PM, “Kabango Malewezi” kmalewezi@jhpiego.net wrote:

Hi Kabango,

That is not possible.

You can do it with report tables and standard reports.

Ola


On Jul 20, 2012 10:49 AM, “Kabango Malewezi” kmalewezi@jhpiego.net wrote:

Gentle People,

I’d like to have Elements and indicators in one excel pivot table. How do I do that? The available views I have have either Pivot_Indicator or Pivot_Routinedata.

Kabango Malewezi

SSDI Services

2nd Floor, Pamodzi House, City Center, P.O Box 1091, Lilongwe, Malawi

Tel: +265 (01) 776 412/3/4 | fax: +265 (01) 776 410 | Mobile: +265 (0) 88 4 245 431

kmalewezi@jhpiego.net <mailto:kmalewezi@jhpiego.net > | www.jhpiego.org<http://www.jhpiego.org/>

Skype: kabango.malewezi | Twitter: kabangoM


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

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

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

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

I guess another workaround might be to create simple naive indicators
(ie denominator=1) but this could easily lead to indicator
proliferation.

···

On 20 July 2012 18:39, Ola Hodne Titlestad <olati@ifi.uio.no> wrote:

Jason,
How do you handle the values in this view? Indicator values should be set up
as a calculated field to let excel aggregate numerators and denominators
separately for percentage values. If not the values for higher levels will
be wrong.

Or are you using only one orgunit level in this view?

I guess it is possible to use two separate value fields for data elements
and indicators in the same pivot table?
Sounds confusing, but for a very specific/static table maybe.... but then I
would recommend standard reports.

Ola
-------

On Jul 20, 2012 7:24 PM, "Kabango Malewezi" <kmalewezi@jhpiego.net> wrote:

Hi Kabango,

That is not possible.

You can do it with report tables and standard reports.

Ola
-------

On Jul 20, 2012 10:49 AM, "Kabango Malewezi" <kmalewezi@jhpiego.net> >> wrote:

Gentle People,

I'd like to have Elements and indicators in one excel pivot table. How do
I do that? The available views I have have either Pivot_Indicator or
Pivot_Routinedata.

Kabango Malewezi

SSDI Services

2nd Floor, Pamodzi House, City Center, P.O Box 1091, Lilongwe, Malawi
Tel: +265 (01) 776 412/3/4 | fax: +265 (01) 776 410 | Mobile: +265 (0) 88
4 245 431

kmalewezi@jhpiego.net <mailto:kmalewezi@jhpiego.net > |
www.jhpiego.org<http://www.jhpiego.org/>

Skype: kabango.malewezi | Twitter: kabangoM

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

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

I would need to provide code to make it make sense,but indicators of number type and data elements are handled in one way,indicators with denominators in another manner. I am travelling at the moment but can send some details next week.

Regards,

Jason

···

Sent from my mobile

On Jul 20, 2012 1:39 PM, “Ola Hodne Titlestad” olati@ifi.uio.no wrote:

Jason,

How do you handle the values in this view? Indicator values should be set up as a calculated field to let excel aggregate numerators and denominators separately for percentage values. If not the values for higher levels will be wrong.

Or are you using only one orgunit level in this view?

I guess it is possible to use two separate value fields for data elements and indicators in the same pivot table?

Sounds confusing, but for a very specific/static table maybe… but then I would recommend standard reports.

Ola


On Jul 20, 2012 7:24 PM, “Kabango Malewezi” kmalewezi@jhpiego.net wrote:

Hi Kabango,

That is not possible.

You can do it with report tables and standard reports.

Ola


On Jul 20, 2012 10:49 AM, “Kabango Malewezi” kmalewezi@jhpiego.net wrote:

Gentle People,

I’d like to have Elements and indicators in one excel pivot table. How do I do that? The available views I have have either Pivot_Indicator or Pivot_Routinedata.

Kabango Malewezi

SSDI Services

2nd Floor, Pamodzi House, City Center, P.O Box 1091, Lilongwe, Malawi

Tel: +265 (01) 776 412/3/4 | fax: +265 (01) 776 410 | Mobile: +265 (0) 88 4 245 431

kmalewezi@jhpiego.net <mailto:kmalewezi@jhpiego.net > | www.jhpiego.org<http://www.jhpiego.org/>

Skype: kabango.malewezi | Twitter: kabangoM


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

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

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

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

This is essentially what I have done with the view. The final trick is to adjust the Excel formula to aggregate indicators in one way and calculated data elements (also indicators) and regular data elements in another way. With this dynamic definition of the Excel formula,one can aggregate both indicators and data elements in the same pivot normally. More details (code) tomorrow.

···

Sent from my mobile

On Jul 20, 2012 1:43 PM, “Bob Jolliffe” bobjolliffe@gmail.com wrote:

I guess another workaround might be to create simple naive indicators

(ie denominator=1) but this could easily lead to indicator

proliferation.

On 20 July 2012 18:39, Ola Hodne Titlestad olati@ifi.uio.no wrote:

Jason,

How do you handle the values in this view? Indicator values should be set up

as a calculated field to let excel aggregate numerators and denominators

separately for percentage values. If not the values for higher levels will

be wrong.

Or are you using only one orgunit level in this view?

I guess it is possible to use two separate value fields for data elements

and indicators in the same pivot table?

Sounds confusing, but for a very specific/static table maybe… but then I

would recommend standard reports.

Ola


On Jul 20, 2012 7:24 PM, “Kabango Malewezi” kmalewezi@jhpiego.net wrote:

Hi Kabango,

That is not possible.

You can do it with report tables and standard reports.

Ola


On Jul 20, 2012 10:49 AM, “Kabango Malewezi” kmalewezi@jhpiego.net > > >> wrote:

Gentle People,

I’d like to have Elements and indicators in one excel pivot table. How do

I do that? The available views I have have either Pivot_Indicator or

Pivot_Routinedata.

Kabango Malewezi

SSDI Services

2nd Floor, Pamodzi House, City Center, P.O Box 1091, Lilongwe, Malawi

Tel: +265 (01) 776 412/3/4 | fax: +265 (01) 776 410 | Mobile: +265 (0) 88

4 245 431

kmalewezi@jhpiego.net <mailto:kmalewezi@jhpiego.net > |

www.jhpiego.org<http://www.jhpiego.org/>

Skype: kabango.malewezi | Twitter: kabangoM


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

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

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

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


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

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

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

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


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

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

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

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

Noted.

···

Sent from my BlackBerry® smartphone

-----Original Message-----
From: Bob Jolliffe <bobjolliffe@gmail.com>
Date: Fri, 20 Jul 2012 18:43:13
To: Ola Hodne Titlestad<olati@ifi.uio.no>
Cc: Kabango Malewezi<kmalewezi@jhpiego.net>; <dhis2-users@lists.launchpad.net>
Subject: Re: [Dhis2-users] Datamart

I guess another workaround might be to create simple naive indicators
(ie denominator=1) but this could easily lead to indicator
proliferation.

On 20 July 2012 18:39, Ola Hodne Titlestad <olati@ifi.uio.no> wrote:

Jason,
How do you handle the values in this view? Indicator values should be set up
as a calculated field to let excel aggregate numerators and denominators
separately for percentage values. If not the values for higher levels will
be wrong.

Or are you using only one orgunit level in this view?

I guess it is possible to use two separate value fields for data elements
and indicators in the same pivot table?
Sounds confusing, but for a very specific/static table maybe.... but then I
would recommend standard reports.

Ola
-------

On Jul 20, 2012 7:24 PM, "Kabango Malewezi" <kmalewezi@jhpiego.net> wrote:

Hi Kabango,

That is not possible.

You can do it with report tables and standard reports.

Ola
-------

On Jul 20, 2012 10:49 AM, "Kabango Malewezi" <kmalewezi@jhpiego.net> >> wrote:

Gentle People,

I'd like to have Elements and indicators in one excel pivot table. How do
I do that? The available views I have have either Pivot_Indicator or
Pivot_Routinedata.

Kabango Malewezi

SSDI Services

2nd Floor, Pamodzi House, City Center, P.O Box 1091, Lilongwe, Malawi
Tel: +265 (01) 776 412/3/4 | fax: +265 (01) 776 410 | Mobile: +265 (0) 88
4 245 431

kmalewezi@jhpiego.net <mailto:kmalewezi@jhpiego.net > |
www.jhpiego.org<http://www.jhpiego.org/>

Skype: kabango.malewezi | Twitter: kabangoM

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

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

I get it I'll just use reports.

···

Sent from my BlackBerry® smartphone
________________________________
From: Ola Hodne Titlestad <olati@ifi.uio.no>
Sender: <olatitle@gmail.com>
Date: Fri, 20 Jul 2012 19:39:08 +0200
To: Kabango Malewezi<kmalewezi@jhpiego.net>
Cc: <dhis2-users@lists.launchpad.net>; Jason Pickering<jason.p.pickering@gmail.com>
Subject: Re: [Dhis2-users] Datamart

Jason,
How do you handle the values in this view? Indicator values should be set up as a calculated field to let excel aggregate numerators and denominators separately for percentage values. If not the values for higher levels will be wrong.

Or are you using only one orgunit level in this view?

I guess it is possible to use two separate value fields for data elements and indicators in the same pivot table?
Sounds confusing, but for a very specific/static table maybe.... but then I would recommend standard reports.

Ola
-------

On Jul 20, 2012 7:24 PM, "Kabango Malewezi" <kmalewezi@jhpiego.net<mailto:kmalewezi@jhpiego.net>> wrote:

Hi Kabango,

That is not possible.

You can do it with report tables and standard reports.

Ola
-------

On Jul 20, 2012 10:49 AM, "Kabango Malewezi" <kmalewezi@jhpiego.net<mailto:kmalewezi@jhpiego.net>> wrote:
Gentle People,

I'd like to have Elements and indicators in one excel pivot table. How do I do that? The available views I have have either Pivot_Indicator or Pivot_Routinedata.

Kabango Malewezi

SSDI Services

2nd Floor, Pamodzi House, City Center, P.O Box 1091, Lilongwe, Malawi
Tel: +265 (01) 776 412/3/4 | fax: +265 (01) 776 410<tel:%2B265%20%2801%29%20776%20410> | Mobile: +265 (0) 88 4 245 431<tel:%2B265%20%280%29%2088%204%20245%20431>

kmalewezi@jhpiego.net<mailto:kmalewezi@jhpiego.net> <mailto:kmalewezi@jhpiego.net<mailto:kmalewezi@jhpiego.net> > | www.jhpiego.org<http://www.jhpiego.org><http://www.jhpiego.org/>

Skype: kabango.malewezi | Twitter: kabangoM

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

Hi there. Apologies for the late response.

Here is the way we do it. First, the view in MyDatamart.

DROP VIEW IF EXISTS vw_ou4_pivotsource_rapid_weekly_inds_des;
CREATE VIEW vw_ou4_pivotsource_rapid_weekly_inds_des as

SELECT dv.organisationunitid,
ou2.name as province,
ou3.name as district,
ou4.name as facility,
de.name as dataelementname,
inds.indicator_type as datatype,
p.startdate as begin_date,
p.enddate as end_date,
strftime('%m',p.startdate) as month,
substr(dv.period,1,4) as year,
substr(dv.period,6,8) + 1 as week,
dv.factor,
dv.numeratorvalue,
dv.denominatorvalue,
dv.numeratorvalue*dv.factor AS numxfactor,
CASE dv.numeratorvalue WHEN 0 THEN 1 ELSE 0 END as is_zero,
1 as return_order
FROM aggregatedindicatorvalue dv
INNER JOIN _orgunitstructure ous
ON dv.organisationunitid = ous.organisationunitid
INNER JOIN organisationunit ou2 on ous.idlevel2 = ou2.organisationunitid
INNER JOIN organisationunit ou3 on ous.idlevel3 = ou3.organisationunitid
INNER JOIN organisationunit ou4 on ous.idlevel4 = ou4.organisationunitid
INNER JOIN _indicatorgroupsetstructure inds on inds.indicatorid = dv.indicatorid
INNER JOIN indicator de on dv.indicatorid = de.indicatorid
INNER JOIN period p on dv.period = p.period
WHERE dv.organisationunitid in (SELECT DISTINCT idlevel4 from
_orgunitstructure where idlevel4 IS NOT NULL)
and dv.periodtype = 'W' and inds.indicator_type = 'Indicators'
UNION
SELECT dv.organisationunitid,
ou2.name as province,
ou3.name as district,
ou4.name as facility,
de.name as dataelementname,
'Raw data' as datatype,
p.startdate as begin_date,
p.enddate as end_date,
strftime('%m',p.startdate) as month,
substr(dv.period,1,4) as year,
substr(dv.period,6,8) + 1 as week,
NULL as factor,
dv.value as numeratorvalue,
NULL as denominatorvalue,
  NULL AS numxfactor,
CASE dv.value when 0 THEN 1 ELSE 0 END as is_zero,
2 as return_order
FROM aggregateddatavalue dv
INNER JOIN _orgunitstructure ous
ON dv.organisationunitid = ous.organisationunitid
INNER JOIN organisationunit ou2 on ous.idlevel2 = ou2.organisationunitid
INNER JOIN organisationunit ou3 on ous.idlevel3 = ou3.organisationunitid
INNER JOIN organisationunit ou4 on ous.idlevel4 = ou4.organisationunitid
INNER JOIN dataelement de on dv.dataelementid = de.dataelementid
INNER JOIN period p on dv.period = p.period
WHERE dv.organisationunitid in (SELECT DISTINCT idlevel4 from
_orgunitstructure where idlevel4 IS NOT NULL)
and dv.periodtype = 'W'
UNION
SELECT dv.organisationunitid,
ou2.name as province,
ou3.name as district,
ou4.name as facility,
de.name as dataelementname,
inds.indicator_type as datatype,
p.startdate as begin_date,
p.enddate as end_date,
strftime('%m',p.startdate) as month,
substr(dv.period,1,4) as year,
substr(dv.period,6,8) + 1 as week,
NULL as factor,
dv.numeratorvalue,
NULL as denominatorvalue,
  NULL AS numxfactor,
  CASE dv.numeratorvalue when 0 THEN 1 ELSE 0 END as is_zero,
3 as return_order
FROM aggregatedindicatorvalue dv
INNER JOIN _orgunitstructure ous
ON dv.organisationunitid = ous.organisationunitid
INNER JOIN organisationunit ou2 on ous.idlevel2 = ou2.organisationunitid
INNER JOIN organisationunit ou3 on ous.idlevel3 = ou3.organisationunitid
INNER JOIN organisationunit ou4 on ous.idlevel4 = ou4.organisationunitid
INNER JOIN _indicatorgroupsetstructure inds on inds.indicatorid = dv.indicatorid
INNER JOIN indicator de on dv.indicatorid = de.indicatorid
INNER JOIN period p on dv.period = p.period
WHERE dv.organisationunitid in (SELECT DISTINCT idlevel4 from
_orgunitstructure where idlevel4 IS NOT NULL)
and dv.periodtype = 'W' and inds.indicator_type = 'Calculated data elements'
ORDER BY return_order;

Note that I create three separate views for indicators, data elements
and CDEs and union them together into a consolidated view. I use an
indicator group to distinguish between indicators and calculated data
elements.

Finally, the "value" field is defined in Excel as

=IF(denominatorvalue,numxfactor/denominatorvalue,numeratorvalue)

So, if there is a denominator value, then use a formula appropriate
for indicators, otherwise, just use the numerator value.

Hope this is helpful. The SQL is quite specific for our purposes, but
the approach could probably be adopted rather easily.
Comments/feedback welcome.

Best regards,
Jason

···

On Sat, Jul 21, 2012 at 12:00 PM, Kabango Malewezi <kmalewezi@jhpiego.net> wrote:

I get it I'll just use reports.
Sent from my BlackBerry® smartphone
________________________________
From: Ola Hodne Titlestad <olati@ifi.uio.no>
Sender: <olatitle@gmail.com>
Date: Fri, 20 Jul 2012 19:39:08 +0200
To: Kabango Malewezi<kmalewezi@jhpiego.net>
Cc: <dhis2-users@lists.launchpad.net>; Jason Pickering<jason.p.pickering@gmail.com>
Subject: Re: [Dhis2-users] Datamart

Jason,
How do you handle the values in this view? Indicator values should be set up as a calculated field to let excel aggregate numerators and denominators separately for percentage values. If not the values for higher levels will be wrong.

Or are you using only one orgunit level in this view?

I guess it is possible to use two separate value fields for data elements and indicators in the same pivot table?
Sounds confusing, but for a very specific/static table maybe.... but then I would recommend standard reports.

Ola
-------

On Jul 20, 2012 7:24 PM, "Kabango Malewezi" <kmalewezi@jhpiego.net<mailto:kmalewezi@jhpiego.net>> wrote:

Hi Kabango,

That is not possible.

You can do it with report tables and standard reports.

Ola
-------

On Jul 20, 2012 10:49 AM, "Kabango Malewezi" <kmalewezi@jhpiego.net<mailto:kmalewezi@jhpiego.net>> wrote:
Gentle People,

I'd like to have Elements and indicators in one excel pivot table. How do I do that? The available views I have have either Pivot_Indicator or Pivot_Routinedata.

Kabango Malewezi

SSDI Services

2nd Floor, Pamodzi House, City Center, P.O Box 1091, Lilongwe, Malawi
Tel: +265 (01) 776 412/3/4 | fax: +265 (01) 776 410<tel:%2B265%20%2801%29%20776%20410> | Mobile: +265 (0) 88 4 245 431<tel:%2B265%20%280%29%2088%204%20245%20431>

kmalewezi@jhpiego.net<mailto:kmalewezi@jhpiego.net> <mailto:kmalewezi@jhpiego.net<mailto:kmalewezi@jhpiego.net> > | www.jhpiego.org<http://www.jhpiego.org><http://www.jhpiego.org/>

Skype: kabango.malewezi | Twitter: kabangoM

_______________________________________________
Mailing list: DHIS 2 Users in Launchpad
Post to : dhis2-users@lists.launchpad.net<mailto:dhis2-users@lists.launchpad.net>
Unsubscribe : DHIS 2 Users in Launchpad
More help : ListHelp - Launchpad Help

Thank you so very much. Will try it.

Kabango Malewezi | Database Specialist

SSDI Services

2nd Floor, Pamodzi House, City Center, P.O Box 1091, Lilongwe, Malawi
Tel: +265 (01) 776 412/3/4 | fax: +265 (01) 776 410 | Mobile: +265 (0) 88 4 245 431

kmalewezi@jhpiego.net | www.jhpiego.org

Skype: kabango.malewezi | Twitter: kabangoM

···

________________________________________
From: Jason Pickering [jason.p.pickering@gmail.com]
Sent: Wednesday, July 25, 2012 8:12 PM
To: Kabango Malewezi
Cc: Ola Hodne Titlestad; olatitle@gmail.com; dhis2-users@lists.launchpad.net
Subject: Re: [Dhis2-users] Datamart

Hi there. Apologies for the late response.

Here is the way we do it. First, the view in MyDatamart.

DROP VIEW IF EXISTS vw_ou4_pivotsource_rapid_weekly_inds_des;
CREATE VIEW vw_ou4_pivotsource_rapid_weekly_inds_des as

SELECT dv.organisationunitid,
ou2.name as province,
ou3.name as district,
ou4.name as facility,
de.name as dataelementname,
inds.indicator_type as datatype,
p.startdate as begin_date,
p.enddate as end_date,
strftime('%m',p.startdate) as month,
substr(dv.period,1,4) as year,
substr(dv.period,6,8) + 1 as week,
dv.factor,
dv.numeratorvalue,
dv.denominatorvalue,
dv.numeratorvalue*dv.factor AS numxfactor,
CASE dv.numeratorvalue WHEN 0 THEN 1 ELSE 0 END as is_zero,
1 as return_order
FROM aggregatedindicatorvalue dv
INNER JOIN _orgunitstructure ous
ON dv.organisationunitid = ous.organisationunitid
INNER JOIN organisationunit ou2 on ous.idlevel2 = ou2.organisationunitid
INNER JOIN organisationunit ou3 on ous.idlevel3 = ou3.organisationunitid
INNER JOIN organisationunit ou4 on ous.idlevel4 = ou4.organisationunitid
INNER JOIN _indicatorgroupsetstructure inds on inds.indicatorid = dv.indicatorid
INNER JOIN indicator de on dv.indicatorid = de.indicatorid
INNER JOIN period p on dv.period = p.period
WHERE dv.organisationunitid in (SELECT DISTINCT idlevel4 from
_orgunitstructure where idlevel4 IS NOT NULL)
and dv.periodtype = 'W' and inds.indicator_type = 'Indicators'
UNION
SELECT dv.organisationunitid,
ou2.name as province,
ou3.name as district,
ou4.name as facility,
de.name as dataelementname,
'Raw data' as datatype,
p.startdate as begin_date,
p.enddate as end_date,
strftime('%m',p.startdate) as month,
substr(dv.period,1,4) as year,
substr(dv.period,6,8) + 1 as week,
NULL as factor,
dv.value as numeratorvalue,
NULL as denominatorvalue,
  NULL AS numxfactor,
CASE dv.value when 0 THEN 1 ELSE 0 END as is_zero,
2 as return_order
FROM aggregateddatavalue dv
INNER JOIN _orgunitstructure ous
ON dv.organisationunitid = ous.organisationunitid
INNER JOIN organisationunit ou2 on ous.idlevel2 = ou2.organisationunitid
INNER JOIN organisationunit ou3 on ous.idlevel3 = ou3.organisationunitid
INNER JOIN organisationunit ou4 on ous.idlevel4 = ou4.organisationunitid
INNER JOIN dataelement de on dv.dataelementid = de.dataelementid
INNER JOIN period p on dv.period = p.period
WHERE dv.organisationunitid in (SELECT DISTINCT idlevel4 from
_orgunitstructure where idlevel4 IS NOT NULL)
and dv.periodtype = 'W'
UNION
SELECT dv.organisationunitid,
ou2.name as province,
ou3.name as district,
ou4.name as facility,
de.name as dataelementname,
inds.indicator_type as datatype,
p.startdate as begin_date,
p.enddate as end_date,
strftime('%m',p.startdate) as month,
substr(dv.period,1,4) as year,
substr(dv.period,6,8) + 1 as week,
NULL as factor,
dv.numeratorvalue,
NULL as denominatorvalue,
  NULL AS numxfactor,
  CASE dv.numeratorvalue when 0 THEN 1 ELSE 0 END as is_zero,
3 as return_order
FROM aggregatedindicatorvalue dv
INNER JOIN _orgunitstructure ous
ON dv.organisationunitid = ous.organisationunitid
INNER JOIN organisationunit ou2 on ous.idlevel2 = ou2.organisationunitid
INNER JOIN organisationunit ou3 on ous.idlevel3 = ou3.organisationunitid
INNER JOIN organisationunit ou4 on ous.idlevel4 = ou4.organisationunitid
INNER JOIN _indicatorgroupsetstructure inds on inds.indicatorid = dv.indicatorid
INNER JOIN indicator de on dv.indicatorid = de.indicatorid
INNER JOIN period p on dv.period = p.period
WHERE dv.organisationunitid in (SELECT DISTINCT idlevel4 from
_orgunitstructure where idlevel4 IS NOT NULL)
and dv.periodtype = 'W' and inds.indicator_type = 'Calculated data elements'
ORDER BY return_order;

Note that I create three separate views for indicators, data elements
and CDEs and union them together into a consolidated view. I use an
indicator group to distinguish between indicators and calculated data
elements.

Finally, the "value" field is defined in Excel as

=IF(denominatorvalue,numxfactor/denominatorvalue,numeratorvalue)

So, if there is a denominator value, then use a formula appropriate
for indicators, otherwise, just use the numerator value.

Hope this is helpful. The SQL is quite specific for our purposes, but
the approach could probably be adopted rather easily.
Comments/feedback welcome.

Best regards,
Jason

On Sat, Jul 21, 2012 at 12:00 PM, Kabango Malewezi <kmalewezi@jhpiego.net> wrote:

I get it I'll just use reports.
Sent from my BlackBerry® smartphone
________________________________
From: Ola Hodne Titlestad <olati@ifi.uio.no>
Sender: <olatitle@gmail.com>
Date: Fri, 20 Jul 2012 19:39:08 +0200
To: Kabango Malewezi<kmalewezi@jhpiego.net>
Cc: <dhis2-users@lists.launchpad.net>; Jason Pickering<jason.p.pickering@gmail.com>
Subject: Re: [Dhis2-users] Datamart

Jason,
How do you handle the values in this view? Indicator values should be set up as a calculated field to let excel aggregate numerators and denominators separately for percentage values. If not the values for higher levels will be wrong.

Or are you using only one orgunit level in this view?

I guess it is possible to use two separate value fields for data elements and indicators in the same pivot table?
Sounds confusing, but for a very specific/static table maybe.... but then I would recommend standard reports.

Ola
-------

On Jul 20, 2012 7:24 PM, "Kabango Malewezi" <kmalewezi@jhpiego.net<mailto:kmalewezi@jhpiego.net>> wrote:

Hi Kabango,

That is not possible.

You can do it with report tables and standard reports.

Ola
-------

On Jul 20, 2012 10:49 AM, "Kabango Malewezi" <kmalewezi@jhpiego.net<mailto:kmalewezi@jhpiego.net>> wrote:
Gentle People,

I'd like to have Elements and indicators in one excel pivot table. How do I do that? The available views I have have either Pivot_Indicator or Pivot_Routinedata.

Kabango Malewezi

SSDI Services

2nd Floor, Pamodzi House, City Center, P.O Box 1091, Lilongwe, Malawi
Tel: +265 (01) 776 412/3/4 | fax: +265 (01) 776 410<tel:%2B265%20%2801%29%20776%20410> | Mobile: +265 (0) 88 4 245 431<tel:%2B265%20%280%29%2088%204%20245%20431>

kmalewezi@jhpiego.net<mailto:kmalewezi@jhpiego.net> <mailto:kmalewezi@jhpiego.net<mailto:kmalewezi@jhpiego.net> > | www.jhpiego.org<http://www.jhpiego.org><http://www.jhpiego.org/>

Skype: kabango.malewezi | Twitter: kabangoM

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