Create Custom Reports

You can add custom reports using combinations of business rules, dashboard data adapters, dashboard components, and dashboards.

NOTE: Any custom reports that use custom components are removed when performing an Uninstall UI. The ACM_Reports business rule is overwritten during an upgrade. Any customizations to this business rule must be backed up and merged into the updated business rule.

Follow the sample steps below to add a new custom report to show the values of the custom properties FlowReason and FlowPriority.

Add the report definition to the ACM_Reports by navigating to Business Rules > Dashboard Data Set > ACM_Reports.
Add the report to the list of reports. Be sure to add the comma to the previous line.
Copy and paste rows 112-121 and update for the new report name.
Insert the following code after line 331:

Copy

private DataTable GetMyCustomReport1(SessionInfo si, string startTime, string endTime)
{
    try
    {
        using (DbConnInfo dbConnFW = BRApi.Database.CreateFrameworkDbConnInfo(si))
        {
            using (DbConnInfo dbConnApp = BRApi.Database.CreateApplicationDbConnInfo(si))
            {
    endTime = endTime.Replace("/", "-") + " 23:59:59";

    // Create the data table to return
    var sql = new System.Text.StringBuilder();
                sql.Append("Select r.ID, r.RequesterID, r.Status, ");
                sql.Append("'Commit' As StepType, f.Label, r.LastModified, ");
                sql.Append("'" + startTime + "' As CriteriaStartTime, ");
                sql.Append("'" + endTime + "' As CriteriaEndTime, ");
            //For item level properties, use i.ItemProperties instead of r.RequestProperties
                sql.Append("JSON_Value(r.RequestProperties, '$.Properties.FlowReason') As FlowReason, ");
                sql.Append("JSON_Value(r.RequestProperties, '$.Properties.FlowPriority') As FlowPriority ");
            //FOR ITEM LEVEL PROPERTIES, UNCOMMENT NEXT 2 LINES
                // sql.Append("From " + ACM_Globals.m_ItemView + " i ");
                // sql.Append("RIGHT Join " + ACM_Globals.m_MasterRequestView + " r On i.FKRequestID = r.RequestID ");
                sql.Append("From " + ACM_Globals.m_MasterRequestView + " r ");
                sql.Append("INNER JOIN " + ACM_Globals.m_StepTable + " s ON r.FKStepID = s.StepID ");
                sql.Append("INNER JOIN " + ACM_Globals.m_FlowTable + " f ON r.FKFlowID = f.FlowID ");
                sql.Append("WHERE s.StepType = 3 AND r.Status = 'Completed' AND ");
                sql.Append("r.LastModified >= '" + startTime + "' And r.LastModified <= '" + endTime + "' ");
                sql.Append("ORDER BY r.LastModified DESC");        

                using (var dt = BRApi.Database.ExecuteSql(dbConnApp, sql.ToString(), false))
                {
                    dt.TableName = "MyCustomReport1";
                    return dt;
                }
            }
        }
    }
    catch (Exception ex)
    {
        Logger.Write(si, "Error getting Activity Log Report.", ACM_Globals.LogLevel.ERR, ACM_Globals.LogCategory.AcmReports, ex);
        throw ErrorHandler.LogWrite(si, new XFException(si, ex));
    }            
}

This is what it will look like in the business rule:

Copy

        private DataTable GetMyCustomReport1(SessionInfo si, string startTime, string endTime)
        {
            try
            {
                using (DbConnInfo dbConnFW = BRApi.Database.CreateFrameworkDbConnInfo(si))
                {
                    using (DbConnInfo dbConnApp = BRApi.Database.CreateApplicationDbConnInfo(si))
                    {
                        endTime = endTime.Replace("/", "-") + " 23:59:59";

                        // Create the data table to return
                        var sql = new System.Text.StringBuilder();
                        sql.Append("SELECT r.ID, r.RequesterID, r.Status, ");
                        sql.Append("'Commit' AS StepType, f.Label, r.LastModified, ");
                        sql.Append("'" +  startTime + "' AS CriteriaStartTime, ");
                        sql.Append("'" + endTime + "' AS CriteriaEndTime, ");
                    // For ITEM LEVEL Properties, use i.ItemProperties instead of r.RequestProperties
                        sql.Append("JSON_Value(r.RequestProperties, '$.Properties.FlowReason') AS FlowReason, ");
                        sql.Append("JSON_Value(r.RequestProperties, '$.Properties.FlowPriority') AS FlowPriority ");
                    // For ITEM LEVEL Properties, UNCOMMENT NEXT 2 LINES
                        // sql.Append("FROM " + ACM_Globals.m_ItemView + " i ");
                        // sql.Append("RIGHT JOIN " + ACM_Globals.m_MasterRequestView + " r ON i.FKRequestID = r.RequestID");
                        sql.Append("FROM " + ACM_Globals.m_MasterRequestView + " r ");
                        sql.Append("INNER JOIN " + ACM_Globals.m_StepTable + " s ON r.FKStepID = s.StepID ");
                        sql.Append("INNER JOIN " + ACM_Globals.m_FlowTable + " f ON r.FKFlowID = f.FlowID ");
                        sql.Append("WHERE s.StepType = 3 AND r.Status = 'Completed' AND ");
                        sql.Append("r.LastModified >= '" + startTime + "' AND r.LastModified <= '" + endTime + "' ");
                        sql.Append("ORDER BY r.LastModified DESC");

                        using (var dt = BRApi.Database.ExecuteSql(dbConnApp, sql.ToString(), false)) 
                        {
                            dt.TableName = "MyCustomReport1";
                            return dt;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ErrorHandler.LogWrite(si, new XFException(si, ex));
            }
        }

5. Compile the business rule to check the syntax.

Add Reports to the Application Control Manager Custom Dashboard

Add the reports to the Application Control Manager Custom dashboard as follows:

Click Application Dashboards > Dashboard Maintenance Units > Data Adapters.
Click Create Data Adapter.
Enter a name for the data adapter.
For Command Type select Method.
For Method Type select Business Rule.
For Method Query, click the ellipsis and add the following:

{ACM_Reports}{MyCustomReport1}{StartTime=|!Report_StartDateTime_ACM!|, EndTime=|!Report_zEndDateTime_ACM!|}
In Results Table Name enter MyCustomReport1.
Click Test Data Adapter to test the adapter.

You should see similar results to this: