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:

Tools like Query Analyzer provided with SQL Server can be used to refine the query before using it in the sanScript procedure. This will save time as you develop your metrics.


When writing your metric procedure, consider the following guidelines:


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}

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