Writing a metric procedure

Each metric should have its own procedure that gathers the data for the metric and returns the results to be displayed on the user’s Home Page. You can use any approach to gather the data for the metric. Most metric procedures will use pass-through SQL to gather the data. Pass-through SQL provides the following advantages:

• Data in all tables can be accessed
• Querying capabilities of SQL Server are useful for metrics, such as “order by” and “top” clauses.

Guidelines

When writing your metric procedure, consider the following guidelines:

• Make any SQL query you use as efficient as possible. The metric will be run each time the Home Page is loaded or refreshed, so a slow query will make the application less responsive.
• Always close any pass-through SQL connections you open to avoid wasting system resources. Consider using the SQL_GetConnection() function of the XSQLExec form in Microsoft Dynamics GP to retrieve and use the pass-through SQL connection already maintained by the Microsoft Dynamics GP application.

Example

The following example is the metric procedure for the “Top 5 Leads” metric in the sample integrating application. The global procedure is named Metric_Top5Leads. It is called by the MetricExecute global procedure, uses pass-through SQL to assemble the data for the metric, and returns the data through the inout parameters for the procedure.

inout integer iNumCategories;
inout integer iNumSeries;
inout string sChartType;
inout string sCategoryAxis;
inout string sValueAxis;
inout string sCategories[12];
inout currency iSeries1Values[12];

local string sDbName;
local long SQL_connection;
local long SQL_status;
local integer index;
local text SQL_Statements;

{Chart Information}
sChartType = CHART_TYPE_COLUMNCLUSTERED;

sCategoryAxis = "Leads";
sValueAxis = "Potential Revenue";
iNumSeries = 1;
iNumCategories = 5;

{Retrieve the current company's database}
sDbName = 'Intercompany ID' of globals;

if 'SQL Server' of globals > 0 then
	{Connect to the SQL data source. Use the connections already
	maintained by the core application.}
	SQL_status = SQL_GetConnection(FOREGROUND, SQL_connection) of form XSQLExec;
	if SQL_status = OKAY then
		{Build SQL statement to use the appropriate database.}
		SQL_Statements = "use " + sDbName;
	
		{Execute the SQL statements.}
		SQL_status = SQL_Execute(SQL_connection, SQL_Statements);
	end if;

	{SQL query to use}
	SQL_Statements = "select top 5 LeadName, PotentialRevenue from IG001 order by PotentialRevenue desc";
	{Execute the SQL statements.}
	SQL_status = SQL_Execute(SQL_connection, SQL_Statements);
	if SQL_status = 0 then
		index = 1;
		{Retrieve data from the first results set.}
		SQL_status = SQL_FetchNext(SQL_connection);
		while SQL_status <> 31 do

			{Get the information about the top leads.}
			SQL_status = SQL_GetData(SQL_connection, 1, sCategories[index]);
			SQL_status = SQL_GetData(SQL_connection, 2, iSeries1Values[index]);

			index = index + 1;
		
			SQL_status = SQL_FetchNext(SQL_connection);
		end while;

		{Clear the results sets and errors.}
		SQL_status = SQL_Clear(SQL_connection);
	end if;
end if;

Documentation Feedback

---