Question

please help me to write oql or suggest different way to acheieve expected result

0

hello, i have one entity which having two attributes 1. phase_type(string), 2.status(enumeration open,closed,pending). so my task is to generate bar graph based on open, closed and pending count respective to each phase type for that i have created one non persistable entity which having four attributes please see below image i am trying to count phase_status eg. user1 having how much open status, how much closed & pending status and trying to store it in non persistable table . i dont know where i am going correct or not to achive result so please suggest me the OQL query to achieve the result or any other way Check error below in data set.

asked 2021-06-24

Darshana Gunjal

2 answers

Shekhar Tyagi · Accepted Answer · 2021-06-25

SELECT Sum(OpenCount), Sum(ClosedCount), Sum(PendingCount), Phase_Types
FROM
(
SELECT Count(*) OpenCount, '0' ClosedCount, '0' PendingCount, Phase_Types
FROM Table
where status='Open'
Group by Phase_Types
UNION
SELECT '0' OpenCount, Count(*) ClosedCount, '0' PendingCount, Phase_Types
FROM Table
where status='Closed'
Group by Phase_Types
UNION
SELECT '0' OpenCount, '0' ClosedCount, Count(*) PendingCount, Phase_Types
FROM Table
where status='Pending'
Group by Phase_Types
)
GROUP BY Phase_Types

Vijayabharathi V · Accepted Answer · 2021-06-25

Hi Darshana Gunjal,

We can achieve the same using microflows too. @Shekhar Tyagi suggested an efficient way to achieve it. Grouped bar charts will helps you to display the different Status count for each phase-type. We can achieve it by using the AnyChart widget and Plotly.js libraries. For your reference, I will share the sample screenshots.

Doc link: https://docs.mendix.com/appstore/modules/any-chart

Result :

Thanks and regards,

Vijayabharathi V