Dashboard components offer a visual representation of data to the user. They consist of an SQL statement, which if needed, can be dynamically altered by adding certain filters and criteria.
What does this section cover?
There are two types of dashboard components, chart and summary. For an introduction to dashboard components, go to Dashboard Components.
1a. Chart Dashboard Components
Creating Chart Dashboard Components
To create a new Chart Dashboard Component, you need to create:
- An XML file that will define the component's data source, type, and various settings.
- A Java method OR an SQL statement that will retrieve the dashboard results.
- A summary page that the dashboard component will link to.
A data entry page (definition and layout XML pages) that will give the user the ability to adjust the dashboard settings to apply an additional filtering. (optional)
Completed Activities Per Type And Month
1. Dashboard Component XML file
All dashboard component XML files must be placed under <custom_project>/web/WebContent/pages/dashboards/<module_name> directory.
In the following example completedActinitiesPerTypeAndMonth.xml is created under <custom_project>/web/WebContent/pages/dashboards/activities
<?xml version="1.0" encoding="UTF-8"?><dashboardcomponent xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../../../xsd/dashboards.xsd">
<name>key_completed_activities_per_type_and_month</name>
<description>key_completed_activities_per_type_and_month_stacked_bar_chart</description>
<ejb>CRMUIActivityDashboard</ejb>
<method>loadCompletedActivitiesPerTypeAndMonth</method>
<type>stack</type>
<orientation>vertical</orientation>
<primaryfield>ACTIVITIES.ACTIVITYID</primaryfield>
<primaryfieldlabel>key_number_of_activities</primaryfieldlabel>
<groupbyfield>ACTIVITIES.ACTTYPEID,ACTUALCLOSINGDATE_FILTER</groupbyfield>
<groupbyfieldlabel>key_month,_type</groupbyfieldlabel>
<valuelabels>ejb/CRMUIActivityType.getTypeOptions</valuelabels>
<summarypage>activities/activitiesPerType</summarypage>
<setting>
<page>dashboards/activitiesPerTypeSettings</page>
<classname>ejb/CRMUIActivityDashboard</classname>
<method>loadActivitiesPerTypeSettingsForm</method>
</setting>
</dashboardcomponent>
For a full list of dashboard component XML file attributes, go to Dashboard Components Documentation.
2. Java Method to Retrieve Results
The method should:
- Construct the SQL script that will be used to retrieve the results.
- Use executeUsingPreparedStatement(sqlStatement, dbName, parameters, dbType) method of SQLUtil class, to retrieve the results and put them in a variable of type com.crm.framework.util.ResultSetUtil.
- Convert com.crm.framework.util.ResultSetUtil to com.crm.process.dashboards.DashboardComponentData using DashboardComponentData class constructor.
Note:
- To add any additional summary filters to the summary page linked to the dashboard component, use addSummaryFilter method as shown in the following example.
- The method must always return a result of type com.crm.process.dashboards.DashboardComponentData
public DashboardComponentData loadCompletedActivitiesPerTypeAndMonth(
String dashboardID,
String dashboardComponentID,
String filterConditions,
ArrayList<Object> parameters) throws Exception {
CRMDODashboard dashboard = (CRMDODashboard)dashboardBean.load(dashboardID);
CRMDODashboardComponent dashboardComponent = (CRMDODashboardComponent)dashboardComponentBean.load(dashboard, dashboardComponentID);
String dashboardComponentSettings = "";
Set<CRMDOActivityType> activityTypes = activityDashboardXMLUtilBean.loadActivityTypesPerTypeFromXML(dashboardComponent);
if (activityTypes!=null && activityTypes.size()>0)
{
dashboardComponentSettings += "\n AND ACTIVITYTYPES.ACTTYPEID IN ( ";
Iterator<CRMDOActivityType> iter = activityTypes.iterator();
while (iter.hasNext())
{
CRMDOActivityType activityType = iter.next();
dashboardComponentSettings += "\n '" + activityType.getId() + "',";
}
dashboardComponentSettings = dashboardComponentSettings.substring(0, dashboardComponentSettings.length()-1);
dashboardComponentSettings += "\n )";
}
String sql =
"\n SELECT * FROM ( " +
"\n SELECT ACTIVITYTYPES.ACTTYPEID, " +
"\n CASE " +
"\n " + DateUtil.getMonthSQL(getCRMSession().getDbtype(), "ACTIVITIES.ACTIVITYACTUALCLOSINGDATE") +
"\n WHEN '1' THEN 'JAN' " +
"\n WHEN '2' THEN 'FEB' " +
"\n WHEN '3' THEN 'MAR' " +
"\n WHEN '4' THEN 'APR' " +
"\n WHEN '5' THEN 'MAY' " +
"\n WHEN '6' THEN 'JUN' " +
"\n WHEN '7' THEN 'JUL' " +
"\n WHEN '8' THEN 'AUG' " +
"\n WHEN '9' THEN 'SEP' " +
"\n WHEN '10' THEN 'OCT' " +
"\n WHEN '11' THEN 'NOV' " +
"\n WHEN '12' THEN 'DEC' " +
"\n END AS MONTH, " +
"\n COUNT(ACTIVITIES.ACTIVITYID) AS TOTAL " +
"\n FROM ACTIVITIES " +
"\n INNER JOIN ACTIVITYTYPES ON ACTIVITYTYPES.ACTTYPEID = ACTIVITIES.ACTTYPEID " +
"\n WHERE ACTIVITIES.ACTIVITYDELETED = 0 " +
"\n AND ACTIVITIES.LIFECYCLESTATE = '" + ActivityLifeCycleState.COMPLETED.toString() + "' " +
"\n " + dashboardComponentSettings +
"\n " + filterConditions +
"\n AND " + DateUtil.getSQLFirstDayOfMonthDate(getCRMSession().getDbtype(), "ACTIVITIES.ACTIVITYACTUALCLOSINGDATE")+" >= " + DateUtil.addMonthsSQLDate(getCRMSession().getDbtype(), DateUtil.getSelectSQLDate(getCRMSession().getDbtype(), DateUtil.getFirstDateOfMonth(getCurrentDate())), -12) + " " +
"\n GROUP BY ACTIVITYTYPES.ACTTYPEID, " +
"\n CASE " +
"\n " + DateUtil.getMonthSQL(getCRMSession().getDbtype(), "ACTIVITIES.ACTIVITYACTUALCLOSINGDATE") +
"\n WHEN '1' THEN 'JAN' " +
"\n WHEN '2' THEN 'FEB' " +
"\n WHEN '3' THEN 'MAR' " +
"\n WHEN '4' THEN 'APR' " +
"\n WHEN '5' THEN 'MAY' " +
"\n WHEN '6' THEN 'JUN' " +
"\n WHEN '7' THEN 'JUL' " +
"\n WHEN '8' THEN 'AUG' " +
"\n WHEN '9' THEN 'SEP' " +
"\n WHEN '10' THEN 'OCT' " +
"\n WHEN '11' THEN 'NOV' " +
"\n WHEN '12' THEN 'DEC' " +
"\n END " +
"\n ) TEMP " +
"\n ORDER BY ( " +
getOrderByWithCurrentMonthPriority() +
"\n ), ACTTYPEID ";
ResultSetUtil data = SQLUtil.executeUsingPreparedStatement(sql, getOrganisationID(), parameters, getCRMSession().getDbtype());
DashboardComponentData componentData = new DashboardComponentData(data);
String summaryFilter = "LIFECYCLESTATE_FILTER~" + ActivityLifeCycleState.COMPLETED.toString();
componentData.addSummaryFilter(summaryFilter);
return componentData;
}
3. Dashboard Linked Summary Page
The fields defined in <primaryfield> and <groupbyfield> in dashboard component page, and the summary filters defined in the java method should all exist in the dashboard summary page.
<?xml version="1.0" encoding="UTF-8"?><summary xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../../../xsd/summary.xsd">
<title>key_activities</title>
<sql>FROM ACTIVITIES
LEFT JOIN USERS CREATEDBY ON CREATEDBY.USERID = ACTIVITIES.ACTIVITYCREATEDBYUSERID
LEFT JOIN USERS UPDATEDBY ON UPDATEDBY.USERID = ACTIVITIES.ACTIVITYLASTUPDATEDBYUSERID
LEFT JOIN ACTIVITYTYPES ON ACTIVITYTYPES.ACTTYPEID = ACTIVITIES.ACTTYPEID
...
WHERE ACTIVITIES.ACTIVITYDELETED = 0 AND ACTIVITYTYPES.ACTTYPEDELETED = 0
</sql>
<styleclass>((ACTIVITIES.LIFECYCLESTATE))=='COMPLETED'|disabled</styleclass>
<rowsperpage>20</rowsperpage>
<primaryfield>ACTIVITIES.ACTIVITYID</primaryfield>
<mainlinkfield>ACTIVITIES.ACTIVITYNUMBER</mainlinkfield>
<multiselect>true</multiselect>
<entityfilter>ACTIVITIES</entityfilter>
<module>ACTIVITIES</module>
<fieldlist>
<!-- external filters -->
<field>
<fieldname>ACTIVITIES.ACTIVITYID</fieldname>
<filtertextoption>equal</filtertextoption>
</field>
...
<!-- fields -->
<field>
<fieldname>ACTIVITIES.ACTIVITYNUMBER</fieldname>
<caption>key_number</caption>
<summary>true</summary>
<available>true</available>
<filter>true</filter>
<filteravailable>true</filteravailable>
<link>page.do?xml=activities/activity&act=itm&jndi=ejb/CRMUIActivity&fc=loadForm&pv0=((ACTIVITIES.ACTIVITYID))&pvc=1</link>
</field>
<field>
<fieldname>ACTUALCLOSINGDATE_FILTER</fieldname>
<fieldfunction>ACTIVITIES.ACTIVITYID</fieldfunction>
<caption>key_month</caption>
<filter>false</filter>
<filteravailable>false</filteravailable>
<fieldtype>sql</fieldtype>
<filtercaseinsensitive>false</filtercaseinsensitive>
<filtercondition>
ACTIVITIES.ACTIVITYID IN (
SELECT ACT.ACTIVITYID FROM ACTIVITIES ACT
WHERE (
CASE SQL_MONTH[ ACT.ACTIVITYACTUALCLOSINGDATE ]SQL_MONTH
WHEN '1' THEN 'JAN'
WHEN '2' THEN 'FEB'
WHEN '3' THEN 'MAR'
WHEN '4' THEN 'APR'
WHEN '5' THEN 'MAY'
WHEN '6' THEN 'JUN'
WHEN '7' THEN 'JUL'
WHEN '8' THEN 'AUG'
WHEN '9' THEN 'SEP'
WHEN '10' THEN 'OCT'
WHEN '11' THEN 'NOV'
WHEN '12' THEN 'DEC' END
) ='#1'
AND ACT.ACTIVITYACTUALCLOSINGDATE >= SQL_ADD[M,-13,SQL_GETDATETIME]SQL_ADD
AND ACT.ACTIVITYDELETED = 0
)
</filtercondition>
<filteronexpand>false</filteronexpand>
</field>
<field>
<fieldname>LIFECYCLESTATE_FILTER</fieldname>
<fieldfunction>ACTIVITIES.LIFECYCLESTATE</fieldfunction>
<caption>key_life_cycle_state</caption>
<summary>false</summary>
<filter>true</filter>
<fixedlookup>ejb/CRMUIActivity.getLifeCycleStateOptions:key_all</fixedlookup>
<filtertextoption>In</filtertextoption>
</field>
...
</fieldlist>
<actions>
</actions>
</summary>
Installation Activities Completed in August
For more information on creating summary pages, go to Summary Pages.
4. Dashboard Settings Data Entry Page
Creating a dashboard settings data entry page is just like creating any other data entry page. For more information on creating data entry pages, go to Data Entry Pages.
Activities Per Type Settings
1b. Summary Dashboard Components
Creating Chart Dashboard Components
To create a new Summary Dashboard Component, you need to create:
- An XML file that will define the component's data source, type, and various settings.
- The embedded summary page that will retrieve and display the dashboard results.
Non completed activities of logged in user
1. Dashboard Component XML file
All dashboard component XML files must be placed under <custom_project>/web/WebContent/pages/dashboards/<module_name> directory.
In the following example nonCompletedActivitiesSummary.xml is created under <custom_project>/web/WebContent/pages/dashboards/activities
<?xml version="1.0" encoding="UTF-8"?><dashboardcomponent xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../../../xsd/dashboards.xsd">
<name>key_non_completed_activities_of_logged_in_user</name>
<description>key_non_completed_activities_of_logged_in_user</description>
<type>summary</type>
<summarypage>activities/dashboardNonCompletedActivities</summarypage>
</dashboardcomponent>
For a full list of dashboard component XML file attributes, go to Dashboard Components Documentation.
2. Dashboard Embedded Summary Page
In the following example dashboardNonCompletedActivities.xml is created under <custom_project>/web/WebContent/pages/summary/activities
<?xml version="1.0" encoding="UTF-8"?><summary xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../../../xsd/summary.xsd">
<title>key_activities</title>
<sql>FROM ACTIVITIES
LEFT JOIN ACTIVITYTYPES ON ACTIVITYTYPES.ACTTYPEID = ACTIVITIES.ACTTYPEID
LEFT JOIN ACTIVITYSTATUSES ON ACTIVITIES.ACTSTATUSID = ACTIVITYSTATUSES.ACTSTATUSID
WHERE ACTIVITIES.ACTIVITYDELETED = 0
AND ACTIVITIES.LIFECYCLESTATE <> 'COMPLETED'
<!-- for logged in user -->
</sql>
<rowsperpage>5</rowsperpage>
<primaryfield>ACTIVITIES.ACTIVITYID</primaryfield>
<mainlinkfield>ACTIVITIES.ACTIVITYNUMBER</mainlinkfield>
<module>ACTIVITIES</module>
<entityfilter>ACTIVITIES</entityfilter>
<fieldlist>
<!-- external filters -->
<!-- basic search -->
<field>
<fieldname>ACTTYPE_FILTER</fieldname>
<fieldfunction>ACTIVITIES.ACTTYPEID</fieldfunction>
<caption>key_type</caption>
<filter>true</filter>
<filterlookupname>dataset;activities.loadtypes;acttypeid;acttypename</filterlookupname>
</field>
<!-- advanced search -->
<!-- fields -->
<field>
<fieldname>ACTIVITIES.ACTIVITYNUMBER</fieldname>
<caption>key_number</caption>
<summary>true</summary>
<link>page.do?xml=activities/activity&act=itm&jndi=ejb/CRMUIActivity&fc=loadForm&pv0=((ACTIVITIES.ACTIVITYID))&pvc=1</link>
</field>
<field>
<fieldname>ACTIVITIES.ACTTYPEID</fieldname>
<caption>key_type</caption>
<summary>true</summary>
<fixedlookup>ejb/CRMUIActivityType.getTypeOptions:key_all</fixedlookup>
</field>
<field>
<fieldname>ACTIVITIES.ACTIVITYDESC</fieldname>
<caption>key_description</caption>
<summary>true</summary>
</field>
<field>
<fieldname>ACTIVITYSTATUSES.ACTSTATUSNAME</fieldname>
<caption>key_status</caption>
<summary>true</summary>
</field>
<field>
<fieldname>ACTIVITYEXPECTEDCLOSINGDATE</fieldname>
<fieldfunction>ACTIVITIES.ACTIVITYEXPECTEDCLOSINGDATE</fieldfunction>
<caption>key_expected_completion_date</caption>
<summary>true</summary>
</field>
<!-- drilldowns -->
</fieldlist>
</summary>
For more information on creating summary pages, go to Summary Pages.
In order for the dashboard to be available to the user, it must be defined in metadata file modules.xml, which is located under <custom_project>/web/WebContent/metadata directory
<modules>
<module>
<moduleid>ACCOUNTS_RECEIVABLE</moduleid>
<applicationid>FINANCE</applicationid>
...
<features>
<feature>
...
<dashboardcomponents>
<dashboardcomponent>
<id>COMPLETED_ACT_PER_TYPE_MONTH</id>
<name>key_completed_activities_per_type_and_month_stacked_bar_chart</name>
<description>key_completed_activities_per_type_and_month_stacked_bar_chart</description>
<filename>activities/completedActivitiesPerTypeAndMonth</filename>
</dashboardcomponent>
...
</dashboardcomponents>
</feature>
...
</features>
</module>
</modules>
Below you can see the available dashboard components when setting up a new dashboard.
For a full list of metadata dashboard attributes, go to Dashboards Metadata.