My team recently updated our development instance to v2.37.8 and all our pivot tables that included long text type data elements now present with the following error in the Data Visualizer app even though the aggregation type is “By data element”. This is not an issue in our production instance which is on v2.35.10.
We need to use these pivot tables in dashboards to share the data values from our aggregate data entry forms with other users and to easily compare data values across organisation units.
Has anyone else encountered this and do you have a solution for how to get the long text to appear in the pivot tables?
@aroger44 haven’t seen this, but we are also upgrading from 2.35 to 2.37 and so will try and replicate. Can you isolate at what point the data elements (i.e. how many characters) do/do not function? Is this specific to pivot tables?
Thanks @Matthew_Boddie for looking in to this. I tested creating a new data element of the “long text” type and did not submit any data for it and this issue still occurred. So it happens even when the text has 0 characters. I tested and this is not an issue with just “text” type data elements.
I have not experienced this issue before, but apparently, you have a problem with 14 Data elements in displaying long text in pivot tables.
I will suggest for you hereunder workaround solution, and I believe this will solve your issue:
1- Identify the data elements by using: yourdomainaddress.com/api/identifiableObjects/gMI8MX5BK2M
to get the specific data elements that have problems.
2- After identifying all 14 data elements, you can use the event reports App instead of the pivot table App, and I am pretty sure the long text will appear but make sure to select table style → line list and Output type–> Event and select the program (See hereunder snapshot), and then search for the data elements that have the long text and here we go you can see the results displayed with no problems:
Please try and please do not hesitate to DM me if you need further assistance.
I think the solution with event reports won’t work because she said the data elements are aggregate type, not tracker/event.
Thanks for your input you are correct I did not realize that.
@aroger44, I have reproduced the same issue on my end, and what i did is I changed the table options as indicated below:
Or select the Last value option if the Data element option is not working:
Please try the above, and let us know how it goes.
Thank you @ayman.tuffaha for looking in to this. We do not use the pivot table app anymore, we are using the pivot tables in the data visualizer app, and using the data visualizer app, when I change the aggregation type to “last value” I am still running in to the same error.
I did test changing a “long text” data element to a “text” data element and that appears to have solved the problem, so I wonder if the solution is to just change the type of all our long text data elements, tedious as that may be.
I believe it should display the long text values without the need to change the value type from long text to text, but anyway, I want to make sure that you have a satisfactory solution. If you do not feel that your situation has been resolved, please feel free to open a JIRA issue at: https://jira.dhis2.org/ Otherwise, please let me know how I can be of assistance at this point.
I just did upgrade from 2.35.8 to 184.108.40.206 and experiencing the same error even in the SL demo site is the same error. Changing the long text to text is only a solution to the reporting part. Because when changing the value type, the input form also shrinks and compromises the data quality
Thank you @Mahlatse_Mokoena for the post, and welcome to the community!
So the solution above worked partially? Could we get the steps to reproduce the issue in any of the play.dhis2.org instances? If you can’t reproduce it, would you please share screenshots that will help understand the issue.
@Gassim Thank you for looking in to this. In the play.dhis2.org instance DHIS 2 Demo - Sierra Leone I was able to recreate the issue by going to the data visualizer app, selecting “pivot table” as my visualization type, and then when I selected the data element “Project: Narrative” I received this error
Thanks for looking into this. This error seems to be because data element “Project narrative” is of the value type File, not long text.
Try one of the “Malaria/Measles/Cholera” Narrative DEs and it should up in the pivot tables. (See DHIS 2 Demo - Sierra Leone)
Maybe it is more of an upgrade issue since this happens when you go from 2.35 to 2.37? Can you try creating a new data element with the long text value type in your instance and see if the same issue
still persists, or if it is just with data elements that existed before the upgrade?
I am testing out an upgrade to 2.38 and have run into the same issue. This is a big problem for us as we have a lot of dashboards with a lot of long text information in pivot tables. Changing the data elements from “long text” to “text” type is not an option as we need the character limits of the long text for our data entry. Has anyone found another solution?
Are you using aggregate or tracker? Maybe validation rules or program rules could help?
Hi, we are using aggregate. I’m not sure how validation rules could help, we don’t have an issue on the data entry side, the problem is that the pivot tables won’t display the data that has been entered if it’s a long text type.
Does anyone know of a potential timeline for this issue to be resolved? We were looking forward to upgrading for the new features but we won’t be able to until this is resolved.
Sorry misunderstood the question! I thought the requirement was to limit the number of characters not the opposite.
@Guerra-Arias_Maria, it would help to know more details about the issue! Please help debug the issue more so we could open a Jira bug issue if it’s a bug and follow up!
Thanks for continuing to look in to this. The issue persists even when I create new data elements of the long text type after updating the instance. Could you please clarify what more detail you are looking for? The issue is that using aggregate data, when I attempt to create a pivot table in the data visualizer app, I receive the error that the data element must be of a value and a type that allow aggregation. This occurs even if the advanced option aggregation type is “By data element” or by “last value”. There is an open Jira issue here
The steps to replicate the error are as follows:
- Create data element
- Domain type: aggregated
- Value type: LONG_TEXT
- Create data set
- Assign the Data Element into Dataset
- Capture dummy data
- Run analytics
- Open data visualizer app and run a report
The error is clear you cannot aggregate text. I cannot change my DB now, it has been in existence since 2017. It will be impossible to approach the Ministry and tell them that I think I am going to have an even capture now, especially since most user think the issue is with the visualizer app. I have done testing in SL demo 2.39.1 same error. I am on 2.36, the risk are vulnerabilities that may be identified and be forced to upgrade because my version is no longer supported.
@Mahlatse_Mokoena1 welcome to the community!
I’m doing some testing, and thanks for the steps to reproduce the issue. When in the Data Visualizer app, if I use a pivot table, the text appears, but if I use column chart for example then it shows an error.
Are you trying to aggregate the text and show it in column format?
Why change the DB? Please clarify the issue.
Dear @Mahlatse_Mokoena1 ,
This may sound like very bad advice, but it is worth trying. In another system, we noticed that if we set the “Aggregation type” to “SUM” (instead of None) then text data elements could be displayed in the Pivot Tables. I am not sure this is the same problem, but it might be worth checking.
It is not possible to aggregate text with DHIS2 though. So, your Pivot Table must be constructed with the same dimensions as the data was collected at. Otherwise, you will only get the “first” data value. Which data value is the first, is not deterministic.
Displaying text is known to work in other systems though, so I am pretty sure it should be possible!