Hi @jan ,
We have a report which need to shows ‘Element - Sum(Value)’ for a given period. Now, when we export the raw SQL we see that the query is in the following form -
select <fields> from datavalue dv inner join dataelement de on
dv.dataelementid = de.dataelementid inner join period pe on
dv.periodid = pe.periodid inner join periodtype pt on
pe.periodtypeid = pt.periodtypeid inner join organisationunit ou
on dv.sourceid = ou.organisationunitid inner join categoryoptioncombo coc
on dv.categoryoptioncomboid = coc.categoryoptioncomboid inner join
categoryoptioncombo aoc on dv.attributeoptioncomboid = aoc.categoryoptioncomboid
where dv.dataelementid in (7524,6432,6462,8358,5393,6667,8382,5741,6383,5885...)
We actually have selected the elements from an ‘Element Group’ having N number of elements. 2 major issues here -
The report is kind of statically bound to the ‘previously selected N element IDs’ - so adding more elements in the element group does not have any effect. We have to come to each and every report containing those element groups (interesting, when you come back to the visualiser design, how do you know that it was from that group?), find the new element and add it in the report.
We found a limit of around 400 elements can be added in a report because of the SQL command length limitation, and you understand having an ‘in’ clause in an SQL with this many values is not efficient also.
It is just my assumption that the SQL command is created and then executed to have the analytics hold the data and then show it in reports. I can propose 2 things -
A. Just before execution if the SQL can be updated then it can be changed to -
select <fields> from <table list> where Elementid in (<select elements from specific group as subquery>)
This would solve issue 1 and 2 above.
B. The long term option can be in the visualiser design where if you find that someone selected an element group and selected all of those - we can mark that all elements of that group are requested, and we make the sql as option A by default.
Expecting your valuable feedback.