← Back to team overview

dhis2-devs team mailing list archive

  1. dhis2-devs team
  2. Mailing list archive
  3. Message #00980

Re: Providing another source table for reports

  Thread Previous • Date Previous • • Thread Next 
Hi,

Ola Hodne Titlestad wrote:

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.
Yes you are right but that exercise has been done in order to move data from the actual non dhis system.
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. 
Fine.
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.
The A1-34 are not an accident invetion. The actual System uses that. So we are keeping that way. The actual system is the source for data until the migration ends. We want use DHIS. So we will not do another entering application.
Yeah, we need week as entering parameter. We gonna test the ideias from Murodullo. We will come here to tell what happend on the lab explosion :-) 

I do not know other way to say Thanks for your support so I say Thanks a lot
Caveman



best regards,
Ola Hodne Titlestad
HISP
University of Oslo
On Wed, May 13, 2009 at 4:39 PM, Cintia Marisa <cintia.banze@xxxxxxxxx <mailto: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 <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

Follow ups

References

  Thread Previous • Date Previous • • Thread Next