Thank you so very much. Will try it.
···
________________________________________
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: 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