|
|
You're in the
sql server reporting services
group:same data set, different parameters
sql server reporting services:
I have a stored proc that returns results based on startdate and enddate parameters. I have a report that has a matrix on it which needs to show totals for the previous day, month to date, and 3 previous months. To get the data all that is required is for me to run the same proc 5 times with the different date ranges. The problem I have is trying to display this data in the matrix. I was first thinking of using seperate data sets, but it seemed redundant. Is there any way to accomplish this without using seperate datasets? Thanks! -- Ben Sullins Our Vacation Store
Thanks Bruce!
Unfortunately I'm still having some trouble here... So by creating different data sets I can change the parameters, i got that, but how am i going to display the data in the same matrix? Here's an example of what i'm looking for... /*********************************************************/ Department 01/03/04 MTD Dec-04 Nov-04 Oct-04 ___________________________________________________________________ Vacation Sales 100 329 1920 1563 2345 Cert Redemption 34 124 654 736 863 /*********************************************************/ Thanks! -- Ben Sullins [quoted text, click to view] "Bruce L-C [MVP]" wrote:
> You want separate datasets. However, since all the dates can be derived from > the first 2, you only need 2 report parameters. When RS automatically > creates additional report parameters just delete them (from layout tab, > report parameters). In the dataset click on the ... then parameters and map > to the same 2 report parameters. Note that the mapping can be an expression > so you can manipulate the dates during the mapping of query parameters to > report parameters. > > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > "Ben Sullins" <BenSullins@discussions.microsoft.com> wrote in message > news:0B4CA38E-6437-49C0-B5CC-DAEEBEEB9008@microsoft.com... > > greetings... > > > > I have a stored proc that returns results based on startdate and enddate > > parameters. I have a report that has a matrix on it which needs to show > > totals for the previous day, month to date, and 3 previous months. To get > the > > data all that is required is for me to run the same proc 5 times with the > > different date ranges. The problem I have is trying to display this data > in > > the matrix. I was first thinking of using seperate data sets, but it > seemed > > redundant. > > > > Is there any way to accomplish this without using seperate datasets? > > > > Thanks! > > > > -- > > Ben Sullins > > Our Vacation Store > > http://ovstravelfolio.com/ > >
You want separate datasets. However, since all the dates can be derived from
the first 2, you only need 2 report parameters. When RS automatically creates additional report parameters just delete them (from layout tab, report parameters). In the dataset click on the ... then parameters and map to the same 2 report parameters. Note that the mapping can be an expression so you can manipulate the dates during the mapping of query parameters to report parameters. -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "Ben Sullins" <BenSullins@discussions.microsoft.com> wrote in message news:0B4CA38E-6437-49C0-B5CC-DAEEBEEB9008@microsoft.com... > greetings... > > I have a stored proc that returns results based on startdate and enddate > parameters. I have a report that has a matrix on it which needs to show > totals for the previous day, month to date, and 3 previous months. To get the > data all that is required is for me to run the same proc 5 times with the > different date ranges. The problem I have is trying to display this data in > the matrix. I was first thinking of using seperate data sets, but it seemed > redundant. > > Is there any way to accomplish this without using seperate datasets? > > Thanks! > > -- > Ben Sullins > Our Vacation Store > http://ovstravelfolio.com/
I'm sorry, I missed that. Well, that is a lot more complicated. You can
either have a stored procedure wrapper that calls the original sp 5 times and combines it OR you can use subreports. You can put the sub report into a field of the table. RS has no way to join datasets together. -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "Ben Sullins" <BenSullins@discussions.microsoft.com> wrote in message news:0C437E4C-F478-4C5F-8D35-56786A24A6AA@microsoft.com... > Thanks Bruce! > > Unfortunately I'm still having some trouble here... > > So by creating different data sets I can change the parameters, i got > that, > but how am i going to display the data in the same matrix? > > Here's an example of what i'm looking for... > > /*********************************************************/ > Department 01/03/04 MTD Dec-04 > Nov-04 Oct-04 > ___________________________________________________________________ > Vacation Sales 100 329 1920 > 1563 2345 > Cert Redemption 34 124 654 > 736 863 > > /*********************************************************/ > > Thanks! > -- > Ben Sullins > > "Bruce L-C [MVP]" wrote: > >> You want separate datasets. However, since all the dates can be derived >> from >> the first 2, you only need 2 report parameters. When RS automatically >> creates additional report parameters just delete them (from layout tab, >> report parameters). In the dataset click on the ... then parameters and >> map >> to the same 2 report parameters. Note that the mapping can be an >> expression >> so you can manipulate the dates during the mapping of query parameters to >> report parameters. >> >> >> -- >> Bruce Loehle-Conger >> MVP SQL Server Reporting Services >> >> "Ben Sullins" <BenSullins@discussions.microsoft.com> wrote in message >> news:0B4CA38E-6437-49C0-B5CC-DAEEBEEB9008@microsoft.com... >> > greetings... >> > >> > I have a stored proc that returns results based on startdate and >> > enddate >> > parameters. I have a report that has a matrix on it which needs to show >> > totals for the previous day, month to date, and 3 previous months. To >> > get >> the >> > data all that is required is for me to run the same proc 5 times with >> > the >> > different date ranges. The problem I have is trying to display this >> > data >> in >> > the matrix. I was first thinking of using seperate data sets, but it >> seemed >> > redundant. >> > >> > Is there any way to accomplish this without using seperate datasets? >> > >> > Thanks! >> > >> > -- >> > Ben Sullins >> > Our Vacation Store >> > http://ovstravelfolio.com/ >> >> >>
Bruce,
I was able to create a proc that returns the data i need in the correct format, now when I try to call it from reporting services it tells me it does not exist. Any idea what this could be? thanks again... [quoted text, click to view] "Bruce L-C [MVP]" wrote:
> I'm sorry, I missed that. Well, that is a lot more complicated. You can > either have a stored procedure wrapper that calls the original sp 5 times > and combines it OR you can use subreports. You can put the sub report into a > field of the table. > > RS has no way to join datasets together. > > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > > "Ben Sullins" <BenSullins@discussions.microsoft.com> wrote in message > news:0C437E4C-F478-4C5F-8D35-56786A24A6AA@microsoft.com... > > Thanks Bruce! > > > > Unfortunately I'm still having some trouble here... > > > > So by creating different data sets I can change the parameters, i got > > that, > > but how am i going to display the data in the same matrix? > > > > Here's an example of what i'm looking for... > > > > /*********************************************************/ > > Department 01/03/04 MTD Dec-04 > > Nov-04 Oct-04 > > ___________________________________________________________________ > > Vacation Sales 100 329 1920 > > 1563 2345 > > Cert Redemption 34 124 654 > > 736 863 > > > > /*********************************************************/ > > > > Thanks! > > -- > > Ben Sullins > > > > "Bruce L-C [MVP]" wrote: > > > >> You want separate datasets. However, since all the dates can be derived > >> from > >> the first 2, you only need 2 report parameters. When RS automatically > >> creates additional report parameters just delete them (from layout tab, > >> report parameters). In the dataset click on the ... then parameters and > >> map > >> to the same 2 report parameters. Note that the mapping can be an > >> expression > >> so you can manipulate the dates during the mapping of query parameters to > >> report parameters. > >> > >> > >> -- > >> Bruce Loehle-Conger > >> MVP SQL Server Reporting Services > >> > >> "Ben Sullins" <BenSullins@discussions.microsoft.com> wrote in message > >> news:0B4CA38E-6437-49C0-B5CC-DAEEBEEB9008@microsoft.com... > >> > greetings... > >> > > >> > I have a stored proc that returns results based on startdate and > >> > enddate > >> > parameters. I have a report that has a matrix on it which needs to show > >> > totals for the previous day, month to date, and 3 previous months. To > >> > get > >> the > >> > data all that is required is for me to run the same proc 5 times with > >> > the > >> > different date ranges. The problem I have is trying to display this > >> > data > >> in > >> > the matrix. I was first thinking of using seperate data sets, but it > >> seemed > >> > redundant. > >> > > >> > Is there any way to accomplish this without using seperate datasets? > >> > > >> > Thanks! > >> > > >> > -- > >> > Ben Sullins > >> > Our Vacation Store > >> > http://ovstravelfolio.com/ > >> > >> > >> > >
Thanks Bruce!
For some reason the proc wasn't showing up in the sysobject table of the DB. I dropped it and recreated it and it worked fine... I have another question on using parameters in c# functions, i'll do it in a new post though since it is not related... [quoted text, click to view] "Bruce L-C [MVP]" wrote:
> Usually when that happens to me it is because I am using a different user to > access the stored procedure than the stored procedure was created as. > > When trying to execute a stored procedure, if you do not explicitly specify > the owner then it defaults to dbo. If you created it as you then > dbo.yourstoredprocedure does not exist (happens to me all the time). Your > full stored procedure name is yourname.yourstoreprocedure. Create the stored > procedure with the owner as dbo > > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > > > "Ben Sullins" <BenSullins@discussions.microsoft.com> wrote in message > news:E2A6F4FE-EBD6-4EF6-936D-2405844074DC@microsoft.com... > > Bruce, > > > > I was able to create a proc that returns the data i need in the correct > > format, now when I try to call it from reporting services it tells me it > does > > not exist. Any idea what this could be? > > > > thanks again... > > > > "Bruce L-C [MVP]" wrote: > > > > > I'm sorry, I missed that. Well, that is a lot more complicated. You can > > > either have a stored procedure wrapper that calls the original sp 5 > times > > > and combines it OR you can use subreports. You can put the sub report > into a > > > field of the table. > > > > > > RS has no way to join datasets together. > > > > > > > > > -- > > > Bruce Loehle-Conger > > > MVP SQL Server Reporting Services > > > > > > > > > "Ben Sullins" <BenSullins@discussions.microsoft.com> wrote in message > > > news:0C437E4C-F478-4C5F-8D35-56786A24A6AA@microsoft.com... > > > > Thanks Bruce! > > > > > > > > Unfortunately I'm still having some trouble here... > > > > > > > > So by creating different data sets I can change the parameters, i got > > > > that, > > > > but how am i going to display the data in the same matrix? > > > > > > > > Here's an example of what i'm looking for... > > > > > > > > /*********************************************************/ > > > > Department 01/03/04 MTD Dec-04 > > > > Nov-04 Oct-04 > > > > ___________________________________________________________________ > > > > Vacation Sales 100 329 1920 > > > > 1563 2345 > > > > Cert Redemption 34 124 654 > > > > 736 863 > > > > > > > > /*********************************************************/ > > > > > > > > Thanks! > > > > -- > > > > Ben Sullins > > > > > > > > "Bruce L-C [MVP]" wrote: > > > > > > > >> You want separate datasets. However, since all the dates can be > derived > > > >> from > > > >> the first 2, you only need 2 report parameters. When RS automatically > > > >> creates additional report parameters just delete them (from layout > tab, > > > >> report parameters). In the dataset click on the ... then parameters > and > > > >> map > > > >> to the same 2 report parameters. Note that the mapping can be an > > > >> expression > > > >> so you can manipulate the dates during the mapping of query > parameters to > > > >> report parameters. > > > >> > > > >> > > > >> -- > > > >> Bruce Loehle-Conger > > > >> MVP SQL Server Reporting Services > > > >> > > > >> "Ben Sullins" <BenSullins@discussions.microsoft.com> wrote in message > > > >> news:0B4CA38E-6437-49C0-B5CC-DAEEBEEB9008@microsoft.com... > > > >> > greetings... > > > >> > > > > >> > I have a stored proc that returns results based on startdate and > > > >> > enddate > > > >> > parameters. I have a report that has a matrix on it which needs to > show > > > >> > totals for the previous day, month to date, and 3 previous months. > To > > > >> > get > > > >> the > > > >> > data all that is required is for me to run the same proc 5 times > with > > > >> > the > > > >> > different date ranges. The problem I have is trying to display this > > > >> > data > > > >> in > > > >> > the matrix. I was first thinking of using seperate data sets, but > it > > > >> seemed > > > >> > redundant. > > > >> > > > > >> > Is there any way to accomplish this without using seperate > datasets? > > > >> > > > > >> > Thanks! > > > >> > > > > >> > -- > > > >> > Ben Sullins > > > >> > Our Vacation Store > > > >> > http://ovstravelfolio.com/ > > > >> > > > >> > > > >> > > > > > > > > > > >
Usually when that happens to me it is because I am using a different user to
access the stored procedure than the stored procedure was created as. When trying to execute a stored procedure, if you do not explicitly specify the owner then it defaults to dbo. If you created it as you then dbo.yourstoredprocedure does not exist (happens to me all the time). Your full stored procedure name is yourname.yourstoreprocedure. Create the stored procedure with the owner as dbo -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "Ben Sullins" <BenSullins@discussions.microsoft.com> wrote in message news:E2A6F4FE-EBD6-4EF6-936D-2405844074DC@microsoft.com... > Bruce, > > I was able to create a proc that returns the data i need in the correct > format, now when I try to call it from reporting services it tells me it does > not exist. Any idea what this could be? > > thanks again... > > "Bruce L-C [MVP]" wrote: > > > I'm sorry, I missed that. Well, that is a lot more complicated. You can > > either have a stored procedure wrapper that calls the original sp 5 times > > and combines it OR you can use subreports. You can put the sub report into a > > field of the table. > > > > RS has no way to join datasets together. > > > > > > -- > > Bruce Loehle-Conger > > MVP SQL Server Reporting Services > > > > > > "Ben Sullins" <BenSullins@discussions.microsoft.com> wrote in message > > news:0C437E4C-F478-4C5F-8D35-56786A24A6AA@microsoft.com... > > > Thanks Bruce! > > > > > > Unfortunately I'm still having some trouble here... > > > > > > So by creating different data sets I can change the parameters, i got > > > that, > > > but how am i going to display the data in the same matrix? > > > > > > Here's an example of what i'm looking for... > > > > > > /*********************************************************/ > > > Department 01/03/04 MTD Dec-04 > > > Nov-04 Oct-04 > > > ___________________________________________________________________ > > > Vacation Sales 100 329 1920 > > > 1563 2345 > > > Cert Redemption 34 124 654 > > > 736 863 > > > > > > /*********************************************************/ > > > > > > Thanks! > > > -- > > > Ben Sullins > > > > > > "Bruce L-C [MVP]" wrote: > > > > > >> You want separate datasets. However, since all the dates can be derived > > >> from > > >> the first 2, you only need 2 report parameters. When RS automatically > > >> creates additional report parameters just delete them (from layout tab, > > >> report parameters). In the dataset click on the ... then parameters and > > >> map > > >> to the same 2 report parameters. Note that the mapping can be an > > >> expression > > >> so you can manipulate the dates during the mapping of query parameters to > > >> report parameters. > > >> > > >> > > >> -- > > >> Bruce Loehle-Conger > > >> MVP SQL Server Reporting Services > > >> > > >> "Ben Sullins" <BenSullins@discussions.microsoft.com> wrote in message > > >> news:0B4CA38E-6437-49C0-B5CC-DAEEBEEB9008@microsoft.com... > > >> > greetings... > > >> > > > >> > I have a stored proc that returns results based on startdate and > > >> > enddate > > >> > parameters. I have a report that has a matrix on it which needs to show > > >> > totals for the previous day, month to date, and 3 previous months. To > > >> > get > > >> the > > >> > data all that is required is for me to run the same proc 5 times with > > >> > the > > >> > different date ranges. The problem I have is trying to display this > > >> > data > > >> in > > >> > the matrix. I was first thinking of using seperate data sets, but it > > >> seemed > > >> > redundant. > > >> > > > >> > Is there any way to accomplish this without using seperate datasets? > > >> > > > >> > Thanks! > > >> > > > >> > -- > > >> > Ben Sullins > > >> > Our Vacation Store > > >> > http://ovstravelfolio.com/ > > >> > > >> > > >> > > > > > >
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 |