dhis2-devs team mailing list archive

[noreply@xxxxxxxxxxxxx]

------------------------------------------------------------
revno: 623
committer: Lars Helge Øverland <larshelge@xxxxxxxxx>
branch nick: dhis2-docbook-docs
timestamp: Thu 2012-11-15 12:20:50 +0100
message:
  SQL parameters in standard reports
modified:
  src/docbkx/en/dhis2_user_man_creating_reporting.xml


--
lp:~dhis2-documenters/dhis2/dhis2-docbook-docs
https://code.launchpad.net/~dhis2-documenters/dhis2/dhis2-docbook-docs

Your team DHIS 2 developers is subscribed to branch lp:~dhis2-documenters/dhis2/dhis2-docbook-docs.
To unsubscribe from this branch go to https://code.launchpad.net/~dhis2-documenters/dhis2/dhis2-docbook-docs/+edit-subscription

=== modified file 'src/docbkx/en/dhis2_user_man_creating_reporting.xml'
--- src/docbkx/en/dhis2_user_man_creating_reporting.xml	2012-02-18 07:50:49 +0000
+++ src/docbkx/en/dhis2_user_man_creating_reporting.xml	2012-11-15 11:20:50 +0000
@@ -357,8 +357,15 @@
     <title>Standard reports</title>
     <section>
       <title>What is a standard report?</title>
-      <para>A standard report is a manually designed report that presents manually chosen data in a manually specified layout. Still, it&apos;s highly flexible, as it can be designed to be reused over and over again, by making use of the powerful report tables.</para>
-      <para>A standard report can present any value from any table in the DHIS 2 database, but for the report design, we want the reports to be flexible, so we want to connect the reports to tables that changes through time. The report tables are therefore ideal for the report design.</para>
+      <para>A standard report is a manually designed report that presents data in a manually
+        specified layout. Standard reports can be based either on report tables or SQL queries. Both
+        approaches are described in the following sections. The main advantage of using report
+        tables is that of simplicity - no special development skills are required. In cases where
+        you have special requirements or need to utilize additional parts of the DHIS database,
+        beyond the data mart, you might want to use a SQL based standard report. In any case you
+        will be able to utilize report parameters in order to create dynamic reports. The following
+        guide will use the report table approach, while the SQL approach is covered towards the
+        end.</para>
     </section>
     <section>
       <title>Designing Standard reports in iReport</title>
@@ -1168,5 +1175,84 @@
         </itemizedlist>
       </section>
     </section>
+    <section>
+      <title>Designing SQL based standard reports</title>
+      <para>A standard report might also be based on SQL queries. This is useful when you need to
+        access multiple tables in the DHIS database and do custom selects and joins. </para>
+      <para>- This step is optional, but handy when you need to debug your reports and when you have
+        direct access to the database you want to use. Click on the "report datasources" button,
+        "New", "Database JDBC connection" and click "next". In this window you can give you
+        connection a name and select the JDBC driver. PostgreSQL and MySQL should come included in
+        your iReport.  Then enter the JDBC connection URL, username and password. The last three
+        referes to your database and can be retrieved from your DHIS configuration file
+        (hibernate.properties). Click "save". You have now connected iReport to your
+        database.</para>
+      <para>- Go to standard reports and click "add new", then "get report template". Open this
+        template in iReport. This template contains a series of report parameters which can be used
+        to create dynamic SQL statements. These parameters will be subsituted based on the report
+        parameters which we will later select and include in the standard report. The parameters
+        are:</para>
+      <itemizedlist>
+        <listitem>
+          <para>periods - string of comma-separated identifiers of the relative periods</para>
+        </listitem>
+        <listitem>
+          <para>period_name - name of the reporting reporting period</para>
+        </listitem>
+        <listitem>
+          <para>organisationunits - identifier of the selected organisation units</para>
+        </listitem>
+        <listitem>
+          <para>organisationunit_name - name of the reporting organisation unit</para>
+        </listitem>
+        <listitem>
+          <para>organisationunit_level: level of the reporting organisation unit</para>
+        </listitem>
+        <listitem>
+          <para>organisationunit_level_column: name of the corresponding column in the
+            _orgunitstructure resource table</para>
+        </listitem>
+      </itemizedlist>
+      <para>These parameters can be included in SQL statements using the $P!{periods} syntax, where
+        "periods" represents the parameter.</para>
+      <para>- To create a SQL query in iReport, click on the "report query" button. Write or paste
+        your query into the textarea. An example SQL query using parameters which will create a
+        report displaying raw data values at the fourth level in the org unit hierarchy is:</para>
+      <screen>select district.name as district, chiefdom.name as chiefdom, ou.name as facility,
+bcg.value as bcg, yellowfever.value as yellowfever, measles.value as  measles
+from organisationunit ou
+left outer join _orgunitstructure ous
+  on (ou.organisationunitid=ous.organisationunitid)
+left outer join organisationunit district
+  on (ous.idlevel2=district.organisationunitid)
+left outer join organisationunit chiefdom
+  on (ous.idlevel3=chiefdom.organisationunitid)
+left outer join (
+  select sourceid, sum(cast(value as double precision)) as value
+  from datavalue
+  where dataelementid=359706
+  and periodid=$P!{periods}
+  group by sourceid) as bcg on bcg.sourceid=ou.organisationunitid
+left outer join (
+  select sourceid, sum(cast(value as double precision)) as value
+  from datavalue
+  where dataelementid=35
+  and periodid=$P!{periods}
+  group by sourceid) as yellowfever on yellowfever.sourceid=ou.organisationunitid
+where ous.level=4
+and ous.$P!{organisationunit_level_column}=$P!{organisationunits}
+order by district.name, chiefdom.name, ou.name;
+</screen>
+      <para>Notice how all parameters are used in the query, along with SQL joins of resource tables
+        in the DHIS database.</para>
+      <para>- Finally, back in the add new report screen, we click on "Use JDBC data source". This
+        enables you to select any relative period and report parameters for your report. Relative
+        periods are relative to today's date. Report parameters will cause a prompt during report
+        creation and makes it possible to dynamically select organisation units and periods to use
+        for your report during runtime. For the example above, we must select "reporting month"
+        under relative periods and both "reporting month" and "organisation unit" under report
+        parameters. Click save. This will redirect you to the list of reports, where you can click
+        the green "create" icon next to your report to render it.</para>
+    </section>
   </section>
 </chapter>