|
|
You're in the
sql server reporting services
group:Sorting Not Working From Stored Procedure
sql server reporting services:
I have a stored procedure that manipulates data from one table and store it
into a temp table. In the Report Designer data tab, if I run the stored procedure the data is sorted by a sort field I added. However, when I view the report in Preview, the report is not sorted correctly. I have set the
Try removing the sort field from the report (since you already sorted it in
the SP). No reason to apply the sort a second time. My guess is something is not specified properly with your sort. I almost never use RS sorting, I sort in the query or the stored procedure. -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "Chriis" <Chriis@discussions.microsoft.com> wrote in message news:4764212F-E718-498F-A2CE-43FDCC7D45BC@microsoft.com... >I have a stored procedure that manipulates data from one table and store it > into a temp table. In the Report Designer data tab, if I run the stored > procedure the data is sorted by a sort field I added. However, when I > view > the report in Preview, the report is not sorted correctly. I have set the > sort field on the table and the group to be the sort field. Any help?
I removed the sort from RS, however the sorting is still wrong.
Let me explain exactly what I am doing. I am pulling Bulletin information that has a bulletin number associated with it. the numbers are sequential, however some bulletins have been deleted but need to show on the report as cancelled. So in my stored procedure i am pulling the data and as I loop through if the job has been cancelled I add it to the temp table. After I fill the temptable with all of the data including the cancelled job placeholders. I return this data sorted by bulletin number. When I run the SP in Query Analyzer or from the data tab in the Visual Studio Report Designer the data is sorted on the Buletin Number such as 40000 40001 - cancelled 40002 However in the report it drops the 40001 which was one of the added jobs to the bottom like 40000 40002 40003 ....... 40001 - cancelled It puts all of the records that where added to the temp table at the bottom. Any ideas? [quoted text, click to view] "Bruce L-C [MVP]" wrote:
> Try removing the sort field from the report (since you already sorted it in > the SP). No reason to apply the sort a second time. My guess is something is > not specified properly with your sort. I almost never use RS sorting, I sort > in the query or the stored procedure. > > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > "Chriis" <Chriis@discussions.microsoft.com> wrote in message > news:4764212F-E718-498F-A2CE-43FDCC7D45BC@microsoft.com... > >I have a stored procedure that manipulates data from one table and store it > > into a temp table. In the Report Designer data tab, if I run the stored > > procedure the data is sorted by a sort field I added. However, when I > > view > > the report in Preview, the report is not sorted correctly. I have set the > > sort field on the table and the group to be the sort field. Any help? > >
I figured it out. I had a hidden group that was not being used, however it
was hosing up the sort. I took it out and it now works. Don't really understand why it was hosing up the sort, but for whatever reason that fixed it. Thanks so much for your help. [quoted text, click to view] "Bruce L-C [MVP]" wrote:
> Yes. What is your statement returning data from the temp table? You should > have an order by in that statement. > > select * from #temp order by bullitin_number > > > -- > Bruce Loehle-Conger > MVP SQL Server Reporting Services > > "Chriis" <Chriis@discussions.microsoft.com> wrote in message > news:3214F4E0-E97B-42A6-9699-1520A6A3CF86@microsoft.com... > >I removed the sort from RS, however the sorting is still wrong. > > > > Let me explain exactly what I am doing. I am pulling Bulletin information > > that has a bulletin number associated with it. the numbers are > > sequential, > > however some bulletins have been deleted but need to show on the report as > > cancelled. So in my stored procedure i am pulling the data and as I loop > > through if the job has been cancelled I add it to the temp table. After I > > fill the temptable with all of the data including the cancelled job > > placeholders. I return this data sorted by bulletin number. > > > > When I run the SP in Query Analyzer or from the data tab in the Visual > > Studio Report Designer the data is sorted on the Buletin Number such as > > 40000 > > 40001 - cancelled > > 40002 > > However in the report it drops the 40001 which was one of the added jobs > > to > > the bottom like > > 40000 > > 40002 > > 40003 > > ...... > > 40001 - cancelled > > > > It puts all of the records that where added to the temp table at the > > bottom. > > > > Any ideas? > > > > "Bruce L-C [MVP]" wrote: > > > >> Try removing the sort field from the report (since you already sorted it > >> in > >> the SP). No reason to apply the sort a second time. My guess is something > >> is > >> not specified properly with your sort. I almost never use RS sorting, I > >> sort > >> in the query or the stored procedure. > >> > >> > >> -- > >> Bruce Loehle-Conger > >> MVP SQL Server Reporting Services > >> > >> "Chriis" <Chriis@discussions.microsoft.com> wrote in message > >> news:4764212F-E718-498F-A2CE-43FDCC7D45BC@microsoft.com... > >> >I have a stored procedure that manipulates data from one table and store > >> >it > >> > into a temp table. In the Report Designer data tab, if I run the > >> > stored > >> > procedure the data is sorted by a sort field I added. However, when I > >> > view > >> > the report in Preview, the report is not sorted correctly. I have set > >> > the > >> > sort field on the table and the group to be the sort field. Any help? > >> > >> > >> > >
Yes. What is your statement returning data from the temp table? You should
have an order by in that statement. select * from #temp order by bullitin_number -- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "Chriis" <Chriis@discussions.microsoft.com> wrote in message news:3214F4E0-E97B-42A6-9699-1520A6A3CF86@microsoft.com... >I removed the sort from RS, however the sorting is still wrong. > > Let me explain exactly what I am doing. I am pulling Bulletin information > that has a bulletin number associated with it. the numbers are > sequential, > however some bulletins have been deleted but need to show on the report as > cancelled. So in my stored procedure i am pulling the data and as I loop > through if the job has been cancelled I add it to the temp table. After I > fill the temptable with all of the data including the cancelled job > placeholders. I return this data sorted by bulletin number. > > When I run the SP in Query Analyzer or from the data tab in the Visual > Studio Report Designer the data is sorted on the Buletin Number such as > 40000 > 40001 - cancelled > 40002 > However in the report it drops the 40001 which was one of the added jobs > to > the bottom like > 40000 > 40002 > 40003 > ...... > 40001 - cancelled > > It puts all of the records that where added to the temp table at the > bottom. > > Any ideas? > > "Bruce L-C [MVP]" wrote: > >> Try removing the sort field from the report (since you already sorted it >> in >> the SP). No reason to apply the sort a second time. My guess is something >> is >> not specified properly with your sort. I almost never use RS sorting, I >> sort >> in the query or the stored procedure. >> >> >> -- >> Bruce Loehle-Conger >> MVP SQL Server Reporting Services >> >> "Chriis" <Chriis@discussions.microsoft.com> wrote in message >> news:4764212F-E718-498F-A2CE-43FDCC7D45BC@microsoft.com... >> >I have a stored procedure that manipulates data from one table and store >> >it >> > into a temp table. In the Report Designer data tab, if I run the >> > stored >> > procedure the data is sorted by a sort field I added. However, when I >> > view >> > the report in Preview, the report is not sorted correctly. I have set >> > the >> > sort field on the table and the group to be the sort field. Any help? >> >> >>
Good. Glad you got it figured out.
-- Bruce Loehle-Conger MVP SQL Server Reporting Services [quoted text, click to view] "Chriis" <Chriis@discussions.microsoft.com> wrote in message news:485A7471-1C4B-4851-A38F-9BA1DADDCF7C@microsoft.com... >I figured it out. I had a hidden group that was not being used, however it > was hosing up the sort. I took it out and it now works. Don't really > understand why it was hosing up the sort, but for whatever reason that > fixed > it. > > Thanks so much for your help. > > "Bruce L-C [MVP]" wrote: > >> Yes. What is your statement returning data from the temp table? You >> should >> have an order by in that statement. >> >> select * from #temp order by bullitin_number >> >> >> -- >> Bruce Loehle-Conger >> MVP SQL Server Reporting Services >> >> "Chriis" <Chriis@discussions.microsoft.com> wrote in message >> news:3214F4E0-E97B-42A6-9699-1520A6A3CF86@microsoft.com... >> >I removed the sort from RS, however the sorting is still wrong. >> > >> > Let me explain exactly what I am doing. I am pulling Bulletin >> > information >> > that has a bulletin number associated with it. the numbers are >> > sequential, >> > however some bulletins have been deleted but need to show on the report >> > as >> > cancelled. So in my stored procedure i am pulling the data and as I >> > loop >> > through if the job has been cancelled I add it to the temp table. >> > After I >> > fill the temptable with all of the data including the cancelled job >> > placeholders. I return this data sorted by bulletin number. >> > >> > When I run the SP in Query Analyzer or from the data tab in the Visual >> > Studio Report Designer the data is sorted on the Buletin Number such as >> > 40000 >> > 40001 - cancelled >> > 40002 >> > However in the report it drops the 40001 which was one of the added >> > jobs >> > to >> > the bottom like >> > 40000 >> > 40002 >> > 40003 >> > ...... >> > 40001 - cancelled >> > >> > It puts all of the records that where added to the temp table at the >> > bottom. >> > >> > Any ideas? >> > >> > "Bruce L-C [MVP]" wrote: >> > >> >> Try removing the sort field from the report (since you already sorted >> >> it >> >> in >> >> the SP). No reason to apply the sort a second time. My guess is >> >> something >> >> is >> >> not specified properly with your sort. I almost never use RS sorting, >> >> I >> >> sort >> >> in the query or the stored procedure. >> >> >> >> >> >> -- >> >> Bruce Loehle-Conger >> >> MVP SQL Server Reporting Services >> >> >> >> "Chriis" <Chriis@discussions.microsoft.com> wrote in message >> >> news:4764212F-E718-498F-A2CE-43FDCC7D45BC@microsoft.com... >> >> >I have a stored procedure that manipulates data from one table and >> >> >store >> >> >it >> >> > into a temp table. In the Report Designer data tab, if I run the >> >> > stored >> >> > procedure the data is sorted by a sort field I added. However, when >> >> > I >> >> > view >> >> > the report in Preview, the report is not sorted correctly. I have >> >> > set >> >> > the >> >> > sort field on the table and the group to be the sort field. Any >> >> > help? >> >> >> >> >> >> >> >> >>
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 |