| Groups | Blog | Home |
sql server reporting services : 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.
Thanks for you prompt reply.
Can you please explain with an example - may be this will work. [quoted text, click to view] "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.
[quoted text, click to view] "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. > >> > >> > >> > >
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 [quoted text, click to view] "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 [quoted text, click to view] "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 [quoted text, click to view] "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. >> >> >> >> >> >> >> >> >>
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |