[Throwaway]

Hello,

I was wondering what the most efficient way would be to figure out the percentage or number of Dynaforms that have already been submitted/completed?

Also,

can the data reporting tools report be used on community edition?

[amosbatto]

Are you talking about the number of DynaForms in a particular case? You would have to look at the variables which are assigned to a fields in the DynaForms to figure out whether the DynaForms have been submitted or not in the case. You can also look at the APP_HISTORY table or the serialized data for a case in the APPLICATION.APP_DATA field in the database, but it is much easier to export the data to a Report Table and use an SQL query to check whether the value of a variable associated with a field in a DynaForm is null or not.

For example,
DynaForm1 defines var1 which is exported as VAR1 in table 'PMT_MY_REPORT',
DynaForm2 defines var2 which is exported as VAR2,
DynaForm3 defines var3 which is exported as VAR3.

Then, your SQL query would be like this:

Code: Select all

SELECT
    MY_FIELD1, MY_FIELD2,
    CASE 
        WHEN VAR3 IS NOT NULL THEN '100%'
        WHEN VAR2 IS NOT NULL THEN '66%'
        WHEN VAR1 IS NOT NULL THEN '33%'
        ELSE '0%'
   END AS PERCENT_COMPLETE
FROM PMT_MY_REPORT

You can use any external data reporting tool such as JasperReports, Birt, Crystal Reports, etc. on your Report Tables.

[Throwaway]

Are you talking about the number of DynaForms in a particular case? You would have to look at the variables which are assigned to a fields in the DynaForms to figure out whether the DynaForms have been submitted or not in the case. You can also look at the APP_HISTORY table or the serialized data for a case in the APPLICATION.APP_DATA field in the database, but it is much easier to export the data to a Report Table and use an SQL query to check whether the value of a variable associated with a field in a DynaForm is null or not.

For example,
DynaForm1 defines var1 which is exported as VAR1 in table 'PMT_MY_REPORT',
DynaForm2 defines var2 which is exported as VAR2,
DynaForm3 defines var3 which is exported as VAR3.

Then, your SQL query would be like this:

Code: Select all

SELECT
    MY_FIELD1, MY_FIELD2,
    CASE 
        WHEN VAR3 IS NOT NULL THEN '100%'
        WHEN VAR2 IS NOT NULL THEN '66%'
        WHEN VAR1 IS NOT NULL THEN '33%'
        ELSE '0%'
   END AS PERCENT_COMPLETE
FROM PMT_MY_REPORT

You can use any external data reporting tool such as JasperReports, Birt, Crystal Reports, etc. on your Report Tables.

Yes, because I have forms that are broken down into parts (ex. Name Part 1, Name Part 2, and so on).
The report tables only allow me to add the fields in each of them, so I'm trying to think of the best way to allow a "supervisor" to look at the data to see the percentage of forms complete, maybe even only using the first field of each part. Where would I place the SQL query at?

[amosbatto]

You can use that query in an external data reporting tool. If you want to use it inside PM, then you can add a textbox to one of your DynaForm's to display the percent completed, which is associated with the "percentComplete" case variable.

Then, create the following trigger in the process:

Code: Select all

$caseId = @@APPLICATION;
$sql = "SELECT MY_FIELD1, MY_FIELD2,
    CASE
        WHEN VAR3 IS NOT NULL THEN '100%'
        WHEN VAR2 IS NOT NULL THEN '66%'
        WHEN VAR1 IS NOT NULL THEN '33%'
        ELSE '0%'
   END AS PERCENT_COMPLETE
   FROM PMT_MY_REPORT WHERE APP_UID='$caseId' ";
@=aResult = executeQuery($sql);
if (count(@=aResult) > 0) {
   @@percentComplete = @=aResult[1]['PERCENT_COMPLETE'];
} else {
   throw new Exception("Error in query: $sql");
}

Set this trigger to execute before the DynaForm with the "percentComplete" textbox.

[Throwaway]

You can use that query in an external data reporting tool. If you want to use it inside PM, then you can add a textbox to one of your DynaForm's to display the percent completed, which is associated with the "percentComplete" case variable.

Then, create the following trigger in the process:

Code: Select all

$caseId = @@APPLICATION;
$sql = "SELECT MY_FIELD1, MY_FIELD2,
    CASE
        WHEN VAR3 IS NOT NULL THEN '100%'
        WHEN VAR2 IS NOT NULL THEN '66%'
        WHEN VAR1 IS NOT NULL THEN '33%'
        ELSE '0%'
   END AS PERCENT_COMPLETE
   FROM PMT_MY_REPORT WHERE APP_UID='$caseId' ";
@=aResult = executeQuery($sql);
if (count(@=aResult) > 0) {
   @@percentComplete = @=aResult[1]['PERCENT_COMPLETE'];
} else {
   throw new Exception("Error in query: $sql");
}

Set this trigger to execute before the DynaForm with the "percentComplete" textbox.

Could I make this execute in a particular case by entering the case ID perhaps?

[amosbatto]

You can use it in a dependent field query, like this:

Code: Select all

SELECT CASE
        WHEN VAR3 IS NOT NULL THEN '100%'
        WHEN VAR2 IS NOT NULL THEN '66%'
        WHEN VAR1 IS NOT NULL THEN '33%'
        ELSE '0%'
   END AS PERCENT_COMPLETE, 
   CASE
        WHEN VAR3 IS NOT NULL THEN '100%'
        WHEN VAR2 IS NOT NULL THEN '66%'
        WHEN VAR1 IS NOT NULL THEN '33%'
        ELSE '0%'
   END AS PERCENT_COMPLETE_LABEL
   FROM PMT_MY_REPORT WHERE APP_NUMBER=@@caseNumber

where you have another textbox in the DynaForm with the ID "caseNumber", which is associated with the variable "caseNumber". (Note: The ID and the variable have to have the same name for dependent fields to work correctly.)