[mohamad]

Hello Experts
First of all, I should mention that I tried to find similar question. however I could not find that. Please accept my apology, if my question is repetitive. I would be appreciated, if you give me a link about this subject.
One of the most important reports for every managers is KPI. The fact that, Community Edition does not have this valuable feature. Is that possible to get planning time and actual time from PM according to the users (Separately)
That would be great, If it will be specified according to the processes' name, too.
Looking forward to your comments.
Kind Regards

[amosbatto]

If you aren't going to pay for the Enterprise Edition, you are better off using an external reports tool like JasperReports or Crystal Reports to query the database and generate the report.

You can query the APP_CACHE_VIEW table like this in the workspace's database:
SELECT DEL_INIT_DATE, DEL_FINISH_DATE, DEL_TASK_DUE_DATE, DEL_DEL_RISK_DATE, THREAD_STATUS FROM APP_CACHE_VIEW WHERE APP_CURRENT_USER='Doe John' AND APP_PRO_TITLE='My Process Title' AND APP_TAS_TITLE='Title 1';

See: https://wiki.processmaker.com/3.0/Consu ... yAdmin_GUI

[mohamad]

Thanks a lot, I'll study your links to solve my issue.

[mohamad]

Dear Amosbatto,
This table is really excellent for reporting. (APP_CACHE_VIEW)
If I want to have the efficiency for users (Planning duration of user / Actual duration of users or something like this), how can I do that? In PM's tables, is this available?
Another question is that, how can I access the Advanced Search information which are in mysql? Some valuable information are available in (Home/Advanced Search). Those are excellent for charts. How can I access them from mysql?
At the moment, I connected PM's database to some BI tools like Report server and Jasper. and I do not know, how can I access them?
I was wondering if you could help me to find a good solution.
Looking forward to your valuable comments
Kind Regards,

[amosbatto]

If I want to have the efficiency for users (Planning duration of user / Actual duration of users or something like this), how can I do that? In PM's tables, is this available?

If you use the Enterprise Edition, see:
https://wiki.processmaker.com/3.0/StrategicDashboards
https://wiki.processmaker.com/3.2/Data_Reporting_Tools
https://wiki.processmaker.com/3.0/Advanced_Dashboard

In the Community Edition, you can figure out the difference between expected time and actual time to complete tasks by looking DEL_FINISH_DATE and DEL_TASK_DUE_DATE. You can create a plugin or process in PM or create an external script that will look up this info in APP_CACHE_VIEW and write the difference to a PM Table. Another solution, is to create a trigger at the end of each process to write this info to a PM Table. However, as I told you, you are better off using an external Reporting tool to calculate it.

Another question is that, how can I access the Advanced Search information which are in mysql? Some valuable information are available in (Home/Advanced Search). Those are excellent for charts. How can I access them from mysql?
At the moment, I connected PM's database to some BI tools like Report server and Jasper. and I do not know, how can I access them?

You can use standard SQL to search the APP_CACHE_VIEW table to give you the same records as Home>Advanced Search in PM. Learn how to use SQL in Jasper Reports.

[mohamad]

Thanks for your kind reply,
DEL_FINISH_DATE and DEL_TASK_DUE_DATE are just date field. There are some another fields in app_cache_view table like DEL_DURATION and DEL_DELAY_DURATION and DEL_DELAYED. I don't know why these fields are 0 in my table?? and what is their functionality? Because, it seems if I want to have KPI, I have to divide planning duration into actual duration? Would you please mention their duty(function) ( DEL_DURATION and DEL_DELAY_DURATION and DEL_DELAYED)?

If I want to embed Japser or Report Server in CE, how can I do? Is it possible? I mean, I like that users do not have to enter another link separately, It is better to run Jasper or Report server from PM menus. The language is java, and I'm not sure about using plugin for them? I would be grateful, if you could help me with this issue
Looking forward to your valuable comments,
Kind Regards

[amosbatto]

DEL_FINISH_DATE and DEL_TASK_DUE_DATE are just date field. There are some another fields in app_cache_view table like DEL_DURATION and DEL_DELAY_DURATION and DEL_DELAYED. I don't know why these fields are 0 in my table?? and what is their functionality? Because, it seems if I want to have KPI, I have to divide planning duration into actual duration? Would you please mention their duty(function) ( DEL_DURATION and DEL_DELAY_DURATION and DEL_DELAYED)?

Those fields are no long used by ProcessMaker. (I think it was version 3.1 when they stopped being used.)
Ex:
SELECT TIMESTAMPDIFF(HOUR, DEL_FINISH_DATE, DEL_TASK_DUE_DATE) AS DIFF_HOURS FROM APP_CACHE_VIEW

If I want to embed Japser or Report Server in CE, how can I do? Is it possible? I mean, I like that users do not have to enter another link separately, It is better to run Jasper or Report server from PM menus. The language is java, and I'm not sure about using plugin for them? I would be grateful, if you could help me with this issue
Looking forward to your valuable comments,
Kind Regards

To embed Jasper Reports inside an <iframe> inside a panel in a Dynaform or inside an <iframe> in a custom plugin page in ProcesssMaker, see:
https://community.jaspersoft.com/wiki/e ... g-http-api