Hi,
In attachment one of the reports I need to build, maybe it will help you
guys to understand what i need.
I used the query below to get the data that i needed.
=============================================================
select a.periodid, a.sourceid, b.week, b.YR , b.startdate, b.enddate,
c.name <http://c.name>,
sum(if(dataelementid=691,a.value,0)) as A1,
sum(if(dataelementid=692,a.value,0)) as A2,
sum(if(dataelementid=693,a.value,0)) as A3,
sum(if(dataelementid=694,a.value,0)) as A4,
sum(if(dataelementid=695,a.value,0)) as A5,
sum(if(dataelementid=696,a.value,0)) as A6,
sum(if(dataelementid=697,a.value,0)) as A7,
sum(if(dataelementid=698,a.value,0)) as A8,
sum(if(dataelementid=699,a.value,0)) as A9,
sum(if(dataelementid=700,a.value,0)) as A10,
sum(if(dataelementid=730,a.value,0)) as A11,
sum(if(dataelementid=727,a.value,0)) as A12,
sum(if(dataelementid=728,a.value,0)) as A13,
sum(if(dataelementid=729,a.value,0)) as A14,
sum(if(dataelementid=705,a.value,0)) as A15,
sum(if(dataelementid=706,a.value,0)) as A16,
sum(if(dataelementid=707,a.value,0)) as A17,
sum(if(dataelementid=708,a.value,0)) as A18,
sum(if(dataelementid=709,a.value,0)) as A19,
sum(if(dataelementid=710,a.value,0)) as A20,
sum(if(dataelementid=711,a.value,0)) as A21,
sum(if(dataelementid=712,a.value,0)) as A22,
sum(if(dataelementid=713,a.value,0)) as A23,
sum(if(dataelementid=714,a.value,0)) as A24,
sum(if(dataelementid=715,a.value,0)) as A25,
sum(if(dataelementid=716,a.value,0)) as A26,
sum(if(dataelementid=717,a.value,0)) as A27,
sum(if(dataelementid=718,a.value,0)) as A28,
sum(if(dataelementid=721,a.value,0)) as A29,
sum(if(dataelementid=722,a.value,0)) as A30,
sum(if(dataelementid=731,a.value,0)) as A31,
sum(if(dataelementid=725,a.value,0)) as A32,
sum(if(dataelementid=726,a.value,0)) as A33,
sum(if(dataelementid=732,a.value,0)) as A34
from dhis2_exp.datavalue a
inner join modbas2dhis.period b on a.periodid = b.periodid
-- inner join dhis2_exp.organisationunit c on a.sourceid =
c.organisationunitid
where periodtypeid=7
group by a.periodid, a.sourceid
=============================================================
regards
On Wed, May 13, 2009 at 12:50 PM, Ola Hodne Titlestad <olati@xxxxxxxxxx
<mailto:olati@xxxxxxxxxx>> wrote:
HI Cintia,
I reply to the list so that we can share this discussion with the
rest of the community.
The report table concept has been developed in an evolutionary way
based on various requirements from different countries. We are happy
to further extend it to meet your needs in Mozambique. I am not sure
I understand your problems of periods, and not sure what you mean by
"doesn't seem to keep the keys to link the data elements, periods
and organization units".
If you like you could provide us more info on this, and if possible
a screenshot of your report, and we could try to modify report
tables functionality to meet your needs.
best regards,
Ola Hodne Titlestad
HISP
University of Oslo
On Wed, May 13, 2009 at 12:40 PM, Cintia Marisa
<cintia.banze@xxxxxxxxx <mailto:cintia.banze@xxxxxxxxx>> wrote:
Hi, thanks for the help.
I've already read the articles you sent to me.
I'll try again, then I'll give you guys a feedback.
I choose not use report table, mostly because of the periods.
The problem Orvalho is having( we are having) with periods,
make it difficult for us to create report tables.
It, takes a long time for the table to be created, and it
doesn't seem to keep the keys to link the data elements, periods
and organization units as I need.
In the reports I'm making i need to have cross tabulated data
with org units, data elements and periods.
I know report tables are mean to make my work easier, but i
can't get them to provide the data the way I need.
Thanks for the support.
On Wed, May 13, 2009 at 11:21 AM, Ola Hodne Titlestad
<olati@xxxxxxxxxx <mailto:olati@xxxxxxxxxx>> wrote:
Hi,
When using standard reports (BIRT reports in DHIS 2) the
only limitation is that you need to use the DHIS 2 database
as your source, but within that database you can create BIRT
datasets based on any table or view as you like. This is
simply because DHIS replaces the database connection string
in the BIRT design file when you upload the report. This is
done to make sure that the data source is available.
Still, I'm wondering why you do not want to use report
tables? Report tables are generic and very flexible and can
provide routine data sources for your reports that are
automatically updated to e.g. the latest month, the last
quarter, acculmulative values so far this year and more.You
can also specify exactly which data elements or indicators
to use. Furthermore, report tables also allow for report
paremeters where you can specify which orgunit to show data
for, or all children below a selected orgunit, and also
which month to use, so that you can reuse your design for
many purposes. Report tables are small and automatically
generated datamarts that you can use out of the box while
other data sources most liley will need some kind of manual
update like an export to datamart or similar.
You can read more about report tables here:
https://answers.launchpad.net/dhis2/+question/61455
https://answers.launchpad.net/dhis2/+faq/370
What kind of report are you designing and what made you
choose not to use report tables?
best regards,
Ola Hodne Titlestad
HISP
University of Oslo
On Wed, May 13, 2009 at 10:40 AM, Murodullo Latifov
<murodlatifov@xxxxxxxxx <mailto:murodlatifov@xxxxxxxxx>> wrote:
Hi Cintia,
------------------------------------------------------------------------
*From:* Cintia Marisa <cintia.banze@xxxxxxxxx
<mailto:cintia.banze@xxxxxxxxx>>
*To:* dhis2-devs@xxxxxxxxxxxxxxxxxxx
<mailto:dhis2-devs@xxxxxxxxxxxxxxxxxxx>
*Sent:* Wednesday, May 13, 2009 9:22:14 AM
*Subject:* [Dhis2-devs] Providing another source table
for reports
Hi all,
I'm trying to build the reports without using the Report
Tables tools.
When i add new report on dhis it gives a chance to
provide another data source different from the report
tables (" If selecting no report table, the user is
responsible for providing the data source table. ").
I just don't know how to tell DHSI that I want to use
another data source.
Any help on how to accomplish this?
Thanks in advance
--
Cintia Marisa
You can use BIRT to include your dataset. Simply create
dataset under outline->DataSet. Simply choose "SQL
select query" and make you sql statement on next screen,
like "select * from yourtable where ..."
murod
_______________________________________________
Mailing list: https://launchpad.net/~dhis2-devs
<https://launchpad.net/%7Edhis2-devs>
Post to : dhis2-devs@xxxxxxxxxxxxxxxxxxx
<mailto:dhis2-devs@xxxxxxxxxxxxxxxxxxx>
Unsubscribe : https://launchpad.net/~dhis2-devs
<https://launchpad.net/%7Edhis2-devs>
More help : https://help.launchpad.net/ListHelp
_______________________________________________
Mailing list: https://launchpad.net/~dhis2-devs
<https://launchpad.net/%7Edhis2-devs>
Post to : dhis2-devs@xxxxxxxxxxxxxxxxxxx
<mailto:dhis2-devs@xxxxxxxxxxxxxxxxxxx>
Unsubscribe : https://launchpad.net/~dhis2-devs
<https://launchpad.net/%7Edhis2-devs>
More help : https://help.launchpad.net/ListHelp
--
Cintia Marisa
--
Cintia Marisa
------------------------------------------------------------------------
_______________________________________________
Mailing list: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@xxxxxxxxxxxxxxxxxxx
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp
