Passing Parameter

I've to create an instance reference to my custom assembly by constructor
method.
It works well with a Code into the report .rdl like this:
---
<Code>
Public Obj As MyClass
Protected Overrides Sub OnInit()
dim MyArg as String = 4
Obj = new MyClass(MyArg)
End Sub
</Code>
--
But i need to pass a real parameter so:
Obj = new MyClass(Parameters!MyPar.Value)
it don't work and break with the error
BC30469 "The reference to a member not shared needs a reference to an object"
(I translate this from the italian version.... sorry for my bad english!!!
How can i do'
Thanks a lot.Try using this reference.
Report.Parameters!MyPar.Value
that should work in the Code.section
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Riccardo" <Riccardo@.discussions.microsoft.com> wrote in message
news:A86E7571-051B-4185-A6EF-110FAFBD67DD@.microsoft.com...
> I've to create an instance reference to my custom assembly by constructor
> method.
> It works well with a Code into the report .rdl like this:
> ---
> <Code>
> Public Obj As MyClass
> Protected Overrides Sub OnInit()
> dim MyArg as String = 4
> Obj = new MyClass(MyArg)
> End Sub
> </Code>
> --
> But i need to pass a real parameter so:
> Obj = new MyClass(Parameters!MyPar.Value)
> it don't work and break with the error
> BC30469 "The reference to a member not shared needs a reference to an
> object"
> (I translate this from the italian version.... sorry for my bad
> english!!!
> How can i do'
> Thanks a lot.|||This not works, but the error is changed with a message box:
" Unable to load the assembly expressions. The expression refers to a
nonexistent parameter in the parameters's collection.".
This is not true, becouse the parameter exists.
Can you help me again?
"Wayne Snyder" wrote:
> Try using this reference.
> Report.Parameters!MyPar.Value
> that should work in the Code.section
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Riccardo" <Riccardo@.discussions.microsoft.com> wrote in message
> news:A86E7571-051B-4185-A6EF-110FAFBD67DD@.microsoft.com...
> > I've to create an instance reference to my custom assembly by constructor
> > method.
> > It works well with a Code into the report .rdl like this:
> > ---
> > <Code>
> > Public Obj As MyClass
> > Protected Overrides Sub OnInit()
> > dim MyArg as String = 4
> > Obj = new MyClass(MyArg)
> > End Sub
> > </Code>
> > --
> > But i need to pass a real parameter so:
> > Obj = new MyClass(Parameters!MyPar.Value)
> > it don't work and break with the error
> > BC30469 "The reference to a member not shared needs a reference to an
> > object"
> > (I translate this from the italian version.... sorry for my bad
> > english!!!
> > How can i do'
> > Thanks a lot.
>
>

passing multiple values to a paramter

Hi,

I'm trying to pass multiple values to a single parameter from a report to a second report. For instance I want to pass the values a user selected in the original report, such as the countries a user select under a Country filter, and once the second report is called, I want that report to filter on those same countries, right now I can only pass one of the values selected to the second report. If someone can let me know if this is possible it'd be much appreciated, thanks in advance.

Nevermind, I figured it out, had to pass

Parameters! <ParameterName> .Label

as the parameter where I was linking the report. This passes the whole array with all the chosen values in it.sql

Passing database names as parameters

Hi all,
I posted this yesterday (thanks Uri Diamant), but there has to be a better
solution.
I have a SQL 2005 instance with 4 dbs in it. In three databases, the structu
re
is identical (data is from 3 seperate companies). Is it possible to access
any of the three databases, from sprocs in the fourth (master) database,
so I don't have to rewrite all the sprocs three times?
Can I pass the database name to a stored procedure, eg
ALTER PROCEDURE [dbo].[usp_Clients_Get]
@.user_ref varchar(20),
@.database_ref varchar(50)
AS
BEGIN
SET NOCOUNT ON
SELECT @.database_ref.[Clients.Co_Name], @.database_ref.[Clients.Client_Ref]
FROM User_Client_Join INNER JOIN
@.database_ref.[Clients] ON [User_Client_Join.Client_Ref] = [Clients.Client_Ref]
WHERE [User_Client_Join].[User_Ref] = @.user_ref
END
I have many queries and I don't want a huge IF...ELSE structure.
Thanks in advance.
JulesJules
Well , you are probably thinjing about dynamic sql. I'm sure you are aware
of downsides of using dynamic sql
alter proc spproc
@.par int,
@.dbname sysname
as
declare @.str as varchar(100)
set @.str=('select * from '+@.dbname+'..orders where orderid='+cast(@.par as
varchar(10)))
exec (@.str)
exec spproc 10248,'northwind'
"Jules Wensley" <juleswensley@.yahoo.co.uk> wrote in message
news:8e34528b4f0b8c81af33ffd7a0a@.news.microsoft.com...
> Hi all,
> I posted this yesterday (thanks Uri Diamant), but there has to be a better
> solution.
> I have a SQL 2005 instance with 4 dbs in it. In three databases, the
> structure is identical (data is from 3 seperate companies). Is it possible
> to access any of the three databases, from sprocs in the fourth (master)
> database, so I don't have to rewrite all the sprocs three times?
> Can I pass the database name to a stored procedure, eg
> ALTER PROCEDURE [dbo].[usp_Clients_Get] @.user_ref varchar(20),
> @.database_ref varchar(50)
> AS
> BEGIN
> SET NOCOUNT ON
> SELECT @.database_ref.[Clients.Co_Name], @.database_ref.[Clients.Client_Ref]
> FROM User_Client_Join INNER JOIN
> @.database_ref.[Clients] ON [User_Client_Join.Client_Ref] =
> [Clients.Client_Ref]
> WHERE [User_Client_Join].[User_Ref] = @.user_ref
> END
> I have many queries and I don't want a huge IF...ELSE structure.
> Thanks in advance.
> Jules
>|||Hi Uri,
The solution you suggest works! What are the downsides of using dynamic sql
in a sproc. If they are the same as in VB, and this is the only way I can
implement this, this will have to do.
Regards
Jules
> Jules
> Well , you are probably thinjing about dynamic sql. I'm sure you are
> aware
> of downsides of using dynamic sql
> alter proc spproc
> @.par int,
> @.dbname sysname
> as
> declare @.str as varchar(100)
> set @.str=('select * from '+@.dbname+'..orders where orderid='+cast(@.par
> as
> varchar(10)))
> exec (@.str)
> exec spproc 10248,'northwind'
> "Jules Wensley" <juleswensley@.yahoo.co.uk> wrote in message
> news:8e34528b4f0b8c81af33ffd7a0a@.news.microsoft.com...
>|||Downside is performance wise. Since the SP is dynamic, the SQL Server has to
recompile it every time it is called. The normal SP is compiled once and
stored in the cache so it is much quicker to use. It may not make any
difference for you so give it a shot.
"Jules Wensley" <juleswensley@.yahoo.co.uk> wrote in message
news:8e34528b91598c81afa7fcca47e@.news.microsoft.com...
> Hi Uri,
> The solution you suggest works! What are the downsides of using dynamic
> sql in a sproc. If they are the same as in VB, and this is the only way I
> can implement this, this will have to do.
> Regards
> Jules
>
>|||Thanks Uri & Grant,
I'd rather do it the non-dynamic way, if is this possible. My client could
be adding more databases in the future.
Regards
Jules
> Downside is performance wise. Since the SP is dynamic, the SQL Server
> has to recompile it every time it is called. The normal SP is compiled
> once and stored in the cache so it is much quicker to use. It may not
> make any difference for you so give it a shot.
> "Jules Wensley" <juleswensley@.yahoo.co.uk> wrote in message
> news:8e34528b91598c81afa7fcca47e@.news.microsoft.com...
>|||Jules,
I think you can achieve this using dynamic SQL, but you will have to create
the SQL command in a string.
e.g. looking at the first bit of your first line.
DECLARE @.SQL nvarchar(1000)
SET @.SQL = 'SELECT ' + @.Database_ref + ".[Clients.Co_Name], ' + etc.....
EXEC (@.SQL)
Robin Hammond
www.enhanceddatasystems.com
"Jules Wensley" <juleswensley@.yahoo.co.uk> wrote in message
news:8e34528b4f0b8c81af33ffd7a0a@.news.microsoft.com...
> Hi all,
> I posted this yesterday (thanks Uri Diamant), but there has to be a better
> solution.
> I have a SQL 2005 instance with 4 dbs in it. In three databases, the
> structure is identical (data is from 3 seperate companies). Is it possible
> to access any of the three databases, from sprocs in the fourth (master)
> database, so I don't have to rewrite all the sprocs three times?
> Can I pass the database name to a stored procedure, eg
> ALTER PROCEDURE [dbo].[usp_Clients_Get] @.user_ref varchar(20),
> @.database_ref varchar(50)
> AS
> BEGIN
> SET NOCOUNT ON
> SELECT @.database_ref.[Clients.Co_Name], @.database_ref.[Clients.Client_Ref]
> FROM User_Client_Join INNER JOIN
> @.database_ref.[Clients] ON [User_Client_Join.Client_Ref] =
> [Clients.Client_Ref]
> WHERE [User_Client_Join].[User_Ref] = @.user_ref
> END
> I have many queries and I don't want a huge IF...ELSE structure.
> Thanks in advance.
> Jules
>|||Read this to know more about Dynamic SAL
http://www.sommarskog.se/dynamic_sql.html
Madhivanan|||Another option you could use is to make mirror copies of procedures in the
customer database which do most of the logic you are looking to impliment
then simply call the procedure in dynamic code. Make sure to use the
sp_executesql call to call the procedure:
@.sql = 'exec ' + @.database_ref + 'dbo.ClentDataProcedure'
exec sp_executesql @.stmt = @.sql
This could mitigate some of the issues with some dynamic implimentations as
the code on the customer db would not itself be dynamic and using
sp_executesql allows for both stored query plans. In addition you could pas
s
through parameters to the dynmic procedure call (see bol under sp_executesql
for the gory details)
--Tony
"Jules Wensley" wrote:

> Thanks Uri & Grant,
> I'd rather do it the non-dynamic way, if is this possible. My client could
> be adding more databases in the future.
> Regards
> Jules
>
>
>|||On Tue, 21 Mar 2006 12:31:23 +0000 (UTC), Jules Wensley wrote:

> What are the downsides of using dynamic sql
>in a sproc.
http://www.sommarskog.se/dynamic_sql.html
Hugo Kornelis, SQL Server MVP

Pasing parameter-value to method in .dll

Hello,
I have a class in a .dll wich I instantiate in reporting services on
the References tab. The instance is called "Myinstance". I also have a
parameter called "parm1".
>From custom code I have:
Protected Overrides Sub OnInit()
MyInstance.company(Parameters!parm1.value)
End Sub
The method company is declared "public void company(string _company)".
My problem is, that I get the error:
"... The definition of the report "/MyReport" is invalid. There is an
error on line 1 of custom code: [BC30469] Reference to a non-shared
member requires an object reference."
If, in custom code, I hard-code the parameter to the method company
instead of trying to get it from a parameter, it works, so it look as
if I try to access the parameter-value before the parameter is
available or I'm accessing the parameter in a wrong way.
I need to pass the value of the parameter "parm1" to a method
"company(string _company)" before the report is build, as fields in
the report will call other functions in the .dll and what they return
depends on the value of "parm1" passed to the method "company".
So, any suggestion? What am I doing wrong?
Thanks in advance
/PeterHey Peter.
Add a variable to your assembly call such as:
Protected Overrides Sub OnInit(parm1 as string)
MyInstance.company(Parameters!parm1.value)
End Sub
Then call you custom code like:
=code.oninit(parameters!parm1.value)
Michael
"Peter" wrote:
> Hello,
> I have a class in a .dll wich I instantiate in reporting services on
> the References tab. The instance is called "Myinstance". I also have a
> parameter called "parm1".
> >From custom code I have:
> Protected Overrides Sub OnInit()
> MyInstance.company(Parameters!parm1.value)
> End Sub
> The method company is declared "public void company(string _company)".
> My problem is, that I get the error:
> "... The definition of the report "/MyReport" is invalid. There is an
> error on line 1 of custom code: [BC30469] Reference to a non-shared
> member requires an object reference."
> If, in custom code, I hard-code the parameter to the method company
> instead of trying to get it from a parameter, it works, so it look as
> if I try to access the parameter-value before the parameter is
> available or I'm accessing the parameter in a wrong way.
> I need to pass the value of the parameter "parm1" to a method
> "company(string _company)" before the report is build, as fields in
> the report will call other functions in the .dll and what they return
> depends on the value of "parm1" passed to the method "company".
> So, any suggestion? What am I doing wrong?
> Thanks in advance
> /Peter
>|||Oops one small change
"Michael C" wrote:
Protected Overrides Sub OnInit(parm1 as string)
MyInstance.company(parm1)
End Sub
Michael
> Hey Peter.
> Add a variable to your assembly call such as:
> Protected Overrides Sub OnInit(parm1 as string)
> MyInstance.company(Parameters!parm1.value)
> End Sub
> Then call you custom code like:
> =code.oninit(parameters!parm1.value)
> Michael
> "Peter" wrote:
> > Hello,
> >
> > I have a class in a .dll wich I instantiate in reporting services on
> > the References tab. The instance is called "Myinstance". I also have a
> > parameter called "parm1".
> >
> > >From custom code I have:
> >
> > Protected Overrides Sub OnInit()
> > MyInstance.company(Parameters!parm1.value)
> > End Sub
> >
> > The method company is declared "public void company(string _company)".
> >
> > My problem is, that I get the error:
> >
> > "... The definition of the report "/MyReport" is invalid. There is an
> > error on line 1 of custom code: [BC30469] Reference to a non-shared
> > member requires an object reference."
> >
> > If, in custom code, I hard-code the parameter to the method company
> > instead of trying to get it from a parameter, it works, so it look as
> > if I try to access the parameter-value before the parameter is
> > available or I'm accessing the parameter in a wrong way.
> >
> > I need to pass the value of the parameter "parm1" to a method
> > "company(string _company)" before the report is build, as fields in
> > the report will call other functions in the .dll and what they return
> > depends on the value of "parm1" passed to the method "company".
> >
> > So, any suggestion? What am I doing wrong?
> >
> > Thanks in advance
> > /Peter
> >
> >|||On 19 Jul., 18:20, Michael C <Micha...@.discussions.microsoft.com>
wrote:
> Oops one small change
> "Michael C" wrote:
> Protected Overrides Sub OnInit(parm1 as string)
> MyInstance.company(parm1)
> End Sub
> Michael
>
> > Hey Peter.
> > Add a variable to your assembly call such as:
> > Protected Overrides Sub OnInit(parm1 as string)
> > MyInstance.company(Parameters!parm1.value)
> > End Sub
> > Then call you custom code like:
> > =code.oninit(parameters!parm1.value)
> > Michael
> > "Peter" wrote:
> > > Hello,
> > > I have a class in a .dll wich I instantiate in reporting services on
> > > the References tab. The instance is called "Myinstance". I also have a
> > > parameter called "parm1".
> > > >From custom code I have:
> > > Protected Overrides Sub OnInit()
> > > MyInstance.company(Parameters!parm1.value)
> > > End Sub
> > > The method company is declared "public void company(string _company)".
> > > My problem is, that I get the error:
> > > "... The definition of the report "/MyReport" is invalid. There is an
> > > error on line 1 of custom code: [BC30469] Reference to a non-shared
> > > member requires an object reference."
> > > If, in custom code, I hard-code the parameter to the method company
> > > instead of trying to get it from a parameter, it works, so it look as
> > > if I try to access the parameter-value before the parameter is
> > > available or I'm accessing the parameter in a wrong way.
> > > I need to pass the value of the parameter "parm1" to a method
> > > "company(string _company)" before the report is build, as fields in
> > > the report will call other functions in the .dll and what they return
> > > depends on the value of "parm1" passed to the method "company".
> > > So, any suggestion? What am I doing wrong?
> > > Thanks in advance
> > > /Peter
Hello Michael,
Thanks for your reply.
There are two issues:
1) I forgot to mention, that pam1 gets its values from a query, so I
cannot call my custom code as "=code.oninit(parameters!parm1.value)"
2) When I tried the custom code in your message, I got this error:
"... There is an error on line 0 of custom code: [BC30284] sub
'OnInit' cannot be declared 'Overrides' because it does not override a
sub in a base class."
What should I do from here ?
Thanks
/Peter|||hey Peter,
Well ...
1) The "Parmameter!Parm1.Value" could be "Fields!MyField.value", or if it is
not generated from the dataset that drives the report (i.e. a secondary
query) then you can use First(Fields!MyField.Value,"MyParmQuery")
2) I haven't worked with Overrides, are you required to have 'Protected
Overrides' in the call, or can you simply state Protected Sub
OnInit(strParm1) or even Public Sub OnInit?
Michael
"Peter" wrote:
> On 19 Jul., 18:20, Michael C <Micha...@.discussions.microsoft.com>
> wrote:
> > Oops one small change
> >
> > "Michael C" wrote:
> >
> > Protected Overrides Sub OnInit(parm1 as string)
> > MyInstance.company(parm1)
> > End Sub
> >
> > Michael
> >
> >
> >
> > > Hey Peter.
> >
> > > Add a variable to your assembly call such as:
> >
> > > Protected Overrides Sub OnInit(parm1 as string)
> > > MyInstance.company(Parameters!parm1.value)
> > > End Sub
> >
> > > Then call you custom code like:
> >
> > > =code.oninit(parameters!parm1.value)
> >
> > > Michael
> >
> > > "Peter" wrote:
> >
> > > > Hello,
> >
> > > > I have a class in a .dll wich I instantiate in reporting services on
> > > > the References tab. The instance is called "Myinstance". I also have a
> > > > parameter called "parm1".
> >
> > > > >From custom code I have:
> >
> > > > Protected Overrides Sub OnInit()
> > > > MyInstance.company(Parameters!parm1.value)
> > > > End Sub
> >
> > > > The method company is declared "public void company(string _company)".
> >
> > > > My problem is, that I get the error:
> >
> > > > "... The definition of the report "/MyReport" is invalid. There is an
> > > > error on line 1 of custom code: [BC30469] Reference to a non-shared
> > > > member requires an object reference."
> >
> > > > If, in custom code, I hard-code the parameter to the method company
> > > > instead of trying to get it from a parameter, it works, so it look as
> > > > if I try to access the parameter-value before the parameter is
> > > > available or I'm accessing the parameter in a wrong way.
> >
> > > > I need to pass the value of the parameter "parm1" to a method
> > > > "company(string _company)" before the report is build, as fields in
> > > > the report will call other functions in the .dll and what they return
> > > > depends on the value of "parm1" passed to the method "company".
> >
> > > > So, any suggestion? What am I doing wrong?
> >
> > > > Thanks in advance
> > > > /Peter
> Hello Michael,
> Thanks for your reply.
> There are two issues:
> 1) I forgot to mention, that pam1 gets its values from a query, so I
> cannot call my custom code as "=code.oninit(parameters!parm1.value)"
> 2) When I tried the custom code in your message, I got this error:
> "... There is an error on line 0 of custom code: [BC30284] sub
> 'OnInit' cannot be declared 'Overrides' because it does not override a
> sub in a base class."
> What should I do from here ?
> Thanks
> /Peter
>|||On 20 Jul., 18:56, Michael C <Micha...@.discussions.microsoft.com>
wrote:
> hey Peter,
> Well ...
> 1) The "Parmameter!Parm1.Value" could be "Fields!MyField.value", or if it is
> not generated from the dataset that drives the report (i.e. a secondary
> query) then you can use First(Fields!MyField.Value,"MyParmQuery")
> 2) I haven't worked with Overrides, are you required to have 'Protected
> Overrides' in the call, or can you simply state Protected Sub
> OnInit(strParm1) or even Public Sub OnInit?
> Michael
Thanks again Michael,
I have to declared it 'Protected' otherwise it will complain that I
use a different access level.
If I declared it Protected Overrides Sub OnInit(DataArea As String),
that is, with one parameter, I get the error:
sub 'OnInit' cannot be declared 'Overrides' because it does not
override a sub in a base class.
If I declare it without any parameters as Protected Overrides Sub
OnInit(), I get the error
Reference to a non-shared member requires an object reference
If I declare it without 'Overrides' (with and without a parameter) I
get the error
Reference to a non-shared member requires an object reference
So I'm stuck again. I need to use a function that is executed before
the report is generated. I need it to call a method i my .dll with an
argument which is based on a selection from a parameter which in turn
is based on a dataset.
What should I try from here?
Thanks
/Peter