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
,
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 <ol...@ifi.uio.no>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.ba...@gmail.com>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
>> <ol...@ifi.uio.no>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 <
>>> murodlati...@yahoo.com> wrote:
>>>
>>>> Hi Cintia,
>>>>
>>>> ------------------------------
>>>> *From:* Cintia Marisa <cintia.ba...@gmail.com>
>>>> *To:* dhis2-devs@lists.launchpad.net
>>>> *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@lists.launchpad.net
>>>> 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@lists.launchpad.net
>>> Unsubscribe :
>>> https://launchpad.net/~dhis2-devs<https://launchpad.net/%7Edhis2-devs>
>>> More help : https://help.launchpad.net/ListHelp
>>>
>>>
>>
>>
>> --
>> Cintia Marisa
>>
>
>
--
Cintia Marisa
C03preview.pdf
Description: Adobe PDF document
_______________________________________________ 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
