Page 1 of 1

how to export data from Report Table?

Posted: Wed Mar 16, 2016 4:53 am
by MitraDeepak
Hi,

How can i export report from report table in excel or .csv format .

Please if anyone knows, Kindly let me know

Regards
MitraDeepak

Re: how to export data from Report Table?

Posted: Tue May 03, 2016 7:24 am
by rundyz
Hi,
best way to do this is to login directly into your database with say phpmyadmin and download the table contents from there.
PM doesn't allow exporting data from pm report tables.

Hope this helps

Re: how to export data from Report Table?

Posted: Tue May 03, 2016 2:56 pm
by amosbatto
I registered a bug report about this: https://processmaker.atlassian.net/browse/TRI-1297

Re: how to export data from Report Table?

Posted: Tue May 03, 2016 7:04 pm
by amosbatto
I added some documentation in the wiki and created a sample process to show how to download the data in Report Tables. See:
http://wiki.processmaker.com/3.0/Report ... ort_Tables

Re: how to export data from Report Table?

Posted: Tue May 03, 2016 9:44 pm
by rundyz
Oh awesome,
I had assumed it was intentional.

Re: how to export data from Report Table?

Posted: Thu Dec 13, 2018 10:57 am
by Ariel1982
Great! It was very helpful!
Now I´m looking for a way to restrict the list of tables the user can select, but I couldn´t find it yet.
I´m really new to processmaker so any clue will be apreciated.
Thanks!

Re: how to export data from Report Table?

Posted: Thu Dec 13, 2018 11:35 pm
by amosbatto
Ariel1982 wrote: Thu Dec 13, 2018 10:57 am Now I´m looking for a way to restrict the list of tables the user can select, but I couldn´t find it yet.
There is no built-in way to limit access to certain PM Tables in ProcessMaker. Here are some suggestions:

If you don't want your users to access the PM Tables through Admin > Settings > PM Tables, then remove the PM_SETUP_PM_TABLES from their role.

Then, if you want them to be able to access a certain table, you can create a process which uses a trigger to query a particular table and display it in a grid. Then only assign certain users to the first task in that process so they can start a case to access the table. You can add a loop to the process, so they only have to use one case to access the table over and over.

If you want to avoid using a case to access the table, you can also create a plugin with a permission and a new page that allows the user to access the case. (Only attempt this if you are a PHP programmer.)

Re: how to export data from Report Table?

Posted: Fri Dec 14, 2018 9:43 am
by Ariel1982
Thanks for answering so soon! Great infomation! Unfortunately, I'm not a programmer so I have limited options.
This is what I've tried up to now: based on the documentation for "Exporting Report Tables with Triggers" I created a form that allows the user to select a table from all de additional tables. This is the dropdown's SQL sentence :
SELECT ADD_TAB_NAME, ADD_TAB_NAME FROM ADDITIONAL_TABLES
I 'm trying to find a way to limit the list of tables it shows (e.g. only the report tables related to a specific process) using a WHERE clause, but I couldn´t make it works. :|

Re: how to export data from Report Table?

Posted: Fri Dec 14, 2018 9:09 pm
by amosbatto
You can use a query like this to limit to Report Tables from a certain number of processes:

SELECT ADD_TAB_NAME, ADD_TAB_NAME FROM ADDITIONAL_TABLES
WHERE PRO_UID IN ('2834197345a2a09e94b2ed5000427983', '33966532359aadfaeb6b2c9081082347',
'33966532359aadfaeb6b2c9081082347')

(You can find the process IDs by looking at the case information or by running cases with the debugger enabled and looking at the PROCESS system variable.)

You can create different processes to access a specific group of Report Tables and assign the users to those processes.

Another option is to make the list of processes in the SQL dependent upon the logged-in user.

Create two groups named "Reports 1" and "Reports 2" and assign users to those groups.

Then create the following trigger to look up whether the logged in user is these groups and dynamically create the list of processes:
Code: Select all
//set to processes that all users can access:
@@processList = "'8511196744b6380deb57b69039061394', '33966532359aadfaeb6b2c9081082347'";

$aUsers1 = PMFGetGroupUsers( PMFGetGroupUID("Reports 1") );
foreach ($aUsers1 as $aUser) {
      if (@@USER_LOGGED == $aUser['USR_UID']) {
           //add processes for Reports 1 group:
           @@processList .= ", '2834197345a2a09e94b2ed5000427983', '1192478525a5edc4e8910a1088175339'"; 
           break;
      }
}

$aUsers2 = PMFGetGroupUsers( PMFGetGroupUID("Reports 2") );
foreach ($aUsers2 as $aUser) {
      if (@@USER_LOGGED == $aUser['USR_UID']) {
           //add processes for Reports 2 group:
           @@processList .= ", '1234567892834197345a2a09e94b'"; 
           break;
      }
}
Set this trigger to fire before the dynaform.

Then, add a hidden field to the dynaform whose variable and ID is "processList"

Then set the SQL query in the dropdown box to:

SELECT ADD_TAB_NAME, ADD_TAB_NAME FROM ADDITIONAL_TABLES WHERE PRO_UID IN (@#processList)

Re: how to export data from Report Table?

Posted: Mon Dec 17, 2018 1:14 pm
by Ariel1982
Perfect! You helped me a lot!