dhis2-users team mailing list archive

[Jason Pickering <jason.p.pickering@xxxxxxxxx>]

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@xxxxxxxxxxx> wrote:
> I get it I'll just use reports.
> Sent from my BlackBerry® smartphone
> ________________________________
> From: Ola Hodne Titlestad <olati@xxxxxxxxxx>
> Sender: <olatitle@xxxxxxxxx>
> Date: Fri, 20 Jul 2012 19:39:08 +0200
> To: Kabango Malewezi<kmalewezi@xxxxxxxxxxx>
> Cc: <dhis2-users@xxxxxxxxxxxxxxxxxxx>; Jason Pickering<jason.p.pickering@xxxxxxxxx>
> 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@xxxxxxxxxxx<mailto:kmalewezi@xxxxxxxxxxx>> 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@xxxxxxxxxxx<mailto:kmalewezi@xxxxxxxxxxx>> 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@xxxxxxxxxxx<mailto:kmalewezi@xxxxxxxxxxx> <mailto:kmalewezi@xxxxxxxxxxx<mailto:kmalewezi@xxxxxxxxxxx> > |  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@xxxxxxxxxxxxxxxxxxx<mailto:dhis2-users@xxxxxxxxxxxxxxxxxxx>
> Unsubscribe : https://launchpad.net/~dhis2-users
> More help   : https://help.launchpad.net/ListHelp