Custom Stored procedure

Hi, I've tried to develop custom function for AS 2005, written in C#, and registered as database assembly

Code snippet is here

public string MySampleMethod()

{

AdomdCommand command = new AdomdCommand();

command.CommandText = string.Format("with member A AS 'username' select A on 0 FROM MyCube");

return (string)command.ExecuteScalar();

}

Error that I've got was "XML for Analysis parser: The input query is not in the language specified in the Dialect property for this request."

When I execute the same query from SQL Management Studio, everything works OK.

What I did wrong and how it can be resolved?

Thanks in advance

Borko

Hi Borko,

You can't use AS stored procs to execute queries on the same connection as you're using to call the proc, unfortunately. You can open a new connection within the proc but that may or may not be particularly useful to you - here's an example of how to do this:

using System;

using System.Collections.Generic;

using System.Text;

using Microsoft.AnalysisServices.AdomdServer;

using Microsoft.AnalysisServices.AdomdClient;

namespace MDXTests

{

public class RunMDXQueries

{

public static Microsoft.AnalysisServices.AdomdClient.AdomdDataReader RunAQuery()

{

Microsoft.AnalysisServices.AdomdClient.AdomdCommand c = new Microsoft.AnalysisServices.AdomdClient.AdomdCommand("select measures.members on 0 from [adventure works]");

AdomdConnection conn = new AdomdConnection(@."Data Source=localhost\sh; Provider=msolap.3; initial catalog=adventure works dw");

conn.Open();

c.Connection = conn;

return c.ExecuteReader();

}

}

}

You need to set security permissions to 'unrestricted' to get this to work; you can then call this from SQLMS using the following:

CALL SPSRUNNINGQUERIES.RUNAQUERY()

HTH,

Chris

|||

Chris,

information that is not possible to execute queries on the same connection as connection used to call the proc is very usefull.

Thank you very much.

Borko

Custom Code problem

I have this very basic custom code in my report:
Public Shared Function Basis(byVal Value as Integer) As Double
Basis = Basis + value
End Function
When I call this code, using =Code.Basis(Fields!BWT.Value), I expect it to
be like a running total. But all I get back is whatever the value of
Fields!BWT.Value is, without it adding up. I know that I could use the
runningvalue function, but I am wondering why this code doesn't work?quite possible the value of the function is not static. So everytime you come
into the function the value is reset. Try setting a temp invisible textbox to
basis' value evertime it updates.
"dachrist" wrote:
> I have this very basic custom code in my report:
> Public Shared Function Basis(byVal Value as Integer) As Double
> Basis = Basis + value
> End Function
> When I call this code, using =Code.Basis(Fields!BWT.Value), I expect it to
> be like a running total. But all I get back is whatever the value of
> Fields!BWT.Value is, without it adding up. I know that I could use the
> runningvalue function, but I am wondering why this code doesn't work?

Custom Code in Chart - background color

I have the following very simple code block that I wrote for a chart.
public function bdr_chartcolor(xType as string) as string
IF xType = "IR"
return "White"
ELSE
return "Blue"
END IF
end function
HOW and WHERE do I implement it to set the charts background color? Many
properties of objects have EXPRESSION as an option, HOWEVER, there is no
EXPRESSION as a type for a chart's background color in the properties.Chart area, Fill.
"MSSQLServerDeveloper" <MSSQLServerDeveloper@.discussions.microsoft.com>
wrote in message news:22B72235-5282-4095-951F-9B9DCF89E0B3@.microsoft.com...
>I have the following very simple code block that I wrote for a chart.
> public function bdr_chartcolor(xType as string) as string
> IF xType = "IR"
> return "White"
> ELSE
> return "Blue"
> END IF
> end function
> HOW and WHERE do I implement it to set the charts background color? Many
> properties of objects have EXPRESSION as an option, HOWEVER, there is no
> EXPRESSION as a type for a chart's background color in the properties.|||Sorry,
plot area, fill.
There are two, the area outside the chart plot area and the area inside the
chart plot area.
Use Code.bdf_chartcolor(...) in the expression editor.
"MSSQLServerDeveloper" <MSSQLServerDeveloper@.discussions.microsoft.com>
wrote in message news:22B72235-5282-4095-951F-9B9DCF89E0B3@.microsoft.com...
>I have the following very simple code block that I wrote for a chart.
> public function bdr_chartcolor(xType as string) as string
> IF xType = "IR"
> return "White"
> ELSE
> return "Blue"
> END IF
> end function
> HOW and WHERE do I implement it to set the charts background color? Many
> properties of objects have EXPRESSION as an option, HOWEVER, there is no
> EXPRESSION as a type for a chart's background color in the properties.

Custom Code

Hi,

Wrote a Custom Code i.e. a function.
Can not refer to this function from an expression area. It says unrecognized identifier.

Public Function TaxDeductions(ByVal Amount As Double) As Double
TaxDeductions = Amount * .25 + Amount * 0.05
End Functrion

Code.TaxDeductions(Sum(Fields!LineAmount.Value)

Any thoughts please?

make it static i.e. Public Shared Function....