Panel

nameid	blue0

Dashboard components offer a visual representation of data to the user. They consist of an SQL statement, which if needed,

They can be dynamically altered by adding certain filters and criteria. implemented by a Java method that returns the data and a metadata XML file.

What does this section cover?

Table of Contents

...

There are two types of dashboard components, chart and summary.

Chart Dashboard Components

...

To create a new Chart Dashboard Component, you need to create:
An XML file that will define the method that will be called to retrieve the results and the various settings of the dashboard component's data source, type, and various settings.
A Java method 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)

Also, in order for the dashboard to be available to the user, it must be defined in metadata file modules.xml, which is located under ../metadata directory.
Expand
title Completed Activities Per Type And Month

Dashboard Component XML file

All dashboard component XML files must be placed under ../pages/dashboards/<module_name> directory.

In the following example completedActinitiesPerTypeAndMonth.xml is created under ../pages/dashboards/activities

Code Block

language	xml
title	completedActivitiesPerTypeAndMonth.xml
collapse	true

<?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>

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.
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

Code Block

language	java
title	loadCompletedActivitiesPerTypeAndMonth
collapse	true

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;
	}

Dashboard 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.

Code Block

language	xml
title	activitiesPerType.xml
collapse	true

<?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&amp;act=itm&amp;jndi=ejb/CRMUIActivity&amp;fc=loadForm&amp;pv0=((ACTIVITIES.ACTIVITYID))&amp;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 &gt;= 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>

Expand

title	Installation Activities Completed in August

Image Removed

Defining Dashboard Components in Metadata

...

Summary 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.

Also, in order for the dashboard to be available to the user, it must be defined be defined in metadata file file modules.xml, which is located under under ../metadata directory directory.

<modules> <module> <moduleid>ACCOUNTS_RECEIVABLE</moduleid> <applicationid>FINANCE</applicationid> ... <features> <feature> ... <reports> <report> <id>ACCOUNTS_RECEIVABLE_BALANCE_REPORT</id> <name>key_accounts_receivable_balance</name> <description>key_view_the_accounts_receivable_balance</description> <reportfilename>AccountsReceivable/Accounts_Receivable_Balance.xml</reportfilename> </report> ... </reports> </feature> ... </features> </module> </modules>

Code Blockexpand

language	xml
title	modules.xml

title	Non completed activities of logged in user

Image Added

For more information on creating dashboard components, go to Create Dashboard Components. For a full list of metadata dashboard attributes, go to Dashboards Metadata.

Versions Compared

Old Version 6

New Version Current

Key

Chart Dashboard Components

Dashboard Component XML file

Java Method to Retrieve Results

Dashboard Summary Page

Defining Dashboard Components in Metadata

Summary Dashboard Components

Page Comparison

Versions Compared

Old Version 6

New Version Current

Key

Chart Dashboard Components

Dashboard Component XML file

Java Method to Retrieve Results

Dashboard Summary Page

Defining Dashboard Components in Metadata

Summary Dashboard Components