How to refer to Custom function in a query

I want to be able to refer to a custom function in a query
The Function is defined in the report proprties "Code" tab as follows:
public shared function MyCustomer as String
Return "ALFKI"
end function
I have tried different query formats, and I currently have:
="SELECT * FROM customers where CustomerID='" + Code.MyCustomer() +
"'"
I am using the Northwind database as an example
Though the code compiles, I am unable to refer to any of the fields in
"customers"
I know there are other ways to accomplish this. What I am interested in
is how to refer to user defined code in QueriesYou might be able to pull a field list first with simply
SELECT * FROM customers
and then change the SQL to include the code. You might want to try the
latter using View Code and see of that gets around the auto-population of
fields step.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"cab0san" <cabosan@.gmail.com> wrote in message
news:1109783555.987331.113460@.g14g2000cwa.googlegroups.com...
>I want to be able to refer to a custom function in a query
> The Function is defined in the report proprties "Code" tab as follows:
> public shared function MyCustomer as String
> Return "ALFKI"
> end function
> I have tried different query formats, and I currently have:
> ="SELECT * FROM customers where CustomerID='" + Code.MyCustomer() +
> "'"
> I am using the Northwind database as an example
> Though the code compiles, I am unable to refer to any of the fields in
> "customers"
> I know there are other ways to accomplish this. What I am interested in
> is how to refer to user defined code in Queries
>|||Thanks, that works. A bit "klugy" but it works!
So, I set my query to
select * from customers, then execute the query, then click another
tab, causing the field list to populate, then switch back to the data
tab and change it to
="SELECT * FROM customers where CustomerID='" + Code.MyCustomer() +
"'"
Behind the scenes that is accomplishing the step of adding the dataset
definition to my RDL|||I'm glad it's working.
I found that editing directly in the data tab works fine until you have a
complex SQL statement that uses its own declared variables (which for SQL
Server look similar to parameters: @.ParmName). When you add parameters
manually to the dataset, sometimes the data tab editing blows away those
manually entered parameters. That's when I use Bruce's suggestion to edit
SQL using View Code.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"cab0san" <cabosan@.gmail.com> wrote in message
news:1109862516.895060.241340@.l41g2000cwc.googlegroups.com...
> Thanks, that works. A bit "klugy" but it works!
> So, I set my query to
> select * from customers, then execute the query, then click another
> tab, causing the field list to populate, then switch back to the data
> tab and change it to
> ="SELECT * FROM customers where CustomerID='" + Code.MyCustomer() +
> "'"
> Behind the scenes that is accomplishing the step of adding the dataset
> definition to my RDL
>|||I have a similar issue however I am using a DB2 stored procedure so I dont
have the option to manipulate the sql. I was trying to put a translation on
the parameters list somehow and that doesnt seem to work although in the
Hitchhikers Guide it states that you can use and expression anywhere you have
the opportunity presented by <expression> or Fx ... which I see as an option
on the datasource parameters tab. I need to translate the client I receive
in as a parameter into a different value.
On the dataset parameters tab I have Parameter Name PM_Client_Code and the
value is =Code.TranslateClient(Parameters!PM_CLIENT_CODE.Value)
The function TranslateClient is this:
Function TranslateClient(ByVal clientnum As String) As String
TranslateClient = "ABC"
End Function
It doesnt work. Although the code works in vb .net just fine.
"cab0san" wrote:
> Thanks, that works. A bit "klugy" but it works!
> So, I set my query to
> select * from customers, then execute the query, then click another
> tab, causing the field list to populate, then switch back to the data
> tab and change it to
> ="SELECT * FROM customers where CustomerID='" + Code.MyCustomer() +
> "'"
> Behind the scenes that is accomplishing the step of adding the dataset
> definition to my RDL
>|||That looks good to me and should work. I suggest creating a report just with
your parameter and a textbox and assign this expression to the textbox (so
you can see what happens). Make sure that your parameter is exactly the name
you have here, the parameter is case sensitive.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MJ Taft" <MJTaft@.discussions.microsoft.com> wrote in message
news:0EF45D79-4C8A-4B99-9D5A-30610FFB1F9D@.microsoft.com...
> I have a similar issue however I am using a DB2 stored procedure so I dont
> have the option to manipulate the sql. I was trying to put a translation
on
> the parameters list somehow and that doesnt seem to work although in the
> Hitchhikers Guide it states that you can use and expression anywhere you
have
> the opportunity presented by <expression> or Fx ... which I see as an
option
> on the datasource parameters tab. I need to translate the client I
receive
> in as a parameter into a different value.
> On the dataset parameters tab I have Parameter Name PM_Client_Code and the
> value is =Code.TranslateClient(Parameters!PM_CLIENT_CODE.Value)
> The function TranslateClient is this:
> Function TranslateClient(ByVal clientnum As String) As String
> TranslateClient = "ABC"
> End Function
> It doesnt work. Although the code works in vb .net just fine.
> "cab0san" wrote:
> > Thanks, that works. A bit "klugy" but it works!
> >
> > So, I set my query to
> >
> > select * from customers, then execute the query, then click another
> > tab, causing the field list to populate, then switch back to the data
> > tab and change it to
> > ="SELECT * FROM customers where CustomerID='" + Code.MyCustomer() +
> > "'"
> > Behind the scenes that is accomplishing the step of adding the dataset
> > definition to my RDL
> >
> >