Calling CLR functions from MDX query

Hi,

I am finding it extremely tricky in getting the CLR function in C# to return anything useful that doenst contain an error message. My function in the assembly SqlServerProject1 and the class UserDefinedFunctions is fnBitwise and performs a bitwise operation on the two input params.

select SqlServerProject1.UserDefinedFunctions.fnBitwise(24,8) on 0,

[measures].[Discount Amount] on 1

from [analysis services tutorial]

OR

Call SqlServerProject1.UserDefinedFunctions.fnBitwise(24,8)

Are not producing results, instead I get error msgs.

I have been getting msgs such as

Execution of the managed stored procedure fnBitwise failed with the following error: Microsoft::AnalysisServices::AdomdServer::AdomdException.

OR

Parser: The end of the input was reached.

Please could someone help as it seems like there is not clear help on this. What Im trying to do is to perform a bitwise operation and summing the results to create a new calculated member. Unfuortunately, Analysis services doesn't allow for bitwise operations, so I had created a CLR function in VS.Net to perform the operation. Now, while this works fine in SQL Server after being deployed there by VS.Net SQLServerProject, I am more inportantly trying to get this to work in AS using an MDX query to test the function. The SQL Server project doesn't let me add a reference to the class library that I may need as described by http://solidqualitylearning.com/blogs/dejan/archive/2005/12/01/1492.aspx .

The CLR Function is very simpe and is shown below:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using System.Data.OleDb;

using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions{

[Microsoft.SqlServer.Server.SqlFunction]

public static SqlInt32 fnBitwise(byte varA, byte varB)

{

long result = varA & varB;

SqlInt32 b;

if (result != 0)

{

b = 1;

}

else

{

b = 0;

}

return b;

}

};

I need a way of getting the result or the bitwise operation out in the MDX query so I can then create my calculated member in SQL Server Business Intelligence Development Studio Cube Designer.

Thanking you in advance.

Rob

There are two problems here. First of all AS doesn't seem to like SQL Server data types; I replaced SqlInt32 with int and got the sproc working ok:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using System.Data.OleDb;

using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions

{

[Microsoft.SqlServer.Server.SqlFunction]

public static int fnBitwise(byte varA, byte varB)

{

long result = varA & varB;

int b;

if (result != 0)

{

b = 1;

}

else

{

b = 0;

}

return b;

}

};

Secondly, neither of the examples of calling the sproc you give in your post are going to work because it returns an integer value and you're calling it places where AS is expecting either a member or a set (in the first example) or a cellset. You need to call the sproc within a calculated member definition, for example:

with member measures.test as ASStoredProc.fnBitWise(8,24)

select measures.test on 0 from mycube

HTH,

Chris

|||

Thanks Chris.

That's great.

Regards

Rob