dhis2-devs team mailing list archive

[Ola Hodne Titlestad <olati@xxxxxxxxxx>]

Hi,

The main challenge with designing such a report in BIRT is that you need to
know which data element the values belong to as the design is not a straight
forward table where you could list the data elements row by row.

Normally I would not recommend BIRT at all for this report, but rather
design a custom data entry form for this and then use the dataset report
option to view the report. That would also give you the possibility of
entering data using the same form. Simply set up a dataset with the data
elements you need and then design the form using the built in editor in
DHIS2.

If you want to use BIRT the easiest way would be to select exactly the data
elements you need and then crosstab them so that the columns names would
correspond to data element names. It is not very intuitive for a new report
designer to use the codes A1-34 as you do with your query and data element
shortnames as columns would be much easier. In report tables you can do this
by selecting data elements as one of the cross-tabbed dimensions and then
select the data elements you need. Since this is a weekly bulletin/report we
need to add a reporting week parameter as well as a new relative period
called reporting week. That can be done. For now you could test to do the
same report by using a static period (one given week) and orgunit with
parameter in repor table and then use this table in BIRT.

best regards,
Ola Hodne Titlestad
HISP
University of Oslo


On Wed, May 13, 2009 at 4:39 PM, Cintia Marisa <cintia.banze@xxxxxxxxx>wrote:

> 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 <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>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>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> wrote:
>>>>
>>>>> Hi Cintia,
>>>>>
>>>>> ------------------------------
>>>>> *From:* Cintia Marisa <cintia.banze@xxxxxxxxx>
>>>>> *To:* 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
>>>>> 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
>>>> Unsubscribe : https://launchpad.net/~dhis2-devs<https://launchpad.net/%7Edhis2-devs>
>>>> More help   : https://help.launchpad.net/ListHelp
>>>>
>>>>
>>>
>>>
>>> --
>>> Cintia Marisa
>>>
>>
>>
>
>
> --
> Cintia Marisa
>