how to reference/add a custom assembly in a report?

I created a custom assembly in VB2003 -- testClassLib. I gave it a namespace
of testNameSpace, class name is testLib with one public function that just
adds 10 to a number argument
NameSpace testNameSpace
Public Class testLib
Public addNum(n As Integer) As Integer
Return n + 10
End Class
End NameSpace
I tested this assembly in a VB2003 app, and it works fine.
In RS2003 I go to Report Properties to References and browse for my custom
assembly which would be testClassLib.dll. I select that in the Assembly name
box which will now display:
testClassLib, Version=1.0.2511.22986, Culture=neutral, PublicKeyToken=null
and then select testClassLib.testNameSpace.testLib and then use an
instance name of test1. This does not work. So I modified the classname to
testNameSpace.testLib -- still no go when I try to rebuild the report. When
I remove the reference and rebuild the report - it rebuilds fine without any
errors.
What do I need to do to add this custom assembly to my report? How to do
this?
Thanks,
RichIf you didn't already do so, you need to move the .dll to the following
directory for VS to recognize it:
Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies
For sql server to recognize it (once you deploy the report) you need to
add it to the following directory on the report server:
Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\ReportServer\bin
Hope that helps.
On Nov 16, 4:10 pm, Rich <R...@.discussions.microsoft.com> wrote:
> I created a custom assembly in VB2003 -- testClassLib. I gave it a namespace
> of testNameSpace, class name is testLib with one public function that just
> adds 10 to a number argument
> NameSpace testNameSpace
> Public Class testLib
> Public addNum(n As Integer) As Integer
> Return n + 10
> End Class
> End NameSpace
> I tested this assembly in a VB2003 app, and it works fine.
> In RS2003 I go to Report Properties to References and browse for my custom
> assembly which would be testClassLib.dll. I select that in the Assembly name
> box which will now display:
> testClassLib, Version=1.0.2511.22986, Culture=neutral, PublicKeyToken=null
> and then select testClassLib.testNameSpace.testLib and then use an
> instance name of test1. This does not work. So I modified the classname to
> testNameSpace.testLib -- still no go when I try to rebuild the report. When
> I remove the reference and rebuild the report - it rebuilds fine without any
> errors.
> What do I need to do to add this custom assembly to my report? How to do
> this?
> Thanks,
> Rich|||Thank you very much. Yes, I pulled out my RS book, and it said the same
thing. But on the dev workstation I actually copied the dll to
C:\Program Files\Microsoft Sql Server\80\Tools\Report Designer
That is where I dropped the dll and where I reference it from the RS
designer, and now the desinger can see it and use it.
But the book was consistent with you on where you place the dll on the
server.
Thanks again for your reply.
Rich
"chris.teter@.gmail.com" wrote:
> If you didn't already do so, you need to move the .dll to the following
> directory for VS to recognize it:
> Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies
> For sql server to recognize it (once you deploy the report) you need to
> add it to the following directory on the report server:
> Program Files\Microsoft SQL Server\MSSQL.3\Reporting
> Services\ReportServer\bin
> Hope that helps.
> On Nov 16, 4:10 pm, Rich <R...@.discussions.microsoft.com> wrote:
> > I created a custom assembly in VB2003 -- testClassLib. I gave it a namespace
> > of testNameSpace, class name is testLib with one public function that just
> > adds 10 to a number argument
> >
> > NameSpace testNameSpace
> > Public Class testLib
> > Public addNum(n As Integer) As Integer
> > Return n + 10
> > End Class
> > End NameSpace
> >
> > I tested this assembly in a VB2003 app, and it works fine.
> >
> > In RS2003 I go to Report Properties to References and browse for my custom
> > assembly which would be testClassLib.dll. I select that in the Assembly name
> > box which will now display:
> >
> > testClassLib, Version=1.0.2511.22986, Culture=neutral, PublicKeyToken=null
> >
> > and then select testClassLib.testNameSpace.testLib and then use an
> > instance name of test1. This does not work. So I modified the classname to
> > testNameSpace.testLib -- still no go when I try to rebuild the report. When
> > I remove the reference and rebuild the report - it rebuilds fine without any
> > errors.
> >
> > What do I need to do to add this custom assembly to my report? How to do
> > this?
> >
> > Thanks,
> > Rich
>

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
> >
> >