|
|
You're in the
sql server reporting services
group:Reporting Svcs - stored procedure
sql server reporting services:
Services. All of which are based on stored procedures, none of which require any parameters. Most if not all of the report is calculated. I am struggling badly to get even the most basic of the fields in place w/the correct expressions. The only one I actually got to accept the expression was a Date field, this is the calculation: =SELECT CONVERT(char(10),dateadd(dd,-0,GetDate()),1) It, too, fails to preview, indicating the value expression for the field contains an error. Again, all of the other report items are calculated fields. SUMs, totals, money formats, CASE statements, CASTs, etc. Using the report designer/report wizard to 'do it for me' only returns Date and a couple fields that are not even in my resultset. like one of my values in the report is 'Total Trades'....one of the fields listed as available is 'ID__Total__' Further, in VS I've got the report open and am on the data tab, i hit the Run option, I receive this error: An error occurred while retrieving the parameters in the query. Could not locate entry in sysdatabases for database 'EXEC databasename'. No entry found with that name. Make sure that the name is entered correctly.' Clearly I am doing something very wrong with the stored procedure definition. Like I said, all of these reports are based on procedures, can anybody give me some suggestion/direction on the right way to call the procs? They run w/out parameters...EXEC databasename.dbo.proc that's it. Please advise.
Pick the command type as stored procedure and then just enter the name of
the stored procedure. When you click on the ! you should get back the data and the fields. If you do not get the field list populated then click on the refresh fields button (to the right of the ..., it looks like the refresh button for IE). Note that RS only works with the first resultset returned. It cannot handle mutliple resultsets. -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "Lynn" <Lynn@discussions.microsoft.com> wrote in message news:EF6A6CBC-083B-4537-83D9-D763A8ABA056@microsoft.com... > Hi. I have a need to create a number of different reports via Reporting > Services. All of which are based on stored procedures, none of which > require > any parameters. Most if not all of the report is calculated. I am > struggling badly to get even the most basic of the fields in place w/the > correct expressions. The only one I actually got to accept the expression > was a Date field, this is the calculation: > > =SELECT CONVERT(char(10),dateadd(dd,-0,GetDate()),1) > > It, too, fails to preview, indicating the value expression for the field > contains an error. Again, all of the other report items are calculated > fields. SUMs, totals, money formats, CASE statements, CASTs, etc. Using > the > report designer/report wizard to 'do it for me' only returns Date and a > couple fields that are not even in my resultset. like one of my values in > the report is 'Total Trades'....one of the fields listed as available is > 'ID__Total__' > > Further, in VS I've got the report open and am on the data tab, i hit the > Run option, I receive this error: An error occurred while retrieving the > parameters in the query. Could not locate entry in sysdatabases for > database > 'EXEC databasename'. No entry found with that name. Make sure that the > name > is entered correctly.' > > Clearly I am doing something very wrong with the stored procedure > definition. Like I said, all of these reports are based on procedures, > can > anybody give me some suggestion/direction on the right way to call the > procs? > They run w/out parameters...EXEC databasename.dbo.proc that's it. > Please advise. > > > -- Lynn
oh, bruce, that is great. I can't believe it was as simple as that. one
remaining problem, though. one of my reports is like this: Date Total Trades Symbols Traded Total $ ---------- ------------------ ------------------ -------------------- 02/07/2006 333,333 333 33,333,333,333.33 #Stocks Volume Total $ ------------- ------------- -------------------- 333,333 33,333,333 3,333,333,333.33 EndPoint Liquidity #Trades Volume Total $ ----------- ------------ -------------- -------------- --------------------- AAAA Add 114,729 21,797,575 583,102,631.33 AAAA Opening 1 27 3,412.53 AAAA Remove 30,836 8,330,843 444,521,928.10 AAAA Route 41 4,543 103,291.31 BBBB Add 26,997 9,940,201 178,600,674.78 BBBB Opening 8,089 1,448,143 44,276,419.41 BBBB Remove 3,406 1,719,403 40,267,318.25 CCCC Add 83,781 22,443,732 746,570,071.43 CCCC Remove 35,756 12,250,359 483,329,796.97 CCCC Route 1,088 244,697 6,655,913.26 DDDD Route 2 62,500 2,539,375.00 DDDD Add 2,354 1,262,589 19,136,233.44 DDDD Remove 258 195,019 6,227,451.69 I only receive the top line in the resultset: Date Total Trades Symbols Traded Total $ ---------- ------------------ ------------------ -------------------- 02/07/2006 333,333 333 33,333,333,333.33 I assume this is what you mean when you say it will only handle the 1st resultset. All of my reports have multiple 'chunks' in the body. Do you have any idea how I can achieve this report via reporting svcs? Unfortunately, this is very important. Please do advise. --Lynn [quoted text, click to view] "Bruce L-C [MVP]" wrote:
> Pick the command type as stored procedure and then just enter the name of > the stored procedure. When you click on the ! you should get back the data > and the fields. If you do not get the field list populated then click on the > refresh fields button (to the right of the ..., it looks like the refresh > button for IE). > > Note that RS only works with the first resultset returned. It cannot handle > mutliple resultsets. > > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > "Lynn" <Lynn@discussions.microsoft.com> wrote in message > news:EF6A6CBC-083B-4537-83D9-D763A8ABA056@microsoft.com... > > Hi. I have a need to create a number of different reports via Reporting > > Services. All of which are based on stored procedures, none of which > > require > > any parameters. Most if not all of the report is calculated. I am > > struggling badly to get even the most basic of the fields in place w/the > > correct expressions. The only one I actually got to accept the expression > > was a Date field, this is the calculation: > > > > =SELECT CONVERT(char(10),dateadd(dd,-0,GetDate()),1) > > > > It, too, fails to preview, indicating the value expression for the field > > contains an error. Again, all of the other report items are calculated > > fields. SUMs, totals, money formats, CASE statements, CASTs, etc. Using > > the > > report designer/report wizard to 'do it for me' only returns Date and a > > couple fields that are not even in my resultset. like one of my values in > > the report is 'Total Trades'....one of the fields listed as available is > > 'ID__Total__' > > > > Further, in VS I've got the report open and am on the data tab, i hit the > > Run option, I receive this error: An error occurred while retrieving the > > parameters in the query. Could not locate entry in sysdatabases for > > database > > 'EXEC databasename'. No entry found with that name. Make sure that the > > name > > is entered correctly.' > > > > Clearly I am doing something very wrong with the stored procedure > > definition. Like I said, all of these reports are based on procedures, > > can > > anybody give me some suggestion/direction on the right way to call the > > procs? > > They run w/out parameters...EXEC databasename.dbo.proc that's it. > > Please advise. > > > > > > -- Lynn > >
Bruce, I'm not sure I follow. Can you elaborate a little more, please, on
the second, possibly third procedure? -- Lynn [quoted text, click to view] "Bruce L-C [MVP]" wrote:
> This is three resultsets. RS does not have the ability to handle that. There > is no work around from RS side. You could create another stored procedure > that calls this one and have it return just one of the resultsets. > > RS can deal with multiple datasets quite well, but each stored procedure > call can only return one. > > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > "Lynn" <Lynn@discussions.microsoft.com> wrote in message > news:E1AFA910-1FF4-4DC8-A3F0-FC1EADE8DFD5@microsoft.com... > > oh, bruce, that is great. I can't believe it was as simple as that. one > > remaining problem, though. one of my reports is like this: > > > > Date Total Trades Symbols Traded Total $ > > ---------- ------------------ ------------------ -------------------- > > 02/07/2006 333,333 333 33,333,333,333.33 > > > > #Stocks Volume Total $ > > ------------- ------------- -------------------- > > 333,333 33,333,333 3,333,333,333.33 > > > > EndPoint Liquidity #Trades Volume Total $ > > ----------- ------------ -------------- -------------- --------------------- > > AAAA Add 114,729 21,797,575 > > 583,102,631.33 > > AAAA Opening 1 27 > > 3,412.53 > > AAAA Remove 30,836 8,330,843 > > 444,521,928.10 > > AAAA Route 41 4,543 > > 103,291.31 > > BBBB Add 26,997 9,940,201 > > 178,600,674.78 > > BBBB Opening 8,089 1,448,143 > > 44,276,419.41 > > BBBB Remove 3,406 1,719,403 > > 40,267,318.25 > > CCCC Add 83,781 22,443,732 > > 746,570,071.43 > > CCCC Remove 35,756 12,250,359 > > 483,329,796.97 > > CCCC Route 1,088 244,697 > > 6,655,913.26 > > DDDD Route 2 62,500 > > 2,539,375.00 > > DDDD Add 2,354 1,262,589 > > 19,136,233.44 > > DDDD Remove 258 195,019 > > 6,227,451.69 > > > > I only receive the top line in the resultset: > > > > Date Total Trades Symbols Traded Total $ > > ---------- ------------------ ------------------ -------------------- > > 02/07/2006 333,333 333 33,333,333,333.33 > > > > I assume this is what you mean when you say it will only handle the 1st > > resultset. All of my reports have multiple 'chunks' in the body. Do you > > have any idea how I can achieve this report via reporting svcs? > > Unfortunately, this is very important. Please do advise. > > > > --Lynn > > > > > > "Bruce L-C [MVP]" wrote: > > > >> Pick the command type as stored procedure and then just enter the name of > >> the stored procedure. When you click on the ! you should get back the > >> data > >> and the fields. If you do not get the field list populated then click on > >> the > >> refresh fields button (to the right of the ..., it looks like the refresh > >> button for IE). > >> > >> Note that RS only works with the first resultset returned. It cannot > >> handle > >> mutliple resultsets. > >> > >> > >> -- > >> Bruce Loehle-Conger > >> MVP SQL Server Reporting Services > >> > >> "Lynn" <Lynn@discussions.microsoft.com> wrote in message > >> news:EF6A6CBC-083B-4537-83D9-D763A8ABA056@microsoft.com... > >> > Hi. I have a need to create a number of different reports via > >> > Reporting > >> > Services. All of which are based on stored procedures, none of which > >> > require > >> > any parameters. Most if not all of the report is calculated. I am > >> > struggling badly to get even the most basic of the fields in place > >> > w/the > >> > correct expressions. The only one I actually got to accept the > >> > expression > >> > was a Date field, this is the calculation: > >> > > >> > =SELECT CONVERT(char(10),dateadd(dd,-0,GetDate()),1) > >> > > >> > It, too, fails to preview, indicating the value expression for the > >> > field > >> > contains an error. Again, all of the other report items are calculated > >> > fields. SUMs, totals, money formats, CASE statements, CASTs, etc. > >> > Using > >> > the > >> > report designer/report wizard to 'do it for me' only returns Date and a > >> > couple fields that are not even in my resultset. like one of my values > >> > in > >> > the report is 'Total Trades'....one of the fields listed as available > >> > is > >> > 'ID__Total__' > >> > > >> > Further, in VS I've got the report open and am on the data tab, i hit > >> > the > >> > Run option, I receive this error: An error occurred while retrieving > >> > the > >> > parameters in the query. Could not locate entry in sysdatabases for > >> > database > >> > 'EXEC databasename'. No entry found with that name. Make sure that the > >> > name > >> > is entered correctly.' > >> > > >> > Clearly I am doing something very wrong with the stored procedure > >> > definition. Like I said, all of these reports are based on procedures, > >> > can > >> > anybody give me some suggestion/direction on the right way to call the > >> > procs? > >> > They run w/out parameters...EXEC databasename.dbo.proc that's it. > >> > Please advise. > >> > > >> > > >> > -- Lynn > >> > >> > >> > >
This is three resultsets. RS does not have the ability to handle that. There
is no work around from RS side. You could create another stored procedure that calls this one and have it return just one of the resultsets. RS can deal with multiple datasets quite well, but each stored procedure call can only return one. -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "Lynn" <Lynn@discussions.microsoft.com> wrote in message news:E1AFA910-1FF4-4DC8-A3F0-FC1EADE8DFD5@microsoft.com... > oh, bruce, that is great. I can't believe it was as simple as that. one > remaining problem, though. one of my reports is like this: > > Date Total Trades Symbols Traded Total $ > ---------- ------------------ ------------------ -------------------- > 02/07/2006 333,333 333 33,333,333,333.33 > > #Stocks Volume Total $ > ------------- ------------- -------------------- > 333,333 33,333,333 3,333,333,333.33 > > EndPoint Liquidity #Trades Volume Total $ > ----------- ------------ -------------- -------------- --------------------- > AAAA Add 114,729 21,797,575 > 583,102,631.33 > AAAA Opening 1 27 > 3,412.53 > AAAA Remove 30,836 8,330,843 > 444,521,928.10 > AAAA Route 41 4,543 > 103,291.31 > BBBB Add 26,997 9,940,201 > 178,600,674.78 > BBBB Opening 8,089 1,448,143 > 44,276,419.41 > BBBB Remove 3,406 1,719,403 > 40,267,318.25 > CCCC Add 83,781 22,443,732 > 746,570,071.43 > CCCC Remove 35,756 12,250,359 > 483,329,796.97 > CCCC Route 1,088 244,697 > 6,655,913.26 > DDDD Route 2 62,500 > 2,539,375.00 > DDDD Add 2,354 1,262,589 > 19,136,233.44 > DDDD Remove 258 195,019 > 6,227,451.69 > > I only receive the top line in the resultset: > > Date Total Trades Symbols Traded Total $ > ---------- ------------------ ------------------ -------------------- > 02/07/2006 333,333 333 33,333,333,333.33 > > I assume this is what you mean when you say it will only handle the 1st > resultset. All of my reports have multiple 'chunks' in the body. Do you > have any idea how I can achieve this report via reporting svcs? > Unfortunately, this is very important. Please do advise. > > --Lynn > > > "Bruce L-C [MVP]" wrote: > >> Pick the command type as stored procedure and then just enter the name of >> the stored procedure. When you click on the ! you should get back the >> data >> and the fields. If you do not get the field list populated then click on >> the >> refresh fields button (to the right of the ..., it looks like the refresh >> button for IE). >> >> Note that RS only works with the first resultset returned. It cannot >> handle >> mutliple resultsets. >> >> >> -- >> Bruce Loehle-Conger >> MVP SQL Server Reporting Services >> >> "Lynn" <Lynn@discussions.microsoft.com> wrote in message >> news:EF6A6CBC-083B-4537-83D9-D763A8ABA056@microsoft.com... >> > Hi. I have a need to create a number of different reports via >> > Reporting >> > Services. All of which are based on stored procedures, none of which >> > require >> > any parameters. Most if not all of the report is calculated. I am >> > struggling badly to get even the most basic of the fields in place >> > w/the >> > correct expressions. The only one I actually got to accept the >> > expression >> > was a Date field, this is the calculation: >> > >> > =SELECT CONVERT(char(10),dateadd(dd,-0,GetDate()),1) >> > >> > It, too, fails to preview, indicating the value expression for the >> > field >> > contains an error. Again, all of the other report items are calculated >> > fields. SUMs, totals, money formats, CASE statements, CASTs, etc. >> > Using >> > the >> > report designer/report wizard to 'do it for me' only returns Date and a >> > couple fields that are not even in my resultset. like one of my values >> > in >> > the report is 'Total Trades'....one of the fields listed as available >> > is >> > 'ID__Total__' >> > >> > Further, in VS I've got the report open and am on the data tab, i hit >> > the >> > Run option, I receive this error: An error occurred while retrieving >> > the >> > parameters in the query. Could not locate entry in sysdatabases for >> > database >> > 'EXEC databasename'. No entry found with that name. Make sure that the >> > name >> > is entered correctly.' >> > >> > Clearly I am doing something very wrong with the stored procedure >> > definition. Like I said, all of these reports are based on procedures, >> > can >> > anybody give me some suggestion/direction on the right way to call the >> > procs? >> > They run w/out parameters...EXEC databasename.dbo.proc that's it. >> > Please advise. >> > >> > >> > -- Lynn >> >> >>
well, bruce, i'm still not sure i know what to do w/these parameters...how
will i get one parameter to do one resultset and get the other resultsets w/parameters 2 and 3? but, i will see if i can walk through this. -- Lynn [quoted text, click to view] "Bruce L-C [MVP]" wrote:
> Configure your existing stored procedure to accept a parameter (you can make > it optional so that it doesn't break existing code). > > Then call it three times in RS with a static value and create three > datasets. > For instance dataset1 > yourproc 1 > > Dataset2 > yourproc 2 > > etc. > > Then in your stored procedure return the appropriate resultset based on the > parameter. > > Downside is that your sp is being run three times. > > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > "Lynn" <Lynn@discussions.microsoft.com> wrote in message > news:E8E34B6C-A36A-4576-BE8C-AB61866EEC80@microsoft.com... > > Bruce, I'm not sure I follow. Can you elaborate a little more, please, on > > the second, possibly third procedure? > > -- Lynn > > > > > > "Bruce L-C [MVP]" wrote: > > > >> This is three resultsets. RS does not have the ability to handle that. > >> There > >> is no work around from RS side. You could create another stored procedure > >> that calls this one and have it return just one of the resultsets. > >> > >> RS can deal with multiple datasets quite well, but each stored procedure > >> call can only return one. > >> > >> > >> -- > >> Bruce Loehle-Conger > >> MVP SQL Server Reporting Services > >> > >> "Lynn" <Lynn@discussions.microsoft.com> wrote in message > >> news:E1AFA910-1FF4-4DC8-A3F0-FC1EADE8DFD5@microsoft.com... > >> > oh, bruce, that is great. I can't believe it was as simple as that. > >> > one > >> > remaining problem, though. one of my reports is like this: > >> > > >> > Date Total Trades Symbols Traded Total $ > >> > ---------- ------------------ ------------------ -------------------- > >> > 02/07/2006 333,333 333 33,333,333,333.33 > >> > > >> > #Stocks Volume Total $ > >> > ------------- ------------- -------------------- > >> > 333,333 33,333,333 3,333,333,333.33 > >> > > >> > EndPoint Liquidity #Trades Volume Total $ > >> > ----------- ------------ -------------- -------------- --------------------- > >> > AAAA Add 114,729 21,797,575 > >> > 583,102,631.33 > >> > AAAA Opening 1 27 > >> > 3,412.53 > >> > AAAA Remove 30,836 8,330,843 > >> > 444,521,928.10 > >> > AAAA Route 41 4,543 > >> > 103,291.31 > >> > BBBB Add 26,997 9,940,201 > >> > 178,600,674.78 > >> > BBBB Opening 8,089 1,448,143 > >> > 44,276,419.41 > >> > BBBB Remove 3,406 1,719,403 > >> > 40,267,318.25 > >> > CCCC Add 83,781 22,443,732 > >> > 746,570,071.43 > >> > CCCC Remove 35,756 12,250,359 > >> > 483,329,796.97 > >> > CCCC Route 1,088 244,697 > >> > 6,655,913.26 > >> > DDDD Route 2 62,500 > >> > 2,539,375.00 > >> > DDDD Add 2,354 1,262,589 > >> > 19,136,233.44 > >> > DDDD Remove 258 195,019 > >> > 6,227,451.69 > >> > > >> > I only receive the top line in the resultset: > >> > > >> > Date Total Trades Symbols Traded Total $ > >> > ---------- ------------------ ------------------ -------------------- > >> > 02/07/2006 333,333 333 33,333,333,333.33 > >> > > >> > I assume this is what you mean when you say it will only handle the 1st > >> > resultset. All of my reports have multiple 'chunks' in the body. Do > >> > you > >> > have any idea how I can achieve this report via reporting svcs? > >> > Unfortunately, this is very important. Please do advise. > >> > > >> > --Lynn > >> > > >> > > >> > "Bruce L-C [MVP]" wrote: > >> > > >> >> Pick the command type as stored procedure and then just enter the name > >> >> of > >> >> the stored procedure. When you click on the ! you should get back the > >> >> data > >> >> and the fields. If you do not get the field list populated then click > >> >> on > >> >> the > >> >> refresh fields button (to the right of the ..., it looks like the > >> >> refresh > >> >> button for IE). > >> >> > >> >> Note that RS only works with the first resultset returned. It cannot > >> >> handle > >> >> mutliple resultsets. > >> >> > >> >> > >> >> -- > >> >> Bruce Loehle-Conger > >> >> MVP SQL Server Reporting Services > >> >> > >> >> "Lynn" <Lynn@discussions.microsoft.com> wrote in message > >> >> news:EF6A6CBC-083B-4537-83D9-D763A8ABA056@microsoft.com... > >> >> > Hi. I have a need to create a number of different reports via > >> >> > Reporting > >> >> > Services. All of which are based on stored procedures, none of > >> >> > which > >> >> > require > >> >> > any parameters. Most if not all of the report is calculated. I am > >> >> > struggling badly to get even the most basic of the fields in place > >> >> > w/the > >> >> > correct expressions. The only one I actually got to accept the > >> >> > expression > >> >> > was a Date field, this is the calculation: > >> >> > > >> >> > =SELECT CONVERT(char(10),dateadd(dd,-0,GetDate()),1) > >> >> > > >> >> > It, too, fails to preview, indicating the value expression for the > >> >> > field > >> >> > contains an error. Again, all of the other report items are > >> >> > calculated > >> >> > fields. SUMs, totals, money formats, CASE statements, CASTs, etc. > >> >> > Using > >> >> > the > >> >> > report designer/report wizard to 'do it for me' only returns Date > >> >> > and a > >> >> > couple fields that are not even in my resultset. like one of my > >> >> > values > >> >> > in > >> >> > the report is 'Total Trades'....one of the fields listed as > >> >> > available > >> >> > is > >> >> > 'ID__Total__' > >> >> > > >> >> > Further, in VS I've got the report open and am on the data tab, i > >> >> > hit > >> >> > the > >> >> > Run option, I receive this error: An error occurred while > >> >> > retrieving > >> >> > the > >> >> > parameters in the query. Could not locate entry in sysdatabases for > >> >> > database > >> >> > 'EXEC databasename'. No entry found with that name. Make sure that > >> >> > the > >> >> > name > >> >> > is entered correctly.' > >> >> > > >> >> > Clearly I am doing something very wrong with the stored procedure > >> >> > definition. Like I said, all of these reports are based on > >> >> > procedures, > >> >> > can > >> >> > anybody give me some suggestion/direction on the right way to call
Configure your existing stored procedure to accept a parameter (you can make
it optional so that it doesn't break existing code). Then call it three times in RS with a static value and create three datasets. For instance dataset1 yourproc 1 Dataset2 yourproc 2 etc. Then in your stored procedure return the appropriate resultset based on the parameter. Downside is that your sp is being run three times. -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "Lynn" <Lynn@discussions.microsoft.com> wrote in message news:E8E34B6C-A36A-4576-BE8C-AB61866EEC80@microsoft.com... > Bruce, I'm not sure I follow. Can you elaborate a little more, please, on > the second, possibly third procedure? > -- Lynn > > > "Bruce L-C [MVP]" wrote: > >> This is three resultsets. RS does not have the ability to handle that. >> There >> is no work around from RS side. You could create another stored procedure >> that calls this one and have it return just one of the resultsets. >> >> RS can deal with multiple datasets quite well, but each stored procedure >> call can only return one. >> >> >> -- >> Bruce Loehle-Conger >> MVP SQL Server Reporting Services >> >> "Lynn" <Lynn@discussions.microsoft.com> wrote in message >> news:E1AFA910-1FF4-4DC8-A3F0-FC1EADE8DFD5@microsoft.com... >> > oh, bruce, that is great. I can't believe it was as simple as that. >> > one >> > remaining problem, though. one of my reports is like this: >> > >> > Date Total Trades Symbols Traded Total $ >> > ---------- ------------------ ------------------ -------------------- >> > 02/07/2006 333,333 333 33,333,333,333.33 >> > >> > #Stocks Volume Total $ >> > ------------- ------------- -------------------- >> > 333,333 33,333,333 3,333,333,333.33 >> > >> > EndPoint Liquidity #Trades Volume Total $ >> > ----------- ------------ -------------- -------------- --------------------- >> > AAAA Add 114,729 21,797,575 >> > 583,102,631.33 >> > AAAA Opening 1 27 >> > 3,412.53 >> > AAAA Remove 30,836 8,330,843 >> > 444,521,928.10 >> > AAAA Route 41 4,543 >> > 103,291.31 >> > BBBB Add 26,997 9,940,201 >> > 178,600,674.78 >> > BBBB Opening 8,089 1,448,143 >> > 44,276,419.41 >> > BBBB Remove 3,406 1,719,403 >> > 40,267,318.25 >> > CCCC Add 83,781 22,443,732 >> > 746,570,071.43 >> > CCCC Remove 35,756 12,250,359 >> > 483,329,796.97 >> > CCCC Route 1,088 244,697 >> > 6,655,913.26 >> > DDDD Route 2 62,500 >> > 2,539,375.00 >> > DDDD Add 2,354 1,262,589 >> > 19,136,233.44 >> > DDDD Remove 258 195,019 >> > 6,227,451.69 >> > >> > I only receive the top line in the resultset: >> > >> > Date Total Trades Symbols Traded Total $ >> > ---------- ------------------ ------------------ -------------------- >> > 02/07/2006 333,333 333 33,333,333,333.33 >> > >> > I assume this is what you mean when you say it will only handle the 1st >> > resultset. All of my reports have multiple 'chunks' in the body. Do >> > you >> > have any idea how I can achieve this report via reporting svcs? >> > Unfortunately, this is very important. Please do advise. >> > >> > --Lynn >> > >> > >> > "Bruce L-C [MVP]" wrote: >> > >> >> Pick the command type as stored procedure and then just enter the name >> >> of >> >> the stored procedure. When you click on the ! you should get back the >> >> data >> >> and the fields. If you do not get the field list populated then click >> >> on >> >> the >> >> refresh fields button (to the right of the ..., it looks like the >> >> refresh >> >> button for IE). >> >> >> >> Note that RS only works with the first resultset returned. It cannot >> >> handle >> >> mutliple resultsets. >> >> >> >> >> >> -- >> >> Bruce Loehle-Conger >> >> MVP SQL Server Reporting Services >> >> >> >> "Lynn" <Lynn@discussions.microsoft.com> wrote in message >> >> news:EF6A6CBC-083B-4537-83D9-D763A8ABA056@microsoft.com... >> >> > Hi. I have a need to create a number of different reports via >> >> > Reporting >> >> > Services. All of which are based on stored procedures, none of >> >> > which >> >> > require >> >> > any parameters. Most if not all of the report is calculated. I am >> >> > struggling badly to get even the most basic of the fields in place >> >> > w/the >> >> > correct expressions. The only one I actually got to accept the >> >> > expression >> >> > was a Date field, this is the calculation: >> >> > >> >> > =SELECT CONVERT(char(10),dateadd(dd,-0,GetDate()),1) >> >> > >> >> > It, too, fails to preview, indicating the value expression for the >> >> > field >> >> > contains an error. Again, all of the other report items are >> >> > calculated >> >> > fields. SUMs, totals, money formats, CASE statements, CASTs, etc. >> >> > Using >> >> > the >> >> > report designer/report wizard to 'do it for me' only returns Date >> >> > and a >> >> > couple fields that are not even in my resultset. like one of my >> >> > values >> >> > in >> >> > the report is 'Total Trades'....one of the fields listed as >> >> > available >> >> > is >> >> > 'ID__Total__' >> >> > >> >> > Further, in VS I've got the report open and am on the data tab, i >> >> > hit >> >> > the >> >> > Run option, I receive this error: An error occurred while >> >> > retrieving >> >> > the >> >> > parameters in the query. Could not locate entry in sysdatabases for >> >> > database >> >> > 'EXEC databasename'. No entry found with that name. Make sure that >> >> > the >> >> > name >> >> > is entered correctly.' >> >> > >> >> > Clearly I am doing something very wrong with the stored procedure >> >> > definition. Like I said, all of these reports are based on >> >> > procedures, >> >> > can >> >> > anybody give me some suggestion/direction on the right way to call >> >> > the >> >> > procs? >> >> > They run w/out parameters...EXEC databasename.dbo.proc that's >> >> > it. >> >> > Please advise. >> >> > >> >> > >> >> > -- Lynn >> >> >> >> >> >> >> >> >>
Hi again, Bruce. Is there any way you can give me an example of how to
change my proc to accept the 3 parameters? I know how to pass parameters to a procedure, I just don't understand how I can do it to make my procedure gen three different resultsets. -- Lynn [quoted text, click to view] "Bruce L-C [MVP]" wrote:
> Configure your existing stored procedure to accept a parameter (you can make > it optional so that it doesn't break existing code). > > Then call it three times in RS with a static value and create three > datasets. > For instance dataset1 > yourproc 1 > > Dataset2 > yourproc 2 > > etc. > > Then in your stored procedure return the appropriate resultset based on the > parameter. > > Downside is that your sp is being run three times. > > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > "Lynn" <Lynn@discussions.microsoft.com> wrote in message > news:E8E34B6C-A36A-4576-BE8C-AB61866EEC80@microsoft.com... > > Bruce, I'm not sure I follow. Can you elaborate a little more, please, on > > the second, possibly third procedure? > > -- Lynn > > > > > > "Bruce L-C [MVP]" wrote: > > > >> This is three resultsets. RS does not have the ability to handle that. > >> There > >> is no work around from RS side. You could create another stored procedure > >> that calls this one and have it return just one of the resultsets. > >> > >> RS can deal with multiple datasets quite well, but each stored procedure > >> call can only return one. > >> > >> > >> -- > >> Bruce Loehle-Conger > >> MVP SQL Server Reporting Services > >> > >> "Lynn" <Lynn@discussions.microsoft.com> wrote in message > >> news:E1AFA910-1FF4-4DC8-A3F0-FC1EADE8DFD5@microsoft.com... > >> > oh, bruce, that is great. I can't believe it was as simple as that. > >> > one > >> > remaining problem, though. one of my reports is like this: > >> > > >> > Date Total Trades Symbols Traded Total $ > >> > ---------- ------------------ ------------------ -------------------- > >> > 02/07/2006 333,333 333 33,333,333,333.33 > >> > > >> > #Stocks Volume Total $ > >> > ------------- ------------- -------------------- > >> > 333,333 33,333,333 3,333,333,333.33 > >> > > >> > EndPoint Liquidity #Trades Volume Total $ > >> > ----------- ------------ -------------- -------------- --------------------- > >> > AAAA Add 114,729 21,797,575 > >> > 583,102,631.33 > >> > AAAA Opening 1 27 > >> > 3,412.53 > >> > AAAA Remove 30,836 8,330,843 > >> > 444,521,928.10 > >> > AAAA Route 41 4,543 > >> > 103,291.31 > >> > BBBB Add 26,997 9,940,201 > >> > 178,600,674.78 > >> > BBBB Opening 8,089 1,448,143 > >> > 44,276,419.41 > >> > BBBB Remove 3,406 1,719,403 > >> > 40,267,318.25 > >> > CCCC Add 83,781 22,443,732 > >> > 746,570,071.43 > >> > CCCC Remove 35,756 12,250,359 > >> > 483,329,796.97 > >> > CCCC Route 1,088 244,697 > >> > 6,655,913.26 > >> > DDDD Route 2 62,500 > >> > 2,539,375.00 > >> > DDDD Add 2,354 1,262,589 > >> > 19,136,233.44 > >> > DDDD Remove 258 195,019 > >> > 6,227,451.69 > >> > > >> > I only receive the top line in the resultset: > >> > > >> > Date Total Trades Symbols Traded Total $ > >> > ---------- ------------------ ------------------ -------------------- > >> > 02/07/2006 333,333 333 33,333,333,333.33 > >> > > >> > I assume this is what you mean when you say it will only handle the 1st > >> > resultset. All of my reports have multiple 'chunks' in the body. Do > >> > you > >> > have any idea how I can achieve this report via reporting svcs? > >> > Unfortunately, this is very important. Please do advise. > >> > > >> > --Lynn > >> > > >> > > >> > "Bruce L-C [MVP]" wrote: > >> > > >> >> Pick the command type as stored procedure and then just enter the name > >> >> of > >> >> the stored procedure. When you click on the ! you should get back the > >> >> data > >> >> and the fields. If you do not get the field list populated then click > >> >> on > >> >> the > >> >> refresh fields button (to the right of the ..., it looks like the > >> >> refresh > >> >> button for IE). > >> >> > >> >> Note that RS only works with the first resultset returned. It cannot > >> >> handle > >> >> mutliple resultsets. > >> >> > >> >> > >> >> -- > >> >> Bruce Loehle-Conger > >> >> MVP SQL Server Reporting Services > >> >> > >> >> "Lynn" <Lynn@discussions.microsoft.com> wrote in message > >> >> news:EF6A6CBC-083B-4537-83D9-D763A8ABA056@microsoft.com... > >> >> > Hi. I have a need to create a number of different reports via > >> >> > Reporting > >> >> > Services. All of which are based on stored procedures, none of > >> >> > which > >> >> > require > >> >> > any parameters. Most if not all of the report is calculated. I am > >> >> > struggling badly to get even the most basic of the fields in place > >> >> > w/the > >> >> > correct expressions. The only one I actually got to accept the > >> >> > expression > >> >> > was a Date field, this is the calculation: > >> >> > > >> >> > =SELECT CONVERT(char(10),dateadd(dd,-0,GetDate()),1) > >> >> > > >> >> > It, too, fails to preview, indicating the value expression for the > >> >> > field > >> >> > contains an error. Again, all of the other report items are > >> >> > calculated > >> >> > fields. SUMs, totals, money formats, CASE statements, CASTs, etc. > >> >> > Using > >> >> > the > >> >> > report designer/report wizard to 'do it for me' only returns Date > >> >> > and a > >> >> > couple fields that are not even in my resultset. like one of my > >> >> > values > >> >> > in > >> >> > the report is 'Total Trades'....one of the fields listed as > >> >> > available > >> >> > is > >> >> > 'ID__Total__' > >> >> > > >> >> > Further, in VS I've got the report open and am on the data tab, i > >> >> > hit > >> >> > the > >> >> > Run option, I receive this error: An error occurred while > >> >> > retrieving > >> >> > the > >> >> > parameters in the query. Could not locate entry in sysdatabases for > >> >> > database > >> >> > 'EXEC databasename'. No entry found with that name. Make sure that > >> >> > the > >> >> > name > >> >> > is entered correctly.' > >> >> > > >> >> > Clearly I am doing something very wrong with the stored procedure > >> >> > definition. Like I said, all of these reports are based on > >> >> > procedures, > >> >> > can
Bruce, or somebody else, might you give me an example of how to give the proc
3 parameters, producing three resultsets in a way that RS will display them? Or, am I giving it three parameters and calling it three times? If so, how would i call the 3 procs in RS? -- Lynn [quoted text, click to view] "Bruce L-C [MVP]" wrote:
> Configure your existing stored procedure to accept a parameter (you can make > it optional so that it doesn't break existing code). > > Then call it three times in RS with a static value and create three > datasets. > For instance dataset1 > yourproc 1 > > Dataset2 > yourproc 2 > > etc. > > Then in your stored procedure return the appropriate resultset based on the > parameter. > > Downside is that your sp is being run three times. > > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > "Lynn" <Lynn@discussions.microsoft.com> wrote in message > news:E8E34B6C-A36A-4576-BE8C-AB61866EEC80@microsoft.com... > > Bruce, I'm not sure I follow. Can you elaborate a little more, please, on > > the second, possibly third procedure? > > -- Lynn > > > > > > "Bruce L-C [MVP]" wrote: > > > >> This is three resultsets. RS does not have the ability to handle that. > >> There > >> is no work around from RS side. You could create another stored procedure > >> that calls this one and have it return just one of the resultsets. > >> > >> RS can deal with multiple datasets quite well, but each stored procedure > >> call can only return one. > >> > >> > >> -- > >> Bruce Loehle-Conger > >> MVP SQL Server Reporting Services > >> > >> "Lynn" <Lynn@discussions.microsoft.com> wrote in message > >> news:E1AFA910-1FF4-4DC8-A3F0-FC1EADE8DFD5@microsoft.com... > >> > oh, bruce, that is great. I can't believe it was as simple as that. > >> > one > >> > remaining problem, though. one of my reports is like this: > >> > > >> > Date Total Trades Symbols Traded Total $ > >> > ---------- ------------------ ------------------ -------------------- > >> > 02/07/2006 333,333 333 33,333,333,333.33 > >> > > >> > #Stocks Volume Total $ > >> > ------------- ------------- -------------------- > >> > 333,333 33,333,333 3,333,333,333.33 > >> > > >> > EndPoint Liquidity #Trades Volume Total $ > >> > ----------- ------------ -------------- -------------- --------------------- > >> > AAAA Add 114,729 21,797,575 > >> > 583,102,631.33 > >> > AAAA Opening 1 27 > >> > 3,412.53 > >> > AAAA Remove 30,836 8,330,843 > >> > 444,521,928.10 > >> > AAAA Route 41 4,543 > >> > 103,291.31 > >> > BBBB Add 26,997 9,940,201 > >> > 178,600,674.78 > >> > BBBB Opening 8,089 1,448,143 > >> > 44,276,419.41 > >> > BBBB Remove 3,406 1,719,403 > >> > 40,267,318.25 > >> > CCCC Add 83,781 22,443,732 > >> > 746,570,071.43 > >> > CCCC Remove 35,756 12,250,359 > >> > 483,329,796.97 > >> > CCCC Route 1,088 244,697 > >> > 6,655,913.26 > >> > DDDD Route 2 62,500 > >> > 2,539,375.00 > >> > DDDD Add 2,354 1,262,589 > >> > 19,136,233.44 > >> > DDDD Remove 258 195,019 > >> > 6,227,451.69 > >> > > >> > I only receive the top line in the resultset: > >> > > >> > Date Total Trades Symbols Traded Total $ > >> > ---------- ------------------ ------------------ -------------------- > >> > 02/07/2006 333,333 333 33,333,333,333.33 > >> > > >> > I assume this is what you mean when you say it will only handle the 1st > >> > resultset. All of my reports have multiple 'chunks' in the body. Do > >> > you > >> > have any idea how I can achieve this report via reporting svcs? > >> > Unfortunately, this is very important. Please do advise. > >> > > >> > --Lynn > >> > > >> > > >> > "Bruce L-C [MVP]" wrote: > >> > > >> >> Pick the command type as stored procedure and then just enter the name > >> >> of > >> >> the stored procedure. When you click on the ! you should get back the > >> >> data > >> >> and the fields. If you do not get the field list populated then click > >> >> on > >> >> the > >> >> refresh fields button (to the right of the ..., it looks like the > >> >> refresh > >> >> button for IE). > >> >> > >> >> Note that RS only works with the first resultset returned. It cannot > >> >> handle > >> >> mutliple resultsets. > >> >> > >> >> > >> >> -- > >> >> Bruce Loehle-Conger > >> >> MVP SQL Server Reporting Services > >> >> > >> >> "Lynn" <Lynn@discussions.microsoft.com> wrote in message > >> >> news:EF6A6CBC-083B-4537-83D9-D763A8ABA056@microsoft.com... > >> >> > Hi. I have a need to create a number of different reports via > >> >> > Reporting > >> >> > Services. All of which are based on stored procedures, none of > >> >> > which > >> >> > require > >> >> > any parameters. Most if not all of the report is calculated. I am > >> >> > struggling badly to get even the most basic of the fields in place > >> >> > w/the > >> >> > correct expressions. The only one I actually got to accept the > >> >> > expression > >> >> > was a Date field, this is the calculation: > >> >> > > >> >> > =SELECT CONVERT(char(10),dateadd(dd,-0,GetDate()),1) > >> >> > > >> >> > It, too, fails to preview, indicating the value expression for the > >> >> > field > >> >> > contains an error. Again, all of the other report items are > >> >> > calculated > >> >> > fields. SUMs, totals, money formats, CASE statements, CASTs, etc. > >> >> > Using > >> >> > the > >> >> > report designer/report wizard to 'do it for me' only returns Date > >> >> > and a > >> >> > couple fields that are not even in my resultset. like one of my > >> >> > values > >> >> > in > >> >> > the report is 'Total Trades'....one of the fields listed as > >> >> > available > >> >> > is > >> >> > 'ID__Total__' > >> >> > > >> >> > Further, in VS I've got the report open and am on the data tab, i > >> >> > hit > >> >> > the > >> >> > Run option, I receive this error: An error occurred while > >> >> > retrieving > >> >> > the > >> >> > parameters in the query. Could not locate entry in sysdatabases for > >> >> > database > >> >> > 'EXEC databasename'. No entry found with that name. Make sure that > >> >> > the > >> >> > name > >> >> > is entered correctly.' > >> >> > > >> >> > Clearly I am doing something very wrong with the stored procedure > >> >> > definition. Like I said, all of these reports are based on > >> >> > procedures, > >> >> > can
bruce, i've added the parameters to the proc, now if you fire procname 1,
procname 2, procname 3 or procname 4, it returns each of the resultsets to me in query analyzer. in Rs i've got one dataset running this successfully: EXEC db.dbo.procname 1 results are given, everthing looks good. but, i go add dataset 2, it seems to do so successfully, but now the data is not shown. not even the data from the first dataset. it just gives my header, no results. i don't know how to fix this. should i be using subreports, should i be using one proce multiple times w/multiple datasets, i'm just not sure. -- Lynn [quoted text, click to view] "Bruce L-C [MVP]" wrote:
> Yes, you are calling it three times. In RS you create three datasets. Go to > the dataset tab and there is a combo box with the name of the dataset, you > can click on that and there is a an option for a new. > > In your stored procedure > > if @WHICHRESULTSET = 1 > begin > --return the first resultset here > end > etc > > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > "Lynn" <Lynn@discussions.microsoft.com> wrote in message > news:81E3A442-6694-442D-B151-F083E4750ADB@microsoft.com... > > Bruce, or somebody else, might you give me an example of how to give the > > proc > > 3 parameters, producing three resultsets in a way that RS will display > > them? > > Or, am I giving it three parameters and calling it three times? If so, > > how > > would i call the 3 procs in RS? > > -- Lynn > > > > > > "Bruce L-C [MVP]" wrote: > > > >> Configure your existing stored procedure to accept a parameter (you can > >> make > >> it optional so that it doesn't break existing code). > >> > >> Then call it three times in RS with a static value and create three > >> datasets. > >> For instance dataset1 > >> yourproc 1 > >> > >> Dataset2 > >> yourproc 2 > >> > >> etc. > >> > >> Then in your stored procedure return the appropriate resultset based on > >> the > >> parameter. > >> > >> Downside is that your sp is being run three times. > >> > >> > >> -- > >> Bruce Loehle-Conger > >> MVP SQL Server Reporting Services > >> > >> "Lynn" <Lynn@discussions.microsoft.com> wrote in message > >> news:E8E34B6C-A36A-4576-BE8C-AB61866EEC80@microsoft.com... > >> > Bruce, I'm not sure I follow. Can you elaborate a little more, please, > >> > on > >> > the second, possibly third procedure? > >> > -- Lynn > >> > > >> > > >> > "Bruce L-C [MVP]" wrote: > >> > > >> >> This is three resultsets. RS does not have the ability to handle that. > >> >> There > >> >> is no work around from RS side. You could create another stored > >> >> procedure > >> >> that calls this one and have it return just one of the resultsets. > >> >> > >> >> RS can deal with multiple datasets quite well, but each stored > >> >> procedure > >> >> call can only return one. > >> >> > >> >> > >> >> -- > >> >> Bruce Loehle-Conger > >> >> MVP SQL Server Reporting Services > >> >> > >> >> "Lynn" <Lynn@discussions.microsoft.com> wrote in message > >> >> news:E1AFA910-1FF4-4DC8-A3F0-FC1EADE8DFD5@microsoft.com... > >> >> > oh, bruce, that is great. I can't believe it was as simple as that. > >> >> > one > >> >> > remaining problem, though. one of my reports is like this: > >> >> > > >> >> > Date Total Trades Symbols Traded Total $ > >> >> > ---------- ------------------ ------------------ -------------------- > >> >> > 02/07/2006 333,333 333 33,333,333,333.33 > >> >> > > >> >> > #Stocks Volume Total $ > >> >> > ------------- ------------- -------------------- > >> >> > 333,333 33,333,333 3,333,333,333.33 > >> >> > > >> >> > EndPoint Liquidity #Trades Volume Total $ > >> >> > ----------- ------------ -------------- -------------- --------------------- > >> >> > AAAA Add 114,729 21,797,575 > >> >> > 583,102,631.33 > >> >> > AAAA Opening 1 27 > >> >> > 3,412.53 > >> >> > AAAA Remove 30,836 8,330,843 > >> >> > 444,521,928.10 > >> >> > AAAA Route 41 4,543 > >> >> > 103,291.31 > >> >> > BBBB Add 26,997 9,940,201 > >> >> > 178,600,674.78 > >> >> > BBBB Opening 8,089 1,448,143 > >> >> > 44,276,419.41 > >> >> > BBBB Remove 3,406 1,719,403 > >> >> > 40,267,318.25 > >> >> > CCCC Add 83,781 22,443,732 > >> >> > 746,570,071.43 > >> >> > CCCC Remove 35,756 12,250,359 > >> >> > 483,329,796.97 > >> >> > CCCC Route 1,088 244,697 > >> >> > 6,655,913.26 > >> >> > DDDD Route 2 62,500 > >> >> > 2,539,375.00 > >> >> > DDDD Add 2,354 1,262,589 > >> >> > 19,136,233.44 > >> >> > DDDD Remove 258 195,019 > >> >> > 6,227,451.69 > >> >> > > >> >> > I only receive the top line in the resultset: > >> >> > > >> >> > Date Total Trades Symbols Traded Total $ > >> >> > ---------- ------------------ ------------------ -------------------- > >> >> > 02/07/2006 333,333 333 33,333,333,333.33 > >> >> > > >> >> > I assume this is what you mean when you say it will only handle the > >> >> > 1st > >> >> > resultset. All of my reports have multiple 'chunks' in the body. > >> >> > Do > >> >> > you > >> >> > have any idea how I can achieve this report via reporting svcs? > >> >> > Unfortunately, this is very important. Please do advise. > >> >> > > >> >> > --Lynn > >> >> > > >> >> > > >> >> > "Bruce L-C [MVP]" wrote: > >> >> > > >> >> >> Pick the command type as stored procedure and then just enter the > >> >> >> name > >> >> >> of > >> >> >> the stored procedure. When you click on the ! you should get back > >> >> >> the > >> >> >> data > >> >> >> and the fields. If you do not get the field list populated then > >> >> >> click > >> >> >> on > >> >> >> the > >> >> >> refresh fields button (to the right of the ..., it looks like the > >> >> >> refresh > >> >> >> button for IE). > >> >> >> > >> >> >> Note that RS only works with the first resultset returned. It > >> >> >> cannot > >> >> >> handle > >> >> >> mutliple resultsets. > >> >> >> > >> >> >> > >> >> >> -- > >> >> >> Bruce Loehle-Conger > >> >> >> MVP SQL Server Reporting Services > >> >> >> > >> >> >> "Lynn" <Lynn@discussions.microsoft.com> wrote in message > >> >> >> news:EF6A6CBC-083B-4537-83D9-D763A8ABA056@microsoft.com... > >> >> >> > Hi. I have a need to create a number of different reports via > >> >> >> > Reporting > >> >> >> > Services. All of which are based on stored procedures, none of > >> >> >> > which > >> >> >> > require
And, if I blow away the 2nd dataset and just give my 1st one parameters, it
almost works. EXEC db.dbo.procname 1 for chunk one of the results, works just fine. if i change the parameter to 2, 3 or 4, when I hit RUN in Data tab it returns an error: SQL Syntax Errors Encountered....The designer does not graphically support the EXEC SQL Construct' but, i hit 'OK' and my results are still returned to me. On 'Preview' tab, however, I get no data/results at all. -- Lynn [quoted text, click to view] "Bruce L-C [MVP]" wrote:
> Yes, you are calling it three times. In RS you create three datasets. Go to > the dataset tab and there is a combo box with the name of the dataset, you > can click on that and there is a an option for a new. > > In your stored procedure > > if @WHICHRESULTSET = 1 > begin > --return the first resultset here > end > etc > > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > "Lynn" <Lynn@discussions.microsoft.com> wrote in message > news:81E3A442-6694-442D-B151-F083E4750ADB@microsoft.com... > > Bruce, or somebody else, might you give me an example of how to give the > > proc > > 3 parameters, producing three resultsets in a way that RS will display > > them? > > Or, am I giving it three parameters and calling it three times? If so, > > how > > would i call the 3 procs in RS? > > -- Lynn > > > > > > "Bruce L-C [MVP]" wrote: > > > >> Configure your existing stored procedure to accept a parameter (you can > >> make > >> it optional so that it doesn't break existing code). > >> > >> Then call it three times in RS with a static value and create three > >> datasets. > >> For instance dataset1 > >> yourproc 1 > >> > >> Dataset2 > >> yourproc 2 > >> > >> etc. > >> > >> Then in your stored procedure return the appropriate resultset based on > >> the > >> parameter. > >> > >> Downside is that your sp is being run three times. > >> > >> > >> -- > >> Bruce Loehle-Conger > >> MVP SQL Server Reporting Services > >> > >> "Lynn" <Lynn@discussions.microsoft.com> wrote in message > >> news:E8E34B6C-A36A-4576-BE8C-AB61866EEC80@microsoft.com... > >> > Bruce, I'm not sure I follow. Can you elaborate a little more, please, > >> > on > >> > the second, possibly third procedure? > >> > -- Lynn > >> > > >> > > >> > "Bruce L-C [MVP]" wrote: > >> > > >> >> This is three resultsets. RS does not have the ability to handle that. > >> >> There > >> >> is no work around from RS side. You could create another stored > >> >> procedure > >> >> that calls this one and have it return just one of the resultsets. > >> >> > >> >> RS can deal with multiple datasets quite well, but each stored > >> >> procedure > >> >> call can only return one. > >> >> > >> >> > >> >> -- > >> >> Bruce Loehle-Conger > >> >> MVP SQL Server Reporting Services > >> >> > >> >> "Lynn" <Lynn@discussions.microsoft.com> wrote in message > >> >> news:E1AFA910-1FF4-4DC8-A3F0-FC1EADE8DFD5@microsoft.com... > >> >> > oh, bruce, that is great. I can't believe it was as simple as that. > >> >> > one > >> >> > remaining problem, though. one of my reports is like this: > >> >> > > >> >> > Date Total Trades Symbols Traded Total $ > >> >> > ---------- ------------------ ------------------ -------------------- > >> >> > 02/07/2006 333,333 333 33,333,333,333.33 > >> >> > > >> >> > #Stocks Volume Total $ > >> >> > ------------- ------------- -------------------- > >> >> > 333,333 33,333,333 3,333,333,333.33 > >> >> > > >> >> > EndPoint Liquidity #Trades Volume Total $ > >> >> > ----------- ------------ -------------- -------------- --------------------- > >> >> > AAAA Add 114,729 21,797,575 > >> >> > 583,102,631.33 > >> >> > AAAA Opening 1 27 > >> >> > 3,412.53 > >> >> > AAAA Remove 30,836 8,330,843 > >> >> > 444,521,928.10 > >> >> > AAAA Route 41 4,543 > >> >> > 103,291.31 > >> >> > BBBB Add 26,997 9,940,201 > >> >> > 178,600,674.78 > >> >> > BBBB Opening 8,089 1,448,143 > >> >> > 44,276,419.41 > >> >> > BBBB Remove 3,406 1,719,403 > >> >> > 40,267,318.25 > >> >> > CCCC Add 83,781 22,443,732 > >> >> > 746,570,071.43 > >> >> > CCCC Remove 35,756 12,250,359 > >> >> > 483,329,796.97 > >> >> > CCCC Route 1,088 244,697 > >> >> > 6,655,913.26 > >> >> > DDDD Route 2 62,500 > >> >> > 2,539,375.00 > >> >> > DDDD Add 2,354 1,262,589 > >> >> > 19,136,233.44 > >> >> > DDDD Remove 258 195,019 > >> >> > 6,227,451.69 > >> >> > > >> >> > I only receive the top line in the resultset: > >> >> > > >> >> > Date Total Trades Symbols Traded Total $ > >> >> > ---------- ------------------ ------------------ -------------------- > >> >> > 02/07/2006 333,333 333 33,333,333,333.33 > >> >> > > >> >> > I assume this is what you mean when you say it will only handle the > >> >> > 1st > >> >> > resultset. All of my reports have multiple 'chunks' in the body. > >> >> > Do > >> >> > you > >> >> > have any idea how I can achieve this report via reporting svcs? > >> >> > Unfortunately, this is very important. Please do advise. > >> >> > > >> >> > --Lynn > >> >> > > >> >> > > >> >> > "Bruce L-C [MVP]" wrote: > >> >> > > >> >> >> Pick the command type as stored procedure and then just enter the > >> >> >> name > >> >> >> of > >> >> >> the stored procedure. When you click on the ! you should get back > >> >> >> the > >> >> >> data > >> >> >> and the fields. If you do not get the field list populated then > >> >> >> click > >> >> >> on > >> >> >> the > >> >> >> refresh fields button (to the right of the ..., it looks like the > >> >> >> refresh > >> >> >> button for IE). > >> >> >> > >> >> >> Note that RS only works with the first resultset returned. It > >> >> >> cannot > >> >> >> handle > >> >> >> mutliple resultsets. > >> >> >> > >> >> >> > >> >> >> -- > >> >> >> Bruce Loehle-Conger > >> >> >> MVP SQL Server Reporting Services > >> >> >> > >> >> >> "Lynn" <Lynn@discussions.microsoft.com> wrote in message > >> >> >> news:EF6A6CBC-083B-4537-83D9-D763A8ABA056@microsoft.com... > >> >> >> > Hi. I have a need to create a number of different reports via > >> >> >> > Reporting > >> >> >> > Services. All of which are based on stored procedures, none of > >> >> >> > which > >> >> >> > require > >> >> >> > any parameters. Most if not all of the report is calculated. I > >> >> >> > am
Yes, you are calling it three times. In RS you create three datasets. Go to
the dataset tab and there is a combo box with the name of the dataset, you can click on that and there is a an option for a new. In your stored procedure if @WHICHRESULTSET = 1 begin --return the first resultset here end etc -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "Lynn" <Lynn@discussions.microsoft.com> wrote in message
news:81E3A442-6694-442D-B151-F083E4750ADB@microsoft.com... > Bruce, or somebody else, might you give me an example of how to give the > proc > 3 parameters, producing three resultsets in a way that RS will display > them? > Or, am I giving it three parameters and calling it three times? If so, > how > would i call the 3 procs in RS? > -- Lynn > > > "Bruce L-C [MVP]" wrote: > >> Configure your existing stored procedure to accept a parameter (you can >> make >> it optional so that it doesn't break existing code). >> >> Then call it three times in RS with a static value and create three >> datasets. >> For instance dataset1 >> yourproc 1 >> >> Dataset2 >> yourproc 2 >> >> etc. >> >> Then in your stored procedure return the appropriate resultset based on >> the >> parameter. >> >> Downside is that your sp is being run three times. >> >> >> -- >> Bruce Loehle-Conger >> MVP SQL Server Reporting Services >> >> "Lynn" <Lynn@discussions.microsoft.com> wrote in message >> news:E8E34B6C-A36A-4576-BE8C-AB61866EEC80@microsoft.com... >> > Bruce, I'm not sure I follow. Can you elaborate a little more, please, >> > on >> > the second, possibly third procedure? >> > -- Lynn >> > >> > >> > "Bruce L-C [MVP]" wrote: >> > >> >> This is three resultsets. RS does not have the ability to handle that. >> >> There >> >> is no work around from RS side. You could create another stored >> >> procedure >> >> that calls this one and have it return just one of the resultsets. >> >> >> >> RS can deal with multiple datasets quite well, but each stored >> >> procedure >> >> call can only return one. >> >> >> >> >> >> -- >> >> Bruce Loehle-Conger >> >> MVP SQL Server Reporting Services >> >> >> >> "Lynn" <Lynn@discussions.microsoft.com> wrote in message >> >> news:E1AFA910-1FF4-4DC8-A3F0-FC1EADE8DFD5@microsoft.com... >> >> > oh, bruce, that is great. I can't believe it was as simple as that. >> >> > one >> >> > remaining problem, though. one of my reports is like this: >> >> > >> >> > Date Total Trades Symbols Traded Total $ >> >> > ---------- ------------------ ------------------ -------------------- >> >> > 02/07/2006 333,333 333 33,333,333,333.33 >> >> > >> >> > #Stocks Volume Total $ >> >> > ------------- ------------- -------------------- >> >> > 333,333 33,333,333 3,333,333,333.33 >> >> > >> >> > EndPoint Liquidity #Trades Volume Total $ >> >> > ----------- ------------ -------------- -------------- --------------------- >> >> > AAAA Add 114,729 21,797,575 >> >> > 583,102,631.33 >> >> > AAAA Opening 1 27 >> >> > 3,412.53 >> >> > AAAA Remove 30,836 8,330,843 >> >> > 444,521,928.10 >> >> > AAAA Route 41 4,543 >> >> > 103,291.31 >> >> > BBBB Add 26,997 9,940,201 >> >> > 178,600,674.78 >> >> > BBBB Opening 8,089 1,448,143 >> >> > 44,276,419.41 >> >> > BBBB Remove 3,406 1,719,403 >> >> > 40,267,318.25 >> >> > CCCC Add 83,781 22,443,732 >> >> > 746,570,071.43 >> >> > CCCC Remove 35,756 12,250,359 >> >> > 483,329,796.97 >> >> > CCCC Route 1,088 244,697 >> >> > 6,655,913.26 >> >> > DDDD Route 2 62,500 >> >> > 2,539,375.00 >> >> > DDDD Add 2,354 1,262,589 >> >> > 19,136,233.44 >> >> > DDDD Remove 258 195,019 >> >> > 6,227,451.69 >> >> > >> >> > I only receive the top line in the resultset: >> >> > >> >> > Date Total Trades Symbols Traded Total $ >> >> > ---------- ------------------ ------------------ -------------------- >> >> > 02/07/2006 333,333 333 33,333,333,333.33 >> >> > >> >> > I assume this is what you mean when you say it will only handle the >> >> > 1st >> >> > resultset. All of my reports have multiple 'chunks' in the body. >> >> > Do >> >> > you >> >> > have any idea how I can achieve this report via reporting svcs? >> >> > Unfortunately, this is very important. Please do advise. >> >> > >> >> > --Lynn >> >> > >> >> > >> >> > "Bruce L-C [MVP]" wrote: >> >> > >> >> >> Pick the command type as stored procedure and then just enter the >> >> >> name >> >> >> of >> >> >> the stored procedure. When you click on the ! you should get back >> >> >> the >> >> >> data >> >> >> and the fields. If you do not get the field list populated then >> >> >> click >> >> >> on >> >> >> the >> >> >> refresh fields button (to the right of the ..., it looks like the >> >> >> refresh >> >> >> button for IE). >> >> >> >> >> >> Note that RS only works with the first resultset returned. It >> >> >> cannot >> >> >> handle >> >> >> mutliple resultsets. >> >> >> >> >> >> >> >> >> -- >> >> >> Bruce Loehle-Conger >> >> >> MVP SQL Server Reporting Services >> >> >> >> >> >> "Lynn" <Lynn@discussions.microsoft.com> wrote in message >> >> >> news:EF6A6CBC-083B-4537-83D9-D763A8ABA056@microsoft.com... >> >> >> > Hi. I have a need to create a number of different reports via >> >> >> > Reporting >> >> >> > Services. All of which are based on stored procedures, none of >> >> >> > which >> >> >> > require >> >> >> > any parameters. Most if not all of the report is calculated. I >> >> >> > am >> >> >> > struggling badly to get even the most basic of the fields in >> >> >> > place >> >> >> > w/the >> >> >> > correct expressions. The only one I actually got to accept the >> >> >> > expression >> >> >> > was a Date field, this is the calculation: >> >> >> > >> >> >> > =SELECT CONVERT(char(10),dateadd(dd,-0,GetDate()),1) >> >> >> > >> >> >> > It, too, fails to preview, indicating the value expression for >> >> >> > the >> >> >> > field >> >> >> > contains an error. Again, all of the other report items are >> >> >> > calculated >> >> >> > fields. SUMs, totals, money formats, CASE statements, CASTs, >> >> >> > etc. >> >> >> > Using >> >> >> > the >> >> >> > report designer/report wizard to 'do it for me' only returns Date >> >> >> > and a >> >> >> > couple fields that are not even in my resultset. like one of my >> >> >> > values >> >> >> > in
i'm sorry, bruce, but where do i define the parameters? rs is not detecting
them. in my 1st dataset i've got cmd type stored procedure and the proc name is in the query string. i assumed i would put the 1st parameter in on the parameters tab and then do the same for 2, 3 and 4 on 3 other datasets, so i gave it @ch and the value of 1, but when i attempted to run it, it threw me a 'define query parameters box asking me to put the '1' in it. -- Lynn [quoted text, click to view] "Bruce L-C [MVP]" wrote:
> If you are going against SQL Server do not run your stored procedure this > way. Pick command type of stored procedure. RS will detect the parameters. > If going against ODBC or OLEDB driver (which uses ? format for parameters) > then pick text as the command type and do this: > myproc ? > > Create multiple datasets and for each one you should call the stored > procedure for each one. > > Bruce Loehle-Conger > > "Lynn" <Lynn@discussions.microsoft.com> wrote in message > news:79CA374D-729B-4181-8F45-5D61FCF30F28@microsoft.com... > > And, if I blow away the 2nd dataset and just give my 1st one parameters, > > it > > almost works. EXEC db.dbo.procname 1 for chunk one of the results, > > works > > just fine. if i change the parameter to 2, 3 or 4, when I hit RUN in Data > > tab it returns an error: SQL Syntax Errors Encountered....The designer > > does > > not graphically support the EXEC SQL Construct' but, i hit 'OK' and my > > results are still returned to me. On 'Preview' tab, however, I get no > > data/results at all. > > -- Lynn > > > > > > "Bruce L-C [MVP]" wrote: > > > >> Yes, you are calling it three times. In RS you create three datasets. Go > >> to > >> the dataset tab and there is a combo box with the name of the dataset, > >> you > >> can click on that and there is a an option for a new. > >> > >> In your stored procedure > >> > >> if @WHICHRESULTSET = 1 > >> begin > >> --return the first resultset here > >> end > >> etc > >> > >> > >> -- > >> Bruce Loehle-Conger > >> MVP SQL Server Reporting Services > >> > >> "Lynn" <Lynn@discussions.microsoft.com> wrote in message > >> news:81E3A442-6694-442D-B151-F083E4750ADB@microsoft.com... > >> > Bruce, or somebody else, might you give me an example of how to give > >> > the > >> > proc > >> > 3 parameters, producing three resultsets in a way that RS will display > >> > them? > >> > Or, am I giving it three parameters and calling it three times? If so, > >> > how > >> > would i call the 3 procs in RS? > >> > -- Lynn > >> > > >> > > >> > "Bruce L-C [MVP]" wrote: > >> > > >> >> Configure your existing stored procedure to accept a parameter (you > >> >> can > >> >> make > >> >> it optional so that it doesn't break existing code). > >> >> > >> >> Then call it three times in RS with a static value and create three > >> >> datasets. > >> >> For instance dataset1 > >> >> yourproc 1 > >> >> > >> >> Dataset2 > >> >> yourproc 2 > >> >> > >> >> etc. > >> >> > >> >> Then in your stored procedure return the appropriate resultset based > >> >> on > >> >> the > >> >> parameter. > >> >> > >> >> Downside is that your sp is being run three times. > >> >> > >> >> > >> >> -- > >> >> Bruce Loehle-Conger > >> >> MVP SQL Server Reporting Services > >> >> > >> >> "Lynn" <Lynn@discussions.microsoft.com> wrote in message > >> >> news:E8E34B6C-A36A-4576-BE8C-AB61866EEC80@microsoft.com... > >> >> > Bruce, I'm not sure I follow. Can you elaborate a little more, > >> >> > please, > >> >> > on > >> >> > the second, possibly third procedure? > >> >> > -- Lynn > >> >> > > >> >> > > >> >> > "Bruce L-C [MVP]" wrote: > >> >> > > >> >> >> This is three resultsets. RS does not have the ability to handle > >> >> >> that. > >> >> >> There > >> >> >> is no work around from RS side. You could create another stored > >> >> >> procedure > >> >> >> that calls this one and have it return just one of the resultsets. > >> >> >> > >> >> >> RS can deal with multiple datasets quite well, but each stored > >> >> >> procedure > >> >> >> call can only return one. > >> >> >> > >> >> >> > >> >> >> -- > >> >> >> Bruce Loehle-Conger > >> >> >> MVP SQL Server Reporting Services > >> >> >> > >> >> >> "Lynn" <Lynn@discussions.microsoft.com> wrote in message > >> >> >> news:E1AFA910-1FF4-4DC8-A3F0-FC1EADE8DFD5@microsoft.com... > >> >> >> > oh, bruce, that is great. I can't believe it was as simple as > >> >> >> > that. > >> >> >> > one > >> >> >> > remaining problem, though. one of my reports is like this: > >> >> >> > > >> >> >> > Date Total Trades Symbols Traded Total $ > >> >> >> > ---------- ------------------ ------------------ -------------------- > >> >> >> > 02/07/2006 333,333 333 > >> >> >> > 33,333,333,333.33 > >> >> >> > > >> >> >> > #Stocks Volume Total $ > >> >> >> > ------------- ------------- -------------------- > >> >> >> > 333,333 33,333,333 3,333,333,333.33 > >> >> >> > > >> >> >> > EndPoint Liquidity #Trades Volume Total $ > >> >> >> > ----------- ------------ -------------- -------------- --------------------- > >> >> >> > AAAA Add 114,729 21,797,575 > >> >> >> > 583,102,631.33 > >> >> >> > AAAA Opening 1 27 > >> >> >> > 3,412.53 > >> >> >> > AAAA Remove 30,836 8,330,843 > >> >> >> > 444,521,928.10 > >> >> >> > AAAA Route 41 4,543 > >> >> >> > 103,291.31 > >> >> >> > BBBB Add 26,997 9,940,201 > >> >> >> > 178,600,674.78 > >> >> >> > BBBB Opening 8,089 1,448,143 > >> >> >> > 44,276,419.41 > >> >> >> > BBBB Remove 3,406 1,719,403 > >> >> >> > 40,267,318.25 > >> >> >> > CCCC Add 83,781 22,443,732 > >> >> >> > 746,570,071.43 > >> >> >> > CCCC Remove 35,756 12,250,359 > >> >> >> > 483,329,796.97 > >> >> >> > CCCC Route 1,088 244,697 > >> >> >> > 6,655,913.26 > >> >> >> > DDDD Route 2 62,500 > >> >> >> > 2,539,375.00 > >> >> >> > DDDD Add 2,354 1,262,589 > >> >> >> > 19,136,233.44 > >> >> >> > DDDD Remove 258 195,019 > >> >> >> > 6,227,451.69 > >> >> >> > > >> >> >> > I only receive the top line in the resultset: > >> >> >> > > >> >> >> > Date Total Trades Symbols Traded Total $ > >> >> >> > ---------- ------------------ ------------------ -------------------- > >> >> >> > 02/07/2006 333,333 333 > >> >> >> > 33,333,333,333.33 > >> >> >> > > >> >> >> > I assume this is what you mean when you say it will only handle > >> >> >> > the > >> >> >> > 1st
yes, it's sql2k, i am somewhat of a RS newbie, but I spent last week setting
reports up for all my sql jobs, just to kind of practice in the RS arena. everything went flawlessly. i even set up one of the company reports just fine, but it was a proc with only a single resultset. everything else is multiple resultsets, i'm just having a very difficult time. but, i will give this a shot, bruce. thank you, bruce -- Lynn [quoted text, click to view] "Bruce L-C [MVP]" wrote:
> Are you going against SQL Server? > > If so, then just select tored procedure for the command type and just put in > the name of the stored procedure. Do not put exec in from of it or anything > else. It should detect the parameter and prompt you for it. It should also > create a report parameter as well for you. > > Try creating a new dataset and do this from the start. If you continue to > have problems create a new stored procedure just for testing . > > create procedure dbo.testproc > @PARAM1 int > as > select 'Parameter passed in = ' + @PARAM1 as Message > return > > Get the above procedure to work for you so you know how to call a stored > procedure they way I am telling you (do not use exec). > > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > "Lynn" <Lynn@discussions.microsoft.com> wrote in message > news:9B089D7B-1F23-465B-B443-08BD09469A3E@microsoft.com... > > i'm sorry, bruce, but where do i define the parameters? rs is not > > detecting > > them. in my 1st dataset i've got cmd type stored procedure and the proc > > name > > is in the query string. i assumed i would put the 1st parameter in on the > > parameters tab and then do the same for 2, 3 and 4 on 3 other datasets, so > > i > > gave it @ch and the value of 1, but when i attempted to run it, it threw > > me a > > 'define query parameters box asking me to put the '1' in it. > > -- Lynn > > > > > > "Bruce L-C [MVP]" wrote: > > > >> If you are going against SQL Server do not run your stored procedure this > >> way. Pick command type of stored procedure. RS will detect the > >> parameters. > >> If going against ODBC or OLEDB driver (which uses ? format for > >> parameters) > >> then pick text as the command type and do this: > >> myproc ? > >> > >> Create multiple datasets and for each one you should call the stored > >> procedure for each one. > >> > >> Bruce Loehle-Conger > >> > >> "Lynn" <Lynn@discussions.microsoft.com> wrote in message > >> news:79CA374D-729B-4181-8F45-5D61FCF30F28@microsoft.com... > >> > And, if I blow away the 2nd dataset and just give my 1st one > >> > parameters, > >> > it > >> > almost works. EXEC db.dbo.procname 1 for chunk one of the results, > >> > works > >> > just fine. if i change the parameter to 2, 3 or 4, when I hit RUN in > >> > Data > >> > tab it returns an error: SQL Syntax Errors Encountered....The designer > >> > does > >> > not graphically support the EXEC SQL Construct' but, i hit 'OK' and > >> > my > >> > results are still returned to me. On 'Preview' tab, however, I get no > >> > data/results at all. > >> > -- Lynn > >> > > >> > > >> > "Bruce L-C [MVP]" wrote: > >> > > >> >> Yes, you are calling it three times. In RS you create three datasets. > >> >> Go > >> >> to > >> >> the dataset tab and there is a combo box with the name of the dataset, > >> >> you > >> >> can click on that and there is a an option for a new. > >> >> > >> >> In your stored procedure > >> >> > >> >> if @WHICHRESULTSET = 1 > >> >> begin > >> >> --return the first resultset here > >> >> end > >> >> etc > >> >> > >> >> > >> >> -- > >> >> Bruce Loehle-Conger > >> >> MVP SQL Server Reporting Services > >> >> > >> >> "Lynn" <Lynn@discussions.microsoft.com> wrote in message > >> >> news:81E3A442-6694-442D-B151-F083E4750ADB@microsoft.com... > >> >> > Bruce, or somebody else, might you give me an example of how to give > >> >> > the > >> >> > proc > >> >> > 3 parameters, producing three resultsets in a way that RS will > >> >> > display > >> >> > them? > >> >> > Or, am I giving it three parameters and calling it three times? If > >> >> > so, > >> >> > how > >> >> > would i call the 3 procs in RS? > >> >> > -- Lynn > >> >> > > >> >> > > >> >> > "Bruce L-C [MVP]" wrote: > >> >> > > >> >> >> Configure your existing stored procedure to accept a parameter (you > >> >> >> can > >> >> >> make > >> >> >> it optional so that it doesn't break existing code). > >> >> >> > >> >> >> Then call it three times in RS with a static value and create three > >> >> >> datasets. > >> >> >> For instance dataset1 > >> >> >> yourproc 1 > >> >> >> > >> >> >> Dataset2 > >> >> >> yourproc 2 > >> >> >> > >> >> >> etc. > >> >> >> > >> >> >> Then in your stored procedure return the appropriate resultset > >> >> >> based > >> >> >> on > >> >> >> the > >> >> >> parameter. > >> >> >> > >> >> >> Downside is that your sp is being run three times. > >> >> >> > >> >> >> > >> >> >> -- > >> >> >> Bruce Loehle-Conger > >> >> >> MVP SQL Server Reporting Services > >> >> >> > >> >> >> "Lynn" <Lynn@discussions.microsoft.com> wrote in message > >> >> >> news:E8E34B6C-A36A-4576-BE8C-AB61866EEC80@microsoft.com... > >> >> >> > Bruce, I'm not sure I follow. Can you elaborate a little more, > >> >> >> > please, > >> >> >> > on > >> >> >> > the second, possibly third procedure? > >> >> >> > -- Lynn > >> >> >> > > >> >> >> > > >> >> >> > "Bruce L-C [MVP]" wrote: > >> >> >> > > >> >> >> >> This is three resultsets. RS does not have the ability to handle > >> >> >> >> that. > >> >> >> >> There > >> >> >> >> is no work around from RS side. You could create another stored > >> >> >> >> procedure > >> >> >> >> that calls this one and have it return just one of the > >> >> >> >> resultsets. > >> >> >> >> > >> >> >> >> RS can deal with multiple datasets quite well, but each stored > >> >> >> >> procedure > >> >> >> >> call can only return one. > >> >> >> >> > >> >> >> >> > >> >> >> >> -- > >> >> >> >> Bruce Loehle-Conger > >> >> >> >> MVP SQL Server Reporting Services > >> >> >> >> > >> >> >> >> "Lynn" <Lynn@discussions.microsoft.com> wrote in message > >> >> >> >> news:E1AFA910-1FF4-4DC8-A3F0-FC1EADE8DFD5@microsoft.com... > >> >> >> >> > oh, bruce, that is great. I can't believe it was as simple as > >> >> >> >> > that. > >> >> >> >> > one > >> >> >> >> > remaining problem, though. one of my reports is like this: > >> >> >> >> > > >> >> >> >> > Date Total Trades Symbols Traded Total $ > >> >> >> >> > ---------- ------------------ ------------------ -------------------- > >> >> >> >> > 02/07/2006 333,333 333 > >> >> >> >> > 33,333,333,333.33 > >> >> >> >> > > >> >> >> >> > #Stocks Volume Total $
i am not sure how to display each resultset/dataset in the same report -- Lynn [quoted text, click to view] "Bruce L-C [MVP]" wrote: > Are you going against SQL Server? > > If so, then just select tored procedure for the command type and just put in > the name of the stored procedure. Do not put exec in from of it or anything > else. It should detect the parameter and prompt you for it. It should also > create a report parameter as well for you. > > Try creating a new dataset and do this from the start. If you continue to > have problems create a new stored procedure just for testing . > > create procedure dbo.testproc > @PARAM1 int > as > select 'Parameter passed in = ' + @PARAM1 as Message > return > > Get the above procedure to work for you so you know how to call a stored > procedure they way I am telling you (do not use exec). > > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > "Lynn" <Lynn@discussions.microsoft.com> wrote in message > news:9B089D7B-1F23-465B-B443-08BD09469A3E@microsoft.com... > > i'm sorry, bruce, but where do i define the parameters? rs is not > > detecting > > them. in my 1st dataset i've got cmd type stored procedure and the proc > > name > > is in the query string. i assumed i would put the 1st parameter in on the > > parameters tab and then do the same for 2, 3 and 4 on 3 other datasets, so > > i > > gave it @ch and the value of 1, but when i attempted to run it, it threw > > me a > > 'define query parameters box asking me to put the '1' in it. > > -- Lynn > > > > > > "Bruce L-C [MVP]" wrote: > > > >> If you are going against SQL Server do not run your stored procedure this > >> way. Pick command type of stored procedure. RS will detect the > >> parameters. > >> If going against ODBC or OLEDB driver (which uses ? format for > >> parameters) > >> then pick text as the command type and do this: > >> myproc ? > >> > >> Create multiple datasets and for each one you should call the stored > >> procedure for each one. > >> > >> Bruce Loehle-Conger > >> > >> "Lynn" <Lynn@discussions.microsoft.com> wrote in message > >> news:79CA374D-729B-4181-8F45-5D61FCF30F28@microsoft.com... > >> > And, if I blow away the 2nd dataset and just give my 1st one > >> > parameters, > >> > it > >> > almost works. EXEC db.dbo.procname 1 for chunk one of the results, > >> > works > >> > just fine. if i change the parameter to 2, 3 or 4, when I hit RUN in > >> > Data > >> > tab it returns an error: SQL Syntax Errors Encountered....The designer > >> > does > >> > not graphically support the EXEC SQL Construct' but, i hit 'OK' and > >> > my > >> > results are still returned to me. On 'Preview' tab, however, I get no > >> > data/results at all. > >> > -- Lynn > >> > > >> > > >> > "Bruce L-C [MVP]" wrote: > >> > > >> >> Yes, you are calling it three times. In RS you create three datasets. > >> >> Go > >> >> to > >> >> the dataset tab and there is a combo box with the name of the dataset, > >> >> you > >> >> can click on that and there is a an option for a new. > >> >> > >> >> In your stored procedure > >> >> > >> >> if @WHICHRESULTSET = 1 > >> >> begin > >> >> --return the first resultset here > >> >> end > >> >> etc > >> >> > >> >> > >> >> -- > >> >> Bruce Loehle-Conger > >> >> MVP SQL Server Reporting Services > >> >> > >> >> "Lynn" <Lynn@discussions.microsoft.com> wrote in message > >> >> news:81E3A442-6694-442D-B151-F083E4750ADB@microsoft.com... > >> >> > Bruce, or somebody else, might you give me an example of how to give > >> >> > the > >> >> > proc > >> >> > 3 parameters, producing three resultsets in a way that RS will > >> >> > display > >> >> > them? > >> >> > Or, am I giving it three parameters and calling it three times? If > >> >> > so, > >> >> > how > >> >> > would i call the 3 procs in RS? > >> >> > -- Lynn > >> >> > > >> >> > > >> >> > "Bruce L-C [MVP]" wrote: > >> >> > |