Using a SQL table-valued user-defined function in an custom ASP report

2 minute read time.

I recently wrote an article called "Using a Table-Valued User-Defined Function to return data" that introduced the idea of using these functions as alternative data sources. In this article I want to look at how this idea can be put into practice.

In my database I created a simple table-valued user-defined function called tvf_reportuseractivity.

The function expects to be passed a single integer parameter representing a userid and it will then collate information about that current user's still 'pending' or 'in progress' work.

The ASP page can grab the ID of the current user from context and then pass that to the function using the CreateQueryObj method.

[code language="javascript"]
var intRecordID = CRM.GetContextInfo("user", "user_userid");
var mySQL = "select * from tvf_reportuseractivity(" + intRecordID + ")";
var myQuery = CRM.CreateQueryObj(mySQL, "");
myQuery.SelectSQL();
[/code]

The data from the function is returned to the QueryObj. Because of the table structure of the result set this is the ideal mechanism to be able to handle information provided by the function.

You can see that I have read the 'columns' of the result set using the standard myQuery.FieldValue() method.

[code language="javascript"]
var myContentBlock = CRM.GetBlock("content");
myContentBlock.contents = "

" + CRM.GetTrans("DefaultToDoNoCases", "todo") + "
"
myContentBlock.contents += "";
while (!myQuery.eof) {

myContentBlock.contents += "

"
";
myContentBlock.contents += CRM.GetTrans("Tabnames", myQuery.FieldValue("mytype"));
myContentBlock.contents += ":
";
myContentBlock.contents += myQuery.FieldValue("mycount");
myContentBlock.contents += "

myQuery.NextRecord();
}
myContentBlock.contents += "";
[/code]

You can see that this can been generated within the page very well.

But there are strong limitations with using the either functions like these or stored procedures. This is because Sage CRM dynamically changes defined SQL at run time. The SQL that you may think is referenced in code is not going to be the actual code run by the CRM objects.

For example below you can see that I have tried to use the idea of the function in the definition of the chart block.

[code language="javascript"]
var myChartBlock = CRM.GetBlock("chart");
with(myChartBlock) {
Stylename("Hbar");
xlprop = "mytype";
yprop = "mycount";
SQLText = mySQL;
ImageWidth = 600;
showlegend(false);
}
[/code]

When the SQLText uses the same variable containing the call to the function.

var mySQL = "select * from tvf_reportuseractivity(" + intRecordID + ")";

But when the function is used in the chart block an error will occur.

In the SQL log it will report the error

[code language="sql"]
select * from tvf_reportuseractivity WITH (NOLOCK) (9) Incorrect syntax near '9'
[/code]

This shows that the SQL is dynamically changed by Sage CRM.

In order to draw the chart I would have to change the code to use a simple SQL statement.

[code language="javascript"]
var myChartBlock = CRM.GetBlock("chart");
var strSQL = "select count(oppo_opportunityid) as 'mycount', 'Opportunities' as 'mytype' from opportunity where oppo_status = 'In Progress' and oppo_assigneduserid = " + intRecordID + "union select count(case_caseid) as 'mycount', 'Cases' as 'mytype' from cases where case_status = 'In Progress' and case_assigneduserid = " + intRecordID + " union select count(cmli_commlinkid) as 'mycount', 'Communications' as 'mytype' from vlistcommunication where comm_status = 'Pending' and cmli_comm_userid = " + intRecordID;
with(myChartBlock) {
Stylename("Hbar");
xlprop = "mytype";
yprop = "mycount";
SQLText = strSQL;
ImageWidth = 600;
showlegend(false);
}
[/code]