Calling sp in an expression

Hi All,
Is it possible to call a stored procedure (or a dataset that is based on an
sp) from the expression property in a report?
Can the "Code" tab be used to call the stored procedure and that "code" be
referred to from the expression property in a report (if the above is not
possible)?
This is what I am doing (or want to do):
I have a custom assembly that I don't want to put in the
MsSql\ReportServer\Bin folder. I want the custom assembly (DLL) to be in the
folder of my choice and under my control since the folder structure of
Microsoft for SQL Server 2005 (CTP July version) has changed.
So I created an Assembly in SQL Server 2005 by referring to this DLL. I
created a stored procedure that calls the assembly. Now I want to call this
stored procedure from the expression property of the report since I will be
passing the arguments from here.
Any help is appreciated. Thanks.No. The only place that a stored procedure can be called (unless you use a
custom assembly which is what you are trying to get away from) from is by
creating a dataset. The dataset query parameter mapping can use an
expression. If you have just a single record coming back then you can use
First aggregate to refer to it in your report. I do this all the time. Not
sure if that solves your problem or not.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:4626D487-0E50-4F89-892C-59D33DB800C3@.microsoft.com...
> Hi All,
> Is it possible to call a stored procedure (or a dataset that is based on
> an
> sp) from the expression property in a report?
> Can the "Code" tab be used to call the stored procedure and that "code" be
> referred to from the expression property in a report (if the above is not
> possible)?
> This is what I am doing (or want to do):
> I have a custom assembly that I don't want to put in the
> MsSql\ReportServer\Bin folder. I want the custom assembly (DLL) to be in
> the
> folder of my choice and under my control since the folder structure of
> Microsoft for SQL Server 2005 (CTP July version) has changed.
> So I created an Assembly in SQL Server 2005 by referring to this DLL. I
> created a stored procedure that calls the assembly. Now I want to call
> this
> stored procedure from the expression property of the report since I will
> be
> passing the arguments from here.
> Any help is appreciated. Thanks.|||Thanks for you prompt reply.
Can you please explain with an example - may be this will work.
"Bruce L-C [MVP]" wrote:
> No. The only place that a stored procedure can be called (unless you use a
> custom assembly which is what you are trying to get away from) from is by
> creating a dataset. The dataset query parameter mapping can use an
> expression. If you have just a single record coming back then you can use
> First aggregate to refer to it in your report. I do this all the time. Not
> sure if that solves your problem or not.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "KMP" <KMP@.discussions.microsoft.com> wrote in message
> news:4626D487-0E50-4F89-892C-59D33DB800C3@.microsoft.com...
> > Hi All,
> > Is it possible to call a stored procedure (or a dataset that is based on
> > an
> > sp) from the expression property in a report?
> >
> > Can the "Code" tab be used to call the stored procedure and that "code" be
> > referred to from the expression property in a report (if the above is not
> > possible)?
> >
> > This is what I am doing (or want to do):
> > I have a custom assembly that I don't want to put in the
> > MsSql\ReportServer\Bin folder. I want the custom assembly (DLL) to be in
> > the
> > folder of my choice and under my control since the folder structure of
> > Microsoft for SQL Server 2005 (CTP July version) has changed.
> >
> > So I created an Assembly in SQL Server 2005 by referring to this DLL. I
> > created a stored procedure that calls the assembly. Now I want to call
> > this
> > stored procedure from the expression property of the report since I will
> > be
> > passing the arguments from here.
> >
> > Any help is appreciated. Thanks.
>
>|||Which part is confusing to you? Mapping a query parameter to an expression?
Using the First aggragate?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:1C743F5A-368F-4F98-9EC5-42FA3F8860CB@.microsoft.com...
> Thanks for you prompt reply.
> Can you please explain with an example - may be this will work.
> "Bruce L-C [MVP]" wrote:
>> No. The only place that a stored procedure can be called (unless you use
>> a
>> custom assembly which is what you are trying to get away from) from is by
>> creating a dataset. The dataset query parameter mapping can use an
>> expression. If you have just a single record coming back then you can use
>> First aggregate to refer to it in your report. I do this all the time.
>> Not
>> sure if that solves your problem or not.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "KMP" <KMP@.discussions.microsoft.com> wrote in message
>> news:4626D487-0E50-4F89-892C-59D33DB800C3@.microsoft.com...
>> > Hi All,
>> > Is it possible to call a stored procedure (or a dataset that is based
>> > on
>> > an
>> > sp) from the expression property in a report?
>> >
>> > Can the "Code" tab be used to call the stored procedure and that "code"
>> > be
>> > referred to from the expression property in a report (if the above is
>> > not
>> > possible)?
>> >
>> > This is what I am doing (or want to do):
>> > I have a custom assembly that I don't want to put in the
>> > MsSql\ReportServer\Bin folder. I want the custom assembly (DLL) to be
>> > in
>> > the
>> > folder of my choice and under my control since the folder structure of
>> > Microsoft for SQL Server 2005 (CTP July version) has changed.
>> >
>> > So I created an Assembly in SQL Server 2005 by referring to this DLL. I
>> > created a stored procedure that calls the assembly. Now I want to call
>> > this
>> > stored procedure from the expression property of the report since I
>> > will
>> > be
>> > passing the arguments from here.
>> >
>> > Any help is appreciated. Thanks.
>>|||Mapping a query parameter to an expression.
"Bruce L-C [MVP]" wrote:
> Which part is confusing to you? Mapping a query parameter to an expression?
> Using the First aggragate?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "KMP" <KMP@.discussions.microsoft.com> wrote in message
> news:1C743F5A-368F-4F98-9EC5-42FA3F8860CB@.microsoft.com...
> > Thanks for you prompt reply.
> >
> > Can you please explain with an example - may be this will work.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> No. The only place that a stored procedure can be called (unless you use
> >> a
> >> custom assembly which is what you are trying to get away from) from is by
> >> creating a dataset. The dataset query parameter mapping can use an
> >> expression. If you have just a single record coming back then you can use
> >> First aggregate to refer to it in your report. I do this all the time.
> >> Not
> >> sure if that solves your problem or not.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "KMP" <KMP@.discussions.microsoft.com> wrote in message
> >> news:4626D487-0E50-4F89-892C-59D33DB800C3@.microsoft.com...
> >> > Hi All,
> >> > Is it possible to call a stored procedure (or a dataset that is based
> >> > on
> >> > an
> >> > sp) from the expression property in a report?
> >> >
> >> > Can the "Code" tab be used to call the stored procedure and that "code"
> >> > be
> >> > referred to from the expression property in a report (if the above is
> >> > not
> >> > possible)?
> >> >
> >> > This is what I am doing (or want to do):
> >> > I have a custom assembly that I don't want to put in the
> >> > MsSql\ReportServer\Bin folder. I want the custom assembly (DLL) to be
> >> > in
> >> > the
> >> > folder of my choice and under my control since the folder structure of
> >> > Microsoft for SQL Server 2005 (CTP July version) has changed.
> >> >
> >> > So I created an Assembly in SQL Server 2005 by referring to this DLL. I
> >> > created a stored procedure that calls the assembly. Now I want to call
> >> > this
> >> > stored procedure from the expression property of the report since I
> >> > will
> >> > be
> >> > passing the arguments from here.
> >> >
> >> > Any help is appreciated. Thanks.
> >>
> >>
> >>
>
>|||RS makes this concept murky because it automatically creates a Report
Parameter for every query parameter. However, you can map query parameters
to the same report parameter or to an expression. For instance, let's say I
have two datasets, they both need from and to dates. Let's further say that
the query parameters are named differently. For instance @.FromDate and
@.StartDate. Reporting services will create two report parameters. However
you can map both @.FromDate and @.StartDate to the same report parameter.
Click on the ... in the dataset tab, Parameters tab. On the left is the
query parameter on the right is what it is mapped to. That is where you
would map both to the same report parameter. OK, now, mapping to an
expression, you can click on the combo box and set expression.
For instance this calls some code behind report:
=Code.SomecodeOfMine(Parameters!Param1.Value)
This sets to the current day/time
=Now
Etc. Your expression can be pretty much anything.
When you pick expression you go to the expression builder.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:74430973-4547-419F-AFFC-29CA4CE68667@.microsoft.com...
> Mapping a query parameter to an expression.
> "Bruce L-C [MVP]" wrote:
>> Which part is confusing to you? Mapping a query parameter to an
>> expression?
>> Using the First aggragate?
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "KMP" <KMP@.discussions.microsoft.com> wrote in message
>> news:1C743F5A-368F-4F98-9EC5-42FA3F8860CB@.microsoft.com...
>> > Thanks for you prompt reply.
>> >
>> > Can you please explain with an example - may be this will work.
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> No. The only place that a stored procedure can be called (unless you
>> >> use
>> >> a
>> >> custom assembly which is what you are trying to get away from) from is
>> >> by
>> >> creating a dataset. The dataset query parameter mapping can use an
>> >> expression. If you have just a single record coming back then you can
>> >> use
>> >> First aggregate to refer to it in your report. I do this all the time.
>> >> Not
>> >> sure if that solves your problem or not.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "KMP" <KMP@.discussions.microsoft.com> wrote in message
>> >> news:4626D487-0E50-4F89-892C-59D33DB800C3@.microsoft.com...
>> >> > Hi All,
>> >> > Is it possible to call a stored procedure (or a dataset that is
>> >> > based
>> >> > on
>> >> > an
>> >> > sp) from the expression property in a report?
>> >> >
>> >> > Can the "Code" tab be used to call the stored procedure and that
>> >> > "code"
>> >> > be
>> >> > referred to from the expression property in a report (if the above
>> >> > is
>> >> > not
>> >> > possible)?
>> >> >
>> >> > This is what I am doing (or want to do):
>> >> > I have a custom assembly that I don't want to put in the
>> >> > MsSql\ReportServer\Bin folder. I want the custom assembly (DLL) to
>> >> > be
>> >> > in
>> >> > the
>> >> > folder of my choice and under my control since the folder structure
>> >> > of
>> >> > Microsoft for SQL Server 2005 (CTP July version) has changed.
>> >> >
>> >> > So I created an Assembly in SQL Server 2005 by referring to this
>> >> > DLL. I
>> >> > created a stored procedure that calls the assembly. Now I want to
>> >> > call
>> >> > this
>> >> > stored procedure from the expression property of the report since I
>> >> > will
>> >> > be
>> >> > passing the arguments from here.
>> >> >
>> >> > Any help is appreciated. Thanks.
>> >>
>> >>
>> >>
>>