Testing DHIS2 - Problem while retrieving data

Hi

I’m testing DHIS2, the installation on ubuntu following the guide was

ok. I then tried to add data elements and visualize them with the
reports but couldn’t get this work. I even tried to add one simple data

element (named element A, value type: number, aggregation operator: sum

  • as default), then defined a data set (named collect, frequency: daily)

and associated the data element A to it. An organization unit named root
has been created and associated to this data set. Through the data entry

form I added some values for 4 days. I can see them when selecting back
the periods. But when I go to reports - dataset reports, there is no

value linked to this data element A…

I tried with several examples, this last one were the simplest, but I

still can not get any value with the report tools. A look at the
catalina.out log is showing me this line dealing with my report query:

"nested exception in org.postgresql.util.PSQLException: ERROR: Relation

“analytics_2013” doesn’t exist"

I’ve just run Analytics and datasmart – start exporting, as suggested by Knut (thanks to him) but the situation is the same. I shall also mention that when doing a reporting rate summary, I’m getting correct rates corresponding to the number of data I added.

I’m using version 2.13. Any idea of what is going wrong ? Where can I check in the postgresql database (which table to look in?) ?

Thanks for the help.

Regards
Ilias

The data will be in datavalues

  1. des. 2013 14:02 skrev “Ilias Dine” ilias.dine@gmail.com følgende:
···

Hi

I’m testing DHIS2, the installation on ubuntu following the guide was

ok. I then tried to add data elements and visualize them with the
reports but couldn’t get this work. I even tried to add one simple data

element (named element A, value type: number, aggregation operator: sum

  • as default), then defined a data set (named collect, frequency: daily)

and associated the data element A to it. An organization unit named root
has been created and associated to this data set. Through the data entry

form I added some values for 4 days. I can see them when selecting back
the periods. But when I go to reports - dataset reports, there is no

value linked to this data element A…

I tried with several examples, this last one were the simplest, but I

still can not get any value with the report tools. A look at the
catalina.out log is showing me this line dealing with my report query:

"nested exception in org.postgresql.util.PSQLException: ERROR: Relation

“analytics_2013” doesn’t exist"

I’ve just run Analytics and datasmart – start exporting, as suggested by Knut (thanks to him) but the situation is the same. I shall also mention that when doing a reporting rate summary, I’m getting correct rates corresponding to the number of data I added.

I’m using version 2.13. Any idea of what is going wrong ? Where can I check in the postgresql database (which table to look in?) ?

Thanks for the help.

Regards
Ilias

OK.
Here is the result of the query to the database

dhis2=> select dataelementid, periodid, sourceid, value from datavalue;

dataelementid | periodid | sourceid | value

···

2013/12/7 Knut Staring knutst@gmail.com

The data will be in datavalues

  1. des. 2013 14:02 skrev “Ilias Dine” ilias.dine@gmail.com følgende:

Hi

I’m testing DHIS2, the installation on ubuntu following the guide was

ok. I then tried to add data elements and visualize them with the
reports but couldn’t get this work. I even tried to add one simple data

element (named element A, value type: number, aggregation operator: sum

  • as default), then defined a data set (named collect, frequency: daily)

and associated the data element A to it. An organization unit named root
has been created and associated to this data set. Through the data entry

form I added some values for 4 days. I can see them when selecting back
the periods. But when I go to reports - dataset reports, there is no

value linked to this data element A…

I tried with several examples, this last one were the simplest, but I

still can not get any value with the report tools. A look at the
catalina.out log is showing me this line dealing with my report query:

"nested exception in org.postgresql.util.PSQLException: ERROR: Relation

“analytics_2013” doesn’t exist"

I’ve just run Analytics and datasmart – start exporting, as suggested by Knut (thanks to him) but the situation is the same. I shall also mention that when doing a reporting rate summary, I’m getting correct rates corresponding to the number of data I added.

I’m using version 2.13. Any idea of what is going wrong ? Where can I check in the postgresql database (which table to look in?) ?

Thanks for the help.

Regards
Ilias

Hi there,

go to data admin, resource tables and generate the resource tables there.

Then run analytics again, and verify that the process completes successfully.

The first step happens automatically for scheduled analytics processes, but not for ad-hoc. I think we could include a check in the first situation and generate them if not existing at all.

Lars

···

2013/12/7 Knut Staring knutst@gmail.com

The data will be in datavalues

  1. des. 2013 14:02 skrev “Ilias Dine” ilias.dine@gmail.com følgende:

Hi

I’m testing DHIS2, the installation on ubuntu following the guide was

ok. I then tried to add data elements and visualize them with the
reports but couldn’t get this work. I even tried to add one simple data

element (named element A, value type: number, aggregation operator: sum

  • as default), then defined a data set (named collect, frequency: daily)

and associated the data element A to it. An organization unit named root
has been created and associated to this data set. Through the data entry

form I added some values for 4 days. I can see them when selecting back
the periods. But when I go to reports - dataset reports, there is no

value linked to this data element A…

I tried with several examples, this last one were the simplest, but I

still can not get any value with the report tools. A look at the
catalina.out log is showing me this line dealing with my report query:

"nested exception in org.postgresql.util.PSQLException: ERROR: Relation

“analytics_2013” doesn’t exist"

I’ve just run Analytics and datasmart – start exporting, as suggested by Knut (thanks to him) but the situation is the same. I shall also mention that when doing a reporting rate summary, I’m getting correct rates corresponding to the number of data I added.

I’m using version 2.13. Any idea of what is going wrong ? Where can I check in the postgresql database (which table to look in?) ?

Thanks for the help.

Regards
Ilias

Hi
Did it but still nothing on report.

If this can help figure out what’s going on:

  • In data administrator – data browser, when selecting period daily, begin and end dates covering the entry periods, browse by organization unit_ then selecting the root organization unit, I can see the values appearing on the result page

  • But when browsing by data sets, there is no result

I note also that : when selecting organization unit in browser type, a contextual select menu for organization units appears; no contextual select menu for data sets appears when choosing to browse by data set. Is this normal ?

···

2013/12/8 Lars Helge Øverland larshelge@gmail.com

Hi there,

go to data admin, resource tables and generate the resource tables there.

Then run analytics again, and verify that the process completes successfully.

The first step happens automatically for scheduled analytics processes, but not for ad-hoc. I think we could include a check in the first situation and generate them if not existing at all.

Lars

On Dec 7, 2013 2:47 PM, “Ilias Dine” ilias.dine@gmail.com wrote:

OK.
Here is the result of the query to the database

dhis2=> select dataelementid, periodid, sourceid, value from datavalue;

dataelementid | periodid | sourceid | value

---------------±---------±---------±------

49 | 51 | 48 | 1

49 | 52 | 48 | 2

49 | 53 | 48 | 5

(3 rows)

The values correspond to what has been set with data entry. But they still don’t appear in the dataset report…


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

2013/12/7 Knut Staring knutst@gmail.com

The data will be in datavalues

  1. des. 2013 14:02 skrev “Ilias Dine” ilias.dine@gmail.com følgende:

Hi

I’m testing DHIS2, the installation on ubuntu following the guide was

ok. I then tried to add data elements and visualize them with the
reports but couldn’t get this work. I even tried to add one simple data

element (named element A, value type: number, aggregation operator: sum

  • as default), then defined a data set (named collect, frequency: daily)

and associated the data element A to it. An organization unit named root
has been created and associated to this data set. Through the data entry

form I added some values for 4 days. I can see them when selecting back
the periods. But when I go to reports - dataset reports, there is no

value linked to this data element A…

I tried with several examples, this last one were the simplest, but I

still can not get any value with the report tools. A look at the
catalina.out log is showing me this line dealing with my report query:

"nested exception in org.postgresql.util.PSQLException: ERROR: Relation

“analytics_2013” doesn’t exist"

I’ve just run Analytics and datasmart – start exporting, as suggested by Knut (thanks to him) but the situation is the same. I shall also mention that when doing a reporting rate summary, I’m getting correct rates corresponding to the number of data I added.

I’m using version 2.13. Any idea of what is going wrong ? Where can I check in the postgresql database (which table to look in?) ?

Thanks for the help.

Regards
Ilias

Do your data show up in GIS, PIvot Tables or Data Visualizer?

···

On Sun, Dec 8, 2013 at 7:00 PM, Ilias Dine ilias.dine@gmail.com wrote:

Hi
Did it but still nothing on report.

If this can help figure out what’s going on:

  • In data administrator – data browser, when selecting period daily, begin and end dates covering the entry periods, browse by organization unit_ then selecting the root organization unit, I can see the values appearing on the result page
  • But when browsing by data sets, there is no result

I note also that : when selecting organization unit in browser type, a contextual select menu for organization units appears; no contextual select menu for data sets appears when choosing to browse by data set. Is this normal ?


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp


Knut Staring

Dept. of Informatics, University of Oslo

+4791880522

http://dhis2.org

2013/12/8 Lars Helge Øverland larshelge@gmail.com

Hi there,

go to data admin, resource tables and generate the resource tables there.

Then run analytics again, and verify that the process completes successfully.

The first step happens automatically for scheduled analytics processes, but not for ad-hoc. I think we could include a check in the first situation and generate them if not existing at all.

Lars

On Dec 7, 2013 2:47 PM, “Ilias Dine” ilias.dine@gmail.com wrote:

OK.
Here is the result of the query to the database

dhis2=> select dataelementid, periodid, sourceid, value from datavalue;

dataelementid | periodid | sourceid | value

---------------±---------±---------±------

49 | 51 | 48 | 1

49 | 52 | 48 | 2

49 | 53 | 48 | 5

(3 rows)

The values correspond to what has been set with data entry. But they still don’t appear in the dataset report…


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

2013/12/7 Knut Staring knutst@gmail.com

The data will be in datavalues

  1. des. 2013 14:02 skrev “Ilias Dine” ilias.dine@gmail.com følgende:

Hi

I’m testing DHIS2, the installation on ubuntu following the guide was

ok. I then tried to add data elements and visualize them with the
reports but couldn’t get this work. I even tried to add one simple data

element (named element A, value type: number, aggregation operator: sum

  • as default), then defined a data set (named collect, frequency: daily)

and associated the data element A to it. An organization unit named root
has been created and associated to this data set. Through the data entry

form I added some values for 4 days. I can see them when selecting back
the periods. But when I go to reports - dataset reports, there is no

value linked to this data element A…

I tried with several examples, this last one were the simplest, but I

still can not get any value with the report tools. A look at the
catalina.out log is showing me this line dealing with my report query:

"nested exception in org.postgresql.util.PSQLException: ERROR: Relation

“analytics_2013” doesn’t exist"

I’ve just run Analytics and datasmart – start exporting, as suggested by Knut (thanks to him) but the situation is the same. I shall also mention that when doing a reporting rate summary, I’m getting correct rates corresponding to the number of data I added.

I’m using version 2.13. Any idea of what is going wrong ? Where can I check in the postgresql database (which table to look in?) ?

Thanks for the help.

Regards
Ilias

No. There is nothing showing up. A look at the log gives this error message while attempting to display reports:

  • INFO 2013-12-09 13:27:52,808 Query failed, likely because the requested analytics table does not exist (JdbcAnalyticsManager.java [SimpleAsyncTaskExecutor-2])

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select “de”,“daily”,“uidlevel1”, sum(value) as value from analytics_2013 where “de” in (‘LXRTj3PA8JB’) and “daily” in (‘20131207’) and “uidlevel1” in (‘Jgk0dC2ysx1’) group by “de”,“daily”,“uidlevel1”]; nested exception is org.postgresql.util.PSQLException: ERROR: column « uidlevel1 » doesn’t exist

Seems like the problem is still with analytics_2013. At first, before running analytics, the error were no analytics_2013 table. Now it is with uidlevel1 column.

Below is the description of the analytics_2013 table under my postgres database:

dhis2=> \d analytics_2013

Table “public.analytics_2013”

Column | Type | Modifiers

···

2013/12/9 Knut Staring knutst@gmail.com

Do your data show up in GIS, PIvot Tables or Data Visualizer?

On Sun, Dec 8, 2013 at 7:00 PM, Ilias Dine ilias.dine@gmail.com wrote:

Hi
Did it but still nothing on report.

If this can help figure out what’s going on:

  • In data administrator – data browser, when selecting period daily, begin and end dates covering the entry periods, browse by organization unit_ then selecting the root organization unit, I can see the values appearing on the result page
  • But when browsing by data sets, there is no result

I note also that : when selecting organization unit in browser type, a contextual select menu for organization units appears; no contextual select menu for data sets appears when choosing to browse by data set. Is this normal ?


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Knut Staring

Dept. of Informatics, University of Oslo

+4791880522

http://dhis2.org

2013/12/8 Lars Helge Øverland larshelge@gmail.com

Hi there,

go to data admin, resource tables and generate the resource tables there.

Then run analytics again, and verify that the process completes successfully.

The first step happens automatically for scheduled analytics processes, but not for ad-hoc. I think we could include a check in the first situation and generate them if not existing at all.

Lars

On Dec 7, 2013 2:47 PM, “Ilias Dine” ilias.dine@gmail.com wrote:

OK.
Here is the result of the query to the database

dhis2=> select dataelementid, periodid, sourceid, value from datavalue;

dataelementid | periodid | sourceid | value

---------------±---------±---------±------

49 | 51 | 48 | 1

49 | 52 | 48 | 2

49 | 53 | 48 | 5

(3 rows)

The values correspond to what has been set with data entry. But they still don’t appear in the dataset report…


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

2013/12/7 Knut Staring knutst@gmail.com

The data will be in datavalues

  1. des. 2013 14:02 skrev “Ilias Dine” ilias.dine@gmail.com følgende:

Hi

I’m testing DHIS2, the installation on ubuntu following the guide was

ok. I then tried to add data elements and visualize them with the
reports but couldn’t get this work. I even tried to add one simple data

element (named element A, value type: number, aggregation operator: sum

  • as default), then defined a data set (named collect, frequency: daily)

and associated the data element A to it. An organization unit named root
has been created and associated to this data set. Through the data entry

form I added some values for 4 days. I can see them when selecting back
the periods. But when I go to reports - dataset reports, there is no

value linked to this data element A…

I tried with several examples, this last one were the simplest, but I

still can not get any value with the report tools. A look at the
catalina.out log is showing me this line dealing with my report query:

"nested exception in org.postgresql.util.PSQLException: ERROR: Relation

“analytics_2013” doesn’t exist"

I’ve just run Analytics and datasmart – start exporting, as suggested by Knut (thanks to him) but the situation is the same. I shall also mention that when doing a reporting rate summary, I’m getting correct rates corresponding to the number of data I added.

I’m using version 2.13. Any idea of what is going wrong ? Where can I check in the postgresql database (which table to look in?) ?

Thanks for the help.

Regards
Ilias

Hi,

I have had this problem in the past. The following helped:

  1. Go to Maintenance -> Organisation Unit

  2. Select Organisation Unit Level

  3. You will see you Levels (1, 2, 3 etc) and Level Name (Level 1, Level 2 etc)

  4. Change the level names to either 1,2, 3 etc or National, Province, District etc

  5. Rerun Analytics

These steps have helped to deal with this issue in the past. Hope you find it useful.

Best regards,

Busoye

···

2013/12/9 Knut Staring knutst@gmail.com

Do your data show up in GIS, PIvot Tables or Data Visualizer?

On Sun, Dec 8, 2013 at 7:00 PM, Ilias Dine ilias.dine@gmail.com wrote:

Hi
Did it but still nothing on report.

If this can help figure out what’s going on:

  • In data administrator – data browser, when selecting period daily, begin and end dates covering the entry periods, browse by organization unit_ then selecting the root organization unit, I can see the values appearing on the result page
  • But when browsing by data sets, there is no result

I note also that : when selecting organization unit in browser type, a contextual select menu for organization units appears; no contextual select menu for data sets appears when choosing to browse by data set. Is this normal ?


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Knut Staring

Dept. of Informatics, University of Oslo

+4791880522

http://dhis2.org

2013/12/8 Lars Helge Øverland larshelge@gmail.com

Hi there,

go to data admin, resource tables and generate the resource tables there.

Then run analytics again, and verify that the process completes successfully.

The first step happens automatically for scheduled analytics processes, but not for ad-hoc. I think we could include a check in the first situation and generate them if not existing at all.

Lars

On Dec 7, 2013 2:47 PM, “Ilias Dine” ilias.dine@gmail.com wrote:

OK.
Here is the result of the query to the database

dhis2=> select dataelementid, periodid, sourceid, value from datavalue;

dataelementid | periodid | sourceid | value

---------------±---------±---------±------

49 | 51 | 48 | 1

49 | 52 | 48 | 2

49 | 53 | 48 | 5

(3 rows)

The values correspond to what has been set with data entry. But they still don’t appear in the dataset report…


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

2013/12/7 Knut Staring knutst@gmail.com

The data will be in datavalues

  1. des. 2013 14:02 skrev “Ilias Dine” ilias.dine@gmail.com følgende:

Hi

I’m testing DHIS2, the installation on ubuntu following the guide was

ok. I then tried to add data elements and visualize them with the
reports but couldn’t get this work. I even tried to add one simple data

element (named element A, value type: number, aggregation operator: sum

  • as default), then defined a data set (named collect, frequency: daily)

and associated the data element A to it. An organization unit named root
has been created and associated to this data set. Through the data entry

form I added some values for 4 days. I can see them when selecting back
the periods. But when I go to reports - dataset reports, there is no

value linked to this data element A…

I tried with several examples, this last one were the simplest, but I

still can not get any value with the report tools. A look at the
catalina.out log is showing me this line dealing with my report query:

"nested exception in org.postgresql.util.PSQLException: ERROR: Relation

“analytics_2013” doesn’t exist"

I’ve just run Analytics and datasmart – start exporting, as suggested by Knut (thanks to him) but the situation is the same. I shall also mention that when doing a reporting rate summary, I’m getting correct rates corresponding to the number of data I added.

I’m using version 2.13. Any idea of what is going wrong ? Where can I check in the postgresql database (which table to look in?) ?

Thanks for the help.

Regards
Ilias

Thanks Adebusoye. It helps me fix this problem too.
What is curious is that I created then a second sublevel organization unit, inserted data through data entry for this ou, ran the analytics, and nothing came out for this second ou…

I applied the same process you described and it worked.

Is it because there is a space in the level name ?

Or is there something we missed ?

···

2013/12/9 Adebusoye Anifalaje busoyetolu@gmail.com

Hi,

I have had this problem in the past. The following helped:

  1. Go to Maintenance -> Organisation Unit
  1. Select Organisation Unit Level
  1. You will see you Levels (1, 2, 3 etc) and Level Name (Level 1, Level 2 etc)
  1. Change the level names to either 1,2, 3 etc or National, Province, District etc
  1. Rerun Analytics

These steps have helped to deal with this issue in the past. Hope you find it useful.

Best regards,

Busoye

On 9 Dec 2013, at 10:39, Ilias Dine ilias.dine@gmail.com wrote:

No. There is nothing showing up. A look at the log gives this error message while attempting to display reports:

  • INFO 2013-12-09 13:27:52,808 Query failed, likely because the requested analytics table does not exist (JdbcAnalyticsManager.java [SimpleAsyncTaskExecutor-2])

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select “de”,“daily”,“uidlevel1”, sum(value) as value from analytics_2013 where “de” in (‘LXRTj3PA8JB’) and “daily” in (‘20131207’) and “uidlevel1” in (‘Jgk0dC2ysx1’) group by “de”,“daily”,“uidlevel1”]; nested exception is org.postgresql.util.PSQLException: ERROR: column « uidlevel1 » doesn’t exist

Seems like the problem is still with analytics_2013. At first, before running analytics, the error were no analytics_2013 table. Now it is with uidlevel1 column.

Below is the description of the analytics_2013 table under my postgres database:

dhis2=> \d analytics_2013

Table “public.analytics_2013”

Column | Type | Modifiers

----------------±----------------------±----------

daily | character varying(10) |

weekly | character varying(10) |

monthly | character varying(10) |

bimonthly | character varying(10) |

quarterly | character varying(10) |

sixmonthly | character varying(10) |

yearly | character varying(10) |

financialapril | character varying(10) |

financialjuly | character varying(10) |

financialoct | character varying(10) |

de | character(11) | not null

co | character(11) | not null

level | integer |

daysxvalue | double precision |

daysno | integer | not null

value | double precision |

Indexes:

“in_co_analytics_temp_2013_funkwriazlc” btree (co)

“in_de_analytics_temp_2013_d5ld7ghglkc” btree (de)

“in_level_analytics_temp_2013_whuwwsoenp9” btree (level)

And I can’t see uidlevel1 column in fact…


Mailing list: https://launchpad.net/~dhis2-users
Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users
More help : https://help.launchpad.net/ListHelp

2013/12/9 Knut Staring knutst@gmail.com

Do your data show up in GIS, PIvot Tables or Data Visualizer?

On Sun, Dec 8, 2013 at 7:00 PM, Ilias Dine ilias.dine@gmail.com wrote:

Hi
Did it but still nothing on report.

If this can help figure out what’s going on:

  • In data administrator – data browser, when selecting period daily, begin and end dates covering the entry periods, browse by organization unit_ then selecting the root organization unit, I can see the values appearing on the result page
  • But when browsing by data sets, there is no result

I note also that : when selecting organization unit in browser type, a contextual select menu for organization units appears; no contextual select menu for data sets appears when choosing to browse by data set. Is this normal ?


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Knut Staring

Dept. of Informatics, University of Oslo

+4791880522

http://dhis2.org

2013/12/8 Lars Helge Øverland larshelge@gmail.com

Hi there,

go to data admin, resource tables and generate the resource tables there.

Then run analytics again, and verify that the process completes successfully.

The first step happens automatically for scheduled analytics processes, but not for ad-hoc. I think we could include a check in the first situation and generate them if not existing at all.

Lars

On Dec 7, 2013 2:47 PM, “Ilias Dine” ilias.dine@gmail.com wrote:

OK.
Here is the result of the query to the database

dhis2=> select dataelementid, periodid, sourceid, value from datavalue;

dataelementid | periodid | sourceid | value

---------------±---------±---------±------

49 | 51 | 48 | 1

49 | 52 | 48 | 2

49 | 53 | 48 | 5

(3 rows)

The values correspond to what has been set with data entry. But they still don’t appear in the dataset report…


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

2013/12/7 Knut Staring knutst@gmail.com

The data will be in datavalues

  1. des. 2013 14:02 skrev “Ilias Dine” ilias.dine@gmail.com følgende:

Hi

I’m testing DHIS2, the installation on ubuntu following the guide was

ok. I then tried to add data elements and visualize them with the
reports but couldn’t get this work. I even tried to add one simple data

element (named element A, value type: number, aggregation operator: sum

  • as default), then defined a data set (named collect, frequency: daily)

and associated the data element A to it. An organization unit named root
has been created and associated to this data set. Through the data entry

form I added some values for 4 days. I can see them when selecting back
the periods. But when I go to reports - dataset reports, there is no

value linked to this data element A…

I tried with several examples, this last one were the simplest, but I

still can not get any value with the report tools. A look at the
catalina.out log is showing me this line dealing with my report query:

"nested exception in org.postgresql.util.PSQLException: ERROR: Relation

“analytics_2013” doesn’t exist"

I’ve just run Analytics and datasmart – start exporting, as suggested by Knut (thanks to him) but the situation is the same. I shall also mention that when doing a reporting rate summary, I’m getting correct rates corresponding to the number of data I added.

I’m using version 2.13. Any idea of what is going wrong ? Where can I check in the postgresql database (which table to look in?) ?

Thanks for the help.

Regards
Ilias

It is a quirk in the way that the orgunitlevel table is generated.When you access it through the user interface the new level is generated. After that, the analytics process must be run again.

Regards,

Jason

–Sent from my mobile

···

2013/12/9 Adebusoye Anifalaje busoyetolu@gmail.com

Hi,

I have had this problem in the past. The following helped:

  1. Go to Maintenance → Organisation Unit
  1. Select Organisation Unit Level
  1. You will see you Levels (1, 2, 3 etc) and Level Name (Level 1, Level 2 etc)
  1. Change the level names to either 1,2, 3 etc or National, Province, District etc
  1. Rerun Analytics

These steps have helped to deal with this issue in the past. Hope you find it useful.

Best regards,

Busoye

On 9 Dec 2013, at 10:39, Ilias Dine ilias.dine@gmail.com wrote:

No. There is nothing showing up. A look at the log gives this error message while attempting to display reports:

  • INFO 2013-12-09 13:27:52,808 Query failed, likely because the requested analytics table does not exist (JdbcAnalyticsManager.java [SimpleAsyncTaskExecutor-2])

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select “de”,“daily”,“uidlevel1”, sum(value) as value from analytics_2013 where “de” in (‘LXRTj3PA8JB’) and “daily” in (‘20131207’) and “uidlevel1” in (‘Jgk0dC2ysx1’) group by “de”,“daily”,“uidlevel1”]; nested exception is org.postgresql.util.PSQLException: ERROR: column « uidlevel1 » doesn’t exist

Seems like the problem is still with analytics_2013. At first, before running analytics, the error were no analytics_2013 table. Now it is with uidlevel1 column.

Below is the description of the analytics_2013 table under my postgres database:

dhis2=> \d analytics_2013

Table “public.analytics_2013”

Column | Type | Modifiers

----------------±----------------------±----------

daily | character varying(10) |

weekly | character varying(10) |

monthly | character varying(10) |

bimonthly | character varying(10) |

quarterly | character varying(10) |

sixmonthly | character varying(10) |

yearly | character varying(10) |

financialapril | character varying(10) |

financialjuly | character varying(10) |

financialoct | character varying(10) |

de | character(11) | not null

co | character(11) | not null

level | integer |

daysxvalue | double precision |

daysno | integer | not null

value | double precision |

Indexes:

“in_co_analytics_temp_2013_funkwriazlc” btree (co)

“in_de_analytics_temp_2013_d5ld7ghglkc” btree (de)

“in_level_analytics_temp_2013_whuwwsoenp9” btree (level)

And I can’t see uidlevel1 column in fact…


Mailing list: https://launchpad.net/~dhis2-users
Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users
More help : https://help.launchpad.net/ListHelp

2013/12/9 Knut Staring knutst@gmail.com

Do your data show up in GIS, PIvot Tables or Data Visualizer?

On Sun, Dec 8, 2013 at 7:00 PM, Ilias Dine ilias.dine@gmail.com wrote:

Hi
Did it but still nothing on report.

If this can help figure out what’s going on:

  • In data administrator – data browser, when selecting period daily, begin and end dates covering the entry periods, browse by organization unit_ then selecting the root organization unit, I can see the values appearing on the result page
  • But when browsing by data sets, there is no result

I note also that : when selecting organization unit in browser type, a contextual select menu for organization units appears; no contextual select menu for data sets appears when choosing to browse by data set. Is this normal ?


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Knut Staring

Dept. of Informatics, University of Oslo

+4791880522

http://dhis2.org

2013/12/8 Lars Helge Øverland larshelge@gmail.com

Hi there,

go to data admin, resource tables and generate the resource tables there.

Then run analytics again, and verify that the process completes successfully.

The first step happens automatically for scheduled analytics processes, but not for ad-hoc. I think we could include a check in the first situation and generate them if not existing at all.

Lars

On Dec 7, 2013 2:47 PM, “Ilias Dine” ilias.dine@gmail.com wrote:

OK.
Here is the result of the query to the database

dhis2=> select dataelementid, periodid, sourceid, value from datavalue;

dataelementid | periodid | sourceid | value

---------------±---------±---------±------

49 | 51 | 48 | 1

49 | 52 | 48 | 2

49 | 53 | 48 | 5

(3 rows)

The values correspond to what has been set with data entry. But they still don’t appear in the dataset report…


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

2013/12/7 Knut Staring knutst@gmail.com

The data will be in datavalues

  1. des. 2013 14:02 skrev “Ilias Dine” ilias.dine@gmail.com følgende:

Hi

I’m testing DHIS2, the installation on ubuntu following the guide was

ok. I then tried to add data elements and visualize them with the
reports but couldn’t get this work. I even tried to add one simple data

element (named element A, value type: number, aggregation operator: sum

  • as default), then defined a data set (named collect, frequency: daily)

and associated the data element A to it. An organization unit named root
has been created and associated to this data set. Through the data entry

form I added some values for 4 days. I can see them when selecting back
the periods. But when I go to reports - dataset reports, there is no

value linked to this data element A…

I tried with several examples, this last one were the simplest, but I

still can not get any value with the report tools. A look at the
catalina.out log is showing me this line dealing with my report query:

"nested exception in org.postgresql.util.PSQLException: ERROR: Relation

“analytics_2013” doesn’t exist"

I’ve just run Analytics and datasmart – start exporting, as suggested by Knut (thanks to him) but the situation is the same. I shall also mention that when doing a reporting rate summary, I’m getting correct rates corresponding to the number of data I added.

I’m using version 2.13. Any idea of what is going wrong ? Where can I check in the postgresql database (which table to look in?) ?

Thanks for the help.

Regards
Ilias

Do the docs highlight well the fact that you HAVE to explicitly save the orgunit levels? It is not very intiutive. I always do it, because I know I cannot get GIS etc to work without it, but I’ve certainly forgotten to do it myself. It should be part of “Getting started”.

It is alluded to implicitly, but hidden in a lot of bla bla here:

http://www.dhis2.org/doc/snapshot/en/implementer/html/ch03s03.html#d5e150

http://www.dhis2.org/doc/snapshot/en/user/html/ch14.html#d5e1630

···

On Mon, Dec 9, 2013 at 3:38 PM, Jason Pickering jason.p.pickering@gmail.com wrote:

It is a quirk in the way that the orgunitlevel table is generated.When you access it through the user interface the new level is generated. After that, the analytics process must be run again.

Regards,

Jason

–Sent from my mobile

On Dec 9, 2013 4:04 PM, “Ilias Dine” ilias.dine@gmail.com wrote:

Thanks Adebusoye. It helps me fix this problem too.
What is curious is that I created then a second sublevel organization unit, inserted data through data entry for this ou, ran the analytics, and nothing came out for this second ou…

I applied the same process you described and it worked.

Is it because there is a space in the level name ?

Or is there something we missed ?


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp


Knut Staring

Dept. of Informatics, University of Oslo

+4791880522

http://dhis2.org

2013/12/9 Adebusoye Anifalaje busoyetolu@gmail.com

Hi,

I have had this problem in the past. The following helped:

  1. Go to Maintenance -> Organisation Unit
  1. Select Organisation Unit Level
  1. You will see you Levels (1, 2, 3 etc) and Level Name (Level 1, Level 2 etc)
  1. Change the level names to either 1,2, 3 etc or National, Province, District etc
  1. Rerun Analytics

These steps have helped to deal with this issue in the past. Hope you find it useful.

Best regards,

Busoye

On 9 Dec 2013, at 10:39, Ilias Dine ilias.dine@gmail.com wrote:

No. There is nothing showing up. A look at the log gives this error message while attempting to display reports:

  • INFO 2013-12-09 13:27:52,808 Query failed, likely because the requested analytics table does not exist (JdbcAnalyticsManager.java [SimpleAsyncTaskExecutor-2])

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select “de”,“daily”,“uidlevel1”, sum(value) as value from analytics_2013 where “de” in (‘LXRTj3PA8JB’) and “daily” in (‘20131207’) and “uidlevel1” in (‘Jgk0dC2ysx1’) group by “de”,“daily”,“uidlevel1”]; nested exception is org.postgresql.util.PSQLException: ERROR: column « uidlevel1 » doesn’t exist

Seems like the problem is still with analytics_2013. At first, before running analytics, the error were no analytics_2013 table. Now it is with uidlevel1 column.

Below is the description of the analytics_2013 table under my postgres database:

dhis2=> \d analytics_2013

Table “public.analytics_2013”

Column | Type | Modifiers

----------------±----------------------±----------

daily | character varying(10) |

weekly | character varying(10) |

monthly | character varying(10) |

bimonthly | character varying(10) |

quarterly | character varying(10) |

sixmonthly | character varying(10) |

yearly | character varying(10) |

financialapril | character varying(10) |

financialjuly | character varying(10) |

financialoct | character varying(10) |

de | character(11) | not null

co | character(11) | not null

level | integer |

daysxvalue | double precision |

daysno | integer | not null

value | double precision |

Indexes:

“in_co_analytics_temp_2013_funkwriazlc” btree (co)

“in_de_analytics_temp_2013_d5ld7ghglkc” btree (de)

“in_level_analytics_temp_2013_whuwwsoenp9” btree (level)

And I can’t see uidlevel1 column in fact…


Mailing list: https://launchpad.net/~dhis2-users
Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users
More help : https://help.launchpad.net/ListHelp

2013/12/9 Knut Staring knutst@gmail.com

Do your data show up in GIS, PIvot Tables or Data Visualizer?

On Sun, Dec 8, 2013 at 7:00 PM, Ilias Dine ilias.dine@gmail.com wrote:

Hi
Did it but still nothing on report.

If this can help figure out what’s going on:

  • In data administrator – data browser, when selecting period daily, begin and end dates covering the entry periods, browse by organization unit_ then selecting the root organization unit, I can see the values appearing on the result page
  • But when browsing by data sets, there is no result

I note also that : when selecting organization unit in browser type, a contextual select menu for organization units appears; no contextual select menu for data sets appears when choosing to browse by data set. Is this normal ?


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Knut Staring

Dept. of Informatics, University of Oslo

+4791880522

http://dhis2.org

2013/12/8 Lars Helge Øverland larshelge@gmail.com

Hi there,

go to data admin, resource tables and generate the resource tables there.

Then run analytics again, and verify that the process completes successfully.

The first step happens automatically for scheduled analytics processes, but not for ad-hoc. I think we could include a check in the first situation and generate them if not existing at all.

Lars

On Dec 7, 2013 2:47 PM, “Ilias Dine” ilias.dine@gmail.com wrote:

OK.
Here is the result of the query to the database

dhis2=> select dataelementid, periodid, sourceid, value from datavalue;

dataelementid | periodid | sourceid | value

---------------±---------±---------±------

49 | 51 | 48 | 1

49 | 52 | 48 | 2

49 | 53 | 48 | 5

(3 rows)

The values correspond to what has been set with data entry. But they still don’t appear in the dataset report…


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

2013/12/7 Knut Staring knutst@gmail.com

The data will be in datavalues

  1. des. 2013 14:02 skrev “Ilias Dine” ilias.dine@gmail.com følgende:

Hi

I’m testing DHIS2, the installation on ubuntu following the guide was

ok. I then tried to add data elements and visualize them with the
reports but couldn’t get this work. I even tried to add one simple data

element (named element A, value type: number, aggregation operator: sum

  • as default), then defined a data set (named collect, frequency: daily)

and associated the data element A to it. An organization unit named root
has been created and associated to this data set. Through the data entry

form I added some values for 4 days. I can see them when selecting back
the periods. But when I go to reports - dataset reports, there is no

value linked to this data element A…

I tried with several examples, this last one were the simplest, but I

still can not get any value with the report tools. A look at the
catalina.out log is showing me this line dealing with my report query:

"nested exception in org.postgresql.util.PSQLException: ERROR: Relation

“analytics_2013” doesn’t exist"

I’ve just run Analytics and datasmart – start exporting, as suggested by Knut (thanks to him) but the situation is the same. I shall also mention that when doing a reporting rate summary, I’m getting correct rates corresponding to the number of data I added.

I’m using version 2.13. Any idea of what is going wrong ? Where can I check in the postgresql database (which table to look in?) ?

Thanks for the help.

Regards
Ilias

It is far from clear but most clearly alluded to here.

"3.2.4. Organisation unit level

Here you specify a contextual name for each level in the hierarchy, e.g. “Country”, “Province”, “District”, “Health Facility”, and these names will be used all over the application where levels are referred to. This page will take some time to load if the orgunit hierarchy is very big."

The reason it takes so long to load,is because the orgunit levels are being determined each time this page is loaded. And yes, pressing “Save” is required.

Regards,

Jason

-Sent from my mobile

···

On Mon, Dec 9, 2013 at 3:38 PM, Jason Pickering jason.p.pickering@gmail.com wrote:

It is a quirk in the way that the orgunitlevel table is generated.When you access it through the user interface the new level is generated. After that, the analytics process must be run again.

Regards,

Jason

–Sent from my mobile

On Dec 9, 2013 4:04 PM, “Ilias Dine” ilias.dine@gmail.com wrote:

Thanks Adebusoye. It helps me fix this problem too.
What is curious is that I created then a second sublevel organization unit, inserted data through data entry for this ou, ran the analytics, and nothing came out for this second ou…

I applied the same process you described and it worked.

Is it because there is a space in the level name ?

Or is there something we missed ?


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp


Knut Staring

Dept. of Informatics, University of Oslo

+4791880522

http://dhis2.org

2013/12/9 Adebusoye Anifalaje busoyetolu@gmail.com

Hi,

I have had this problem in the past. The following helped:

  1. Go to Maintenance → Organisation Unit
  1. Select Organisation Unit Level
  1. You will see you Levels (1, 2, 3 etc) and Level Name (Level 1, Level 2 etc)
  1. Change the level names to either 1,2, 3 etc or National, Province, District etc
  1. Rerun Analytics

These steps have helped to deal with this issue in the past. Hope you find it useful.

Best regards,

Busoye

On 9 Dec 2013, at 10:39, Ilias Dine ilias.dine@gmail.com wrote:

No. There is nothing showing up. A look at the log gives this error message while attempting to display reports:

  • INFO 2013-12-09 13:27:52,808 Query failed, likely because the requested analytics table does not exist (JdbcAnalyticsManager.java [SimpleAsyncTaskExecutor-2])

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select “de”,“daily”,“uidlevel1”, sum(value) as value from analytics_2013 where “de” in (‘LXRTj3PA8JB’) and “daily” in (‘20131207’) and “uidlevel1” in (‘Jgk0dC2ysx1’) group by “de”,“daily”,“uidlevel1”]; nested exception is org.postgresql.util.PSQLException: ERROR: column « uidlevel1 » doesn’t exist

Seems like the problem is still with analytics_2013. At first, before running analytics, the error were no analytics_2013 table. Now it is with uidlevel1 column.

Below is the description of the analytics_2013 table under my postgres database:

dhis2=> \d analytics_2013

Table “public.analytics_2013”

Column | Type | Modifiers

----------------±----------------------±----------

daily | character varying(10) |

weekly | character varying(10) |

monthly | character varying(10) |

bimonthly | character varying(10) |

quarterly | character varying(10) |

sixmonthly | character varying(10) |

yearly | character varying(10) |

financialapril | character varying(10) |

financialjuly | character varying(10) |

financialoct | character varying(10) |

de | character(11) | not null

co | character(11) | not null

level | integer |

daysxvalue | double precision |

daysno | integer | not null

value | double precision |

Indexes:

“in_co_analytics_temp_2013_funkwriazlc” btree (co)

“in_de_analytics_temp_2013_d5ld7ghglkc” btree (de)

“in_level_analytics_temp_2013_whuwwsoenp9” btree (level)

And I can’t see uidlevel1 column in fact…


Mailing list: https://launchpad.net/~dhis2-users
Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users
More help : https://help.launchpad.net/ListHelp

2013/12/9 Knut Staring knutst@gmail.com

Do your data show up in GIS, PIvot Tables or Data Visualizer?

On Sun, Dec 8, 2013 at 7:00 PM, Ilias Dine ilias.dine@gmail.com wrote:

Hi
Did it but still nothing on report.

If this can help figure out what’s going on:

  • In data administrator – data browser, when selecting period daily, begin and end dates covering the entry periods, browse by organization unit_ then selecting the root organization unit, I can see the values appearing on the result page
  • But when browsing by data sets, there is no result

I note also that : when selecting organization unit in browser type, a contextual select menu for organization units appears; no contextual select menu for data sets appears when choosing to browse by data set. Is this normal ?


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Knut Staring

Dept. of Informatics, University of Oslo

+4791880522

http://dhis2.org

2013/12/8 Lars Helge Øverland larshelge@gmail.com

Hi there,

go to data admin, resource tables and generate the resource tables there.

Then run analytics again, and verify that the process completes successfully.

The first step happens automatically for scheduled analytics processes, but not for ad-hoc. I think we could include a check in the first situation and generate them if not existing at all.

Lars

On Dec 7, 2013 2:47 PM, “Ilias Dine” ilias.dine@gmail.com wrote:

OK.
Here is the result of the query to the database

dhis2=> select dataelementid, periodid, sourceid, value from datavalue;

dataelementid | periodid | sourceid | value

---------------±---------±---------±------

49 | 51 | 48 | 1

49 | 52 | 48 | 2

49 | 53 | 48 | 5

(3 rows)

The values correspond to what has been set with data entry. But they still don’t appear in the dataset report…


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

2013/12/7 Knut Staring knutst@gmail.com

The data will be in datavalues

  1. des. 2013 14:02 skrev “Ilias Dine” ilias.dine@gmail.com følgende:

Hi

I’m testing DHIS2, the installation on ubuntu following the guide was

ok. I then tried to add data elements and visualize them with the
reports but couldn’t get this work. I even tried to add one simple data

element (named element A, value type: number, aggregation operator: sum

  • as default), then defined a data set (named collect, frequency: daily)

and associated the data element A to it. An organization unit named root
has been created and associated to this data set. Through the data entry

form I added some values for 4 days. I can see them when selecting back
the periods. But when I go to reports - dataset reports, there is no

value linked to this data element A…

I tried with several examples, this last one were the simplest, but I

still can not get any value with the report tools. A look at the
catalina.out log is showing me this line dealing with my report query:

"nested exception in org.postgresql.util.PSQLException: ERROR: Relation

“analytics_2013” doesn’t exist"

I’ve just run Analytics and datasmart – start exporting, as suggested by Knut (thanks to him) but the situation is the same. I shall also mention that when doing a reporting rate summary, I’m getting correct rates corresponding to the number of data I added.

I’m using version 2.13. Any idea of what is going wrong ? Where can I check in the postgresql database (which table to look in?) ?

Thanks for the help.

Regards
Ilias

The user manual has been designed like a (extensive and very good) presentation of the DHIS2, but not like a howto guide for beginners. To be honest, I never would have guessed that we had to change the level names on the organisation unit level page. And I never would have guessed either that we had to run analytics before getting reports. Maybe this sould be explained somewhere on a getting started section as suggested by Knut.
Thanks again for the very reactive support of the members of this community.

Regards,

Ilias

···

2013/12/9 Jason Pickering jason.p.pickering@gmail.com

It is far from clear but most clearly alluded to here.

"3.2.4. Organisation unit level

Here you specify a contextual name for each level in the hierarchy, e.g. “Country”, “Province”, “District”, “Health Facility”, and these names will be used all over the application where levels are referred to. This page will take some time to load if the orgunit hierarchy is very big."

The reason it takes so long to load,is because the orgunit levels are being determined each time this page is loaded. And yes, pressing “Save” is required.

Regards,

Jason

-Sent from my mobile

On Dec 9, 2013 5:12 PM, “Knut Staring” knutst@gmail.com wrote:

Do the docs highlight well the fact that you HAVE to explicitly save the orgunit levels? It is not very intiutive. I always do it, because I know I cannot get GIS etc to work without it, but I’ve certainly forgotten to do it myself. It should be part of “Getting started”.

It is alluded to implicitly, but hidden in a lot of bla bla here:

http://www.dhis2.org/doc/snapshot/en/implementer/html/ch03s03.html#d5e150

http://www.dhis2.org/doc/snapshot/en/user/html/ch14.html#d5e1630

On Mon, Dec 9, 2013 at 3:38 PM, Jason Pickering jason.p.pickering@gmail.com wrote:

It is a quirk in the way that the orgunitlevel table is generated.When you access it through the user interface the new level is generated. After that, the analytics process must be run again.

Regards,

Jason

–Sent from my mobile

On Dec 9, 2013 4:04 PM, “Ilias Dine” ilias.dine@gmail.com wrote:

Thanks Adebusoye. It helps me fix this problem too.
What is curious is that I created then a second sublevel organization unit, inserted data through data entry for this ou, ran the analytics, and nothing came out for this second ou…

I applied the same process you described and it worked.

Is it because there is a space in the level name ?

Or is there something we missed ?


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp


Knut Staring

Dept. of Informatics, University of Oslo

+4791880522

http://dhis2.org

2013/12/9 Adebusoye Anifalaje busoyetolu@gmail.com

Hi,

I have had this problem in the past. The following helped:

  1. Go to Maintenance -> Organisation Unit
  1. Select Organisation Unit Level
  1. You will see you Levels (1, 2, 3 etc) and Level Name (Level 1, Level 2 etc)
  1. Change the level names to either 1,2, 3 etc or National, Province, District etc
  1. Rerun Analytics

These steps have helped to deal with this issue in the past. Hope you find it useful.

Best regards,

Busoye

On 9 Dec 2013, at 10:39, Ilias Dine ilias.dine@gmail.com wrote:

No. There is nothing showing up. A look at the log gives this error message while attempting to display reports:

  • INFO 2013-12-09 13:27:52,808 Query failed, likely because the requested analytics table does not exist (JdbcAnalyticsManager.java [SimpleAsyncTaskExecutor-2])

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select “de”,“daily”,“uidlevel1”, sum(value) as value from analytics_2013 where “de” in (‘LXRTj3PA8JB’) and “daily” in (‘20131207’) and “uidlevel1” in (‘Jgk0dC2ysx1’) group by “de”,“daily”,“uidlevel1”]; nested exception is org.postgresql.util.PSQLException: ERROR: column « uidlevel1 » doesn’t exist

Seems like the problem is still with analytics_2013. At first, before running analytics, the error were no analytics_2013 table. Now it is with uidlevel1 column.

Below is the description of the analytics_2013 table under my postgres database:

dhis2=> \d analytics_2013

Table “public.analytics_2013”

Column | Type | Modifiers

----------------±----------------------±----------

daily | character varying(10) |

weekly | character varying(10) |

monthly | character varying(10) |

bimonthly | character varying(10) |

quarterly | character varying(10) |

sixmonthly | character varying(10) |

yearly | character varying(10) |

financialapril | character varying(10) |

financialjuly | character varying(10) |

financialoct | character varying(10) |

de | character(11) | not null

co | character(11) | not null

level | integer |

daysxvalue | double precision |

daysno | integer | not null

value | double precision |

Indexes:

“in_co_analytics_temp_2013_funkwriazlc” btree (co)

“in_de_analytics_temp_2013_d5ld7ghglkc” btree (de)

“in_level_analytics_temp_2013_whuwwsoenp9” btree (level)

And I can’t see uidlevel1 column in fact…


Mailing list: https://launchpad.net/~dhis2-users
Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users
More help : https://help.launchpad.net/ListHelp

2013/12/9 Knut Staring knutst@gmail.com

Do your data show up in GIS, PIvot Tables or Data Visualizer?

On Sun, Dec 8, 2013 at 7:00 PM, Ilias Dine ilias.dine@gmail.com wrote:

Hi
Did it but still nothing on report.

If this can help figure out what’s going on:

  • In data administrator – data browser, when selecting period daily, begin and end dates covering the entry periods, browse by organization unit_ then selecting the root organization unit, I can see the values appearing on the result page
  • But when browsing by data sets, there is no result

I note also that : when selecting organization unit in browser type, a contextual select menu for organization units appears; no contextual select menu for data sets appears when choosing to browse by data set. Is this normal ?


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Knut Staring

Dept. of Informatics, University of Oslo

+4791880522

http://dhis2.org

2013/12/8 Lars Helge Øverland larshelge@gmail.com

Hi there,

go to data admin, resource tables and generate the resource tables there.

Then run analytics again, and verify that the process completes successfully.

The first step happens automatically for scheduled analytics processes, but not for ad-hoc. I think we could include a check in the first situation and generate them if not existing at all.

Lars

On Dec 7, 2013 2:47 PM, “Ilias Dine” ilias.dine@gmail.com wrote:

OK.
Here is the result of the query to the database

dhis2=> select dataelementid, periodid, sourceid, value from datavalue;

dataelementid | periodid | sourceid | value

---------------±---------±---------±------

49 | 51 | 48 | 1

49 | 52 | 48 | 2

49 | 53 | 48 | 5

(3 rows)

The values correspond to what has been set with data entry. But they still don’t appear in the dataset report…


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

2013/12/7 Knut Staring knutst@gmail.com

The data will be in datavalues

  1. des. 2013 14:02 skrev “Ilias Dine” ilias.dine@gmail.com følgende:

Hi

I’m testing DHIS2, the installation on ubuntu following the guide was

ok. I then tried to add data elements and visualize them with the
reports but couldn’t get this work. I even tried to add one simple data

element (named element A, value type: number, aggregation operator: sum

  • as default), then defined a data set (named collect, frequency: daily)

and associated the data element A to it. An organization unit named root
has been created and associated to this data set. Through the data entry

form I added some values for 4 days. I can see them when selecting back
the periods. But when I go to reports - dataset reports, there is no

value linked to this data element A…

I tried with several examples, this last one were the simplest, but I

still can not get any value with the report tools. A look at the
catalina.out log is showing me this line dealing with my report query:

"nested exception in org.postgresql.util.PSQLException: ERROR: Relation

“analytics_2013” doesn’t exist"

I’ve just run Analytics and datasmart – start exporting, as suggested by Knut (thanks to him) but the situation is the same. I shall also mention that when doing a reporting rate summary, I’m getting correct rates corresponding to the number of data I added.

I’m using version 2.13. Any idea of what is going wrong ? Where can I check in the postgresql database (which table to look in?) ?

Thanks for the help.

Regards
Ilias

Just wanted to underscore to everyone reading this thread and similar ones, that the manual is always work in progress as DHIS2 develops, and is also very much a work of collaboration: Though more structured than a wiki, it is also similar in that anyone who has a bit of time and motivation is welcome to help improve it. Contributions can be of any size, from correcting minor errors and spellings and clarifying language to adding sections and restructuring the way things are presented.

There is a separate guide to how to contribute to the documentation here (also available in Spanish):

http://www.dhis2.org/doc/snapshot/en/user/html/apa.html

···

On Mon, Dec 9, 2013 at 5:26 PM, Ilias Dine ilias.dine@gmail.com wrote:

The user manual has been designed like a (extensive and very good) presentation of the DHIS2, but not like a howto guide for beginners. To be honest, I never would have guessed that we had to change the level names on the organisation unit level page. And I never would have guessed either that we had to run analytics before getting reports. Maybe this sould be explained somewhere on a getting started section as suggested by Knut.
Thanks again for the very reactive support of the members of this community.

Regards,

Ilias


Knut Staring

Dept. of Informatics, University of Oslo

+4791880522

http://dhis2.org

2013/12/9 Jason Pickering jason.p.pickering@gmail.com

It is far from clear but most clearly alluded to here.

"3.2.4. Organisation unit level

Here you specify a contextual name for each level in the hierarchy, e.g. “Country”, “Province”, “District”, “Health Facility”, and these names will be used all over the application where levels are referred to. This page will take some time to load if the orgunit hierarchy is very big."

The reason it takes so long to load,is because the orgunit levels are being determined each time this page is loaded. And yes, pressing “Save” is required.

Regards,

Jason

-Sent from my mobile

On Dec 9, 2013 5:12 PM, “Knut Staring” knutst@gmail.com wrote:

Do the docs highlight well the fact that you HAVE to explicitly save the orgunit levels? It is not very intiutive. I always do it, because I know I cannot get GIS etc to work without it, but I’ve certainly forgotten to do it myself. It should be part of “Getting started”.

It is alluded to implicitly, but hidden in a lot of bla bla here:

http://www.dhis2.org/doc/snapshot/en/implementer/html/ch03s03.html#d5e150

http://www.dhis2.org/doc/snapshot/en/user/html/ch14.html#d5e1630

On Mon, Dec 9, 2013 at 3:38 PM, Jason Pickering jason.p.pickering@gmail.com wrote:

It is a quirk in the way that the orgunitlevel table is generated.When you access it through the user interface the new level is generated. After that, the analytics process must be run again.

Regards,

Jason

–Sent from my mobile

On Dec 9, 2013 4:04 PM, “Ilias Dine” ilias.dine@gmail.com wrote:

Thanks Adebusoye. It helps me fix this problem too.
What is curious is that I created then a second sublevel organization unit, inserted data through data entry for this ou, ran the analytics, and nothing came out for this second ou…

I applied the same process you described and it worked.

Is it because there is a space in the level name ?

Or is there something we missed ?


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp


Knut Staring

Dept. of Informatics, University of Oslo

+4791880522

http://dhis2.org

2013/12/9 Adebusoye Anifalaje busoyetolu@gmail.com

Hi,

I have had this problem in the past. The following helped:

  1. Go to Maintenance -> Organisation Unit
  1. Select Organisation Unit Level
  1. You will see you Levels (1, 2, 3 etc) and Level Name (Level 1, Level 2 etc)
  1. Change the level names to either 1,2, 3 etc or National, Province, District etc
  1. Rerun Analytics

These steps have helped to deal with this issue in the past. Hope you find it useful.

Best regards,

Busoye

On 9 Dec 2013, at 10:39, Ilias Dine ilias.dine@gmail.com wrote:

No. There is nothing showing up. A look at the log gives this error message while attempting to display reports:

  • INFO 2013-12-09 13:27:52,808 Query failed, likely because the requested analytics table does not exist (JdbcAnalyticsManager.java [SimpleAsyncTaskExecutor-2])

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select “de”,“daily”,“uidlevel1”, sum(value) as value from analytics_2013 where “de” in (‘LXRTj3PA8JB’) and “daily” in (‘20131207’) and “uidlevel1” in (‘Jgk0dC2ysx1’) group by “de”,“daily”,“uidlevel1”]; nested exception is org.postgresql.util.PSQLException: ERROR: column « uidlevel1 » doesn’t exist

Seems like the problem is still with analytics_2013. At first, before running analytics, the error were no analytics_2013 table. Now it is with uidlevel1 column.

Below is the description of the analytics_2013 table under my postgres database:

dhis2=> \d analytics_2013

Table “public.analytics_2013”

Column | Type | Modifiers

----------------±----------------------±----------

daily | character varying(10) |

weekly | character varying(10) |

monthly | character varying(10) |

bimonthly | character varying(10) |

quarterly | character varying(10) |

sixmonthly | character varying(10) |

yearly | character varying(10) |

financialapril | character varying(10) |

financialjuly | character varying(10) |

financialoct | character varying(10) |

de | character(11) | not null

co | character(11) | not null

level | integer |

daysxvalue | double precision |

daysno | integer | not null

value | double precision |

Indexes:

“in_co_analytics_temp_2013_funkwriazlc” btree (co)

“in_de_analytics_temp_2013_d5ld7ghglkc” btree (de)

“in_level_analytics_temp_2013_whuwwsoenp9” btree (level)

And I can’t see uidlevel1 column in fact…


Mailing list: https://launchpad.net/~dhis2-users
Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users
More help : https://help.launchpad.net/ListHelp

2013/12/9 Knut Staring knutst@gmail.com

Do your data show up in GIS, PIvot Tables or Data Visualizer?

On Sun, Dec 8, 2013 at 7:00 PM, Ilias Dine ilias.dine@gmail.com wrote:

Hi
Did it but still nothing on report.

If this can help figure out what’s going on:

  • In data administrator – data browser, when selecting period daily, begin and end dates covering the entry periods, browse by organization unit_ then selecting the root organization unit, I can see the values appearing on the result page
  • But when browsing by data sets, there is no result

I note also that : when selecting organization unit in browser type, a contextual select menu for organization units appears; no contextual select menu for data sets appears when choosing to browse by data set. Is this normal ?


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

Knut Staring

Dept. of Informatics, University of Oslo

+4791880522

http://dhis2.org

2013/12/8 Lars Helge Øverland larshelge@gmail.com

Hi there,

go to data admin, resource tables and generate the resource tables there.

Then run analytics again, and verify that the process completes successfully.

The first step happens automatically for scheduled analytics processes, but not for ad-hoc. I think we could include a check in the first situation and generate them if not existing at all.

Lars

On Dec 7, 2013 2:47 PM, “Ilias Dine” ilias.dine@gmail.com wrote:

OK.
Here is the result of the query to the database

dhis2=> select dataelementid, periodid, sourceid, value from datavalue;

dataelementid | periodid | sourceid | value

---------------±---------±---------±------

49 | 51 | 48 | 1

49 | 52 | 48 | 2

49 | 53 | 48 | 5

(3 rows)

The values correspond to what has been set with data entry. But they still don’t appear in the dataset report…


Mailing list: https://launchpad.net/~dhis2-users

Post to : dhis2-users@lists.launchpad.net

Unsubscribe : https://launchpad.net/~dhis2-users

More help : https://help.launchpad.net/ListHelp

2013/12/7 Knut Staring knutst@gmail.com

The data will be in datavalues

  1. des. 2013 14:02 skrev “Ilias Dine” ilias.dine@gmail.com følgende:

Hi

I’m testing DHIS2, the installation on ubuntu following the guide was

ok. I then tried to add data elements and visualize them with the
reports but couldn’t get this work. I even tried to add one simple data

element (named element A, value type: number, aggregation operator: sum

  • as default), then defined a data set (named collect, frequency: daily)

and associated the data element A to it. An organization unit named root
has been created and associated to this data set. Through the data entry

form I added some values for 4 days. I can see them when selecting back
the periods. But when I go to reports - dataset reports, there is no

value linked to this data element A…

I tried with several examples, this last one were the simplest, but I

still can not get any value with the report tools. A look at the
catalina.out log is showing me this line dealing with my report query:

"nested exception in org.postgresql.util.PSQLException: ERROR: Relation

“analytics_2013” doesn’t exist"

I’ve just run Analytics and datasmart – start exporting, as suggested by Knut (thanks to him) but the situation is the same. I shall also mention that when doing a reporting rate summary, I’m getting correct rates corresponding to the number of data I added.

I’m using version 2.13. Any idea of what is going wrong ? Where can I check in the postgresql database (which table to look in?) ?

Thanks for the help.

Regards
Ilias