I'm tapping into a SQL Server 2014 database using SSRS 2014. I've used SSRS from its initial release but have never experienced this problem so I don't know if its a bug in SSRS 2014. I have a stored procedure that returns some data, something as simple as this:
CREATE PROCEDURE [dbo].[GetNewsletterStories]
@NewsletterID int,
@IsMainStory int=2 --2 is both, 1 is true, 0 is false
AS
BEGIN
SET NOCOUNT ON;
SELECT
ns.SortOrder as SortOrder,
ns.Title,
ns.Description,
ns.LinkText,
ns.LinkURL,
ns.PictureName,
ns.IsMainStory
FROM
NewsletterStory ns
INNER JOIN
Newsletter n
ON
n.NewsletterID = ns.NewsletterID
WHERE
n.Deleted=0
AND ns.Deleted=0
AND n.NewsletterID = @NewsletterID
--do they want non main story or main story or both
AND ((ns.IsMainStory = 0 AND @IsMainStory=0) OR (ns.IsMainStory = 1 AND @IsMainStory=1) OR (@IsMainStory=2))
ORDER BY
ns.SortOrder
ENDIf I run the stored procedure from within Management Studio like so:
USE [MyDB]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[GetNewsletterStories]
@NewsletterID = 1,
@IsMainStory = 1
SELECT 'Return Value' = @return_value
GOI get exactly what I need in the correct order:
Then I'm in SSRS and I create my dataset and can go to the query designer and run it:
Here is where it prompts me for my parameters:
I get exactly what I needed:
So far so good, I added my tablix and set its dataset name I created and even went as far as setting the sorting section to [SortOrder] based on the column in my dataset:
Every time I run my report I get the wrong sort order:
Simple I thought, it must be caching some old version, so I made sure to delete my reports MyReport.rdl.data file so that I get a fresh report. Nope that too still shows "Concerto Integration" first and then "RIMS Newsletter"...but my dataset and my stored procedure is returning the right order "RIMS Newsletter" and "Concerto Integration". I've tried everything but it always seems to be sorting by the primary key of the table (when the article was created, as I created the Concerto Integration article before the RIMS Newsletter article).
I don't know where else to look, I even inspected the resultant XML (view code feature) and the SortOrder is in the dataset. Even if I remove that SortOrder it should work because the stored procedure is already sorting by it as you see in the code.
What gives?
4条答案
按热度按时间bqucvtff1#
Wow found it and as usual it is a bug in SSRS, I remember these sorts of things being bugs in SSRS 2005 and SSRS 2008. So even if you change the SortOrder in the designer, in my case I changed it to SortOrder. That didnt work...so what I did was right clicked on the actual report and did a "View Code". And I looked for SortExpression and it did not have the value SortOrder, its almost like in some cases when you update the report from the designer the code xml portion does not see those updates.
Anyhow I changed it such that the XML read this:
Saved my report and ran it again and voila, all is well now. Lesson Learned to use prior experience with the "View Code" option to fix issues like this.
Here was the whole thing:
Whatever I did in the designer would not update the sortexpression value portion, this portion:
I tried through the designer but it never updated the XML, and running the report produced the wrong result. Its only until I manually changed the SortExpression to read Fields!SortOrder.Value did my report sort correctly.
Here's the screenshot as mentioned in the comments:
Better photo:
Even if I do it at the title level same issue:
mlnl4t2r2#
SQL Server 2012
In row groups, I changed first columns sorting to what I want. Go to Row group> Change sorting as shown in figures. Also, I have changed the tablix sorting.
zc0qhyus3#
I had the same issue, it would only sort on the Group which was the name not the sort order that I wanted. Changing the Tablix's sort order had no affect. I was able to address it by going to the Group properties and selecting Sorting. In there I changed the "sort by" property from the datasets "name" field to the datasets "sortorder" field and that fixed it. The Sort Order at the Tablix level apparently is overridden by the groupings sort order. Jay
z0qdvdin4#
Thank you, yes, the group sort order was overriding the dataset. No XML editing required. And I have four sort levels, last one is the one I really needed from the Dataset query.