SSRS Bad Sorting

Question

I am connecting to a SQL Server 2014 database using SSRS 2014. I have used SSRS since its initial version, but have never experienced this problem, so I don’t know if there is an error in SSRS 2014. I have a stored procedure that returns some data something simple:

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 END 

If I started the stored procedure from Management Studio like this:

 USE [MyDB] GO DECLARE @return_value int EXEC @return_value = [dbo].[GetNewsletterStories] @NewsletterID = 1, @IsMainStory = 1 SELECT 'Return Value' = @return_value GO 

I get exactly what I need in the correct order:

Then I am in SSRS, and I create my dataset and can go to the query designer and run it:

Here he will tell me my options:

I get exactly what I need:

So far, so good, I added my tablix and set its dataset name that I created, and even went as far as setting the sort section to [SortOrder] based on the column in my dataset:

Every time I run my report, I get the wrong sort order:

I just thought that it should cache some old version, so I will definitely delete the MyReport.rdl.data report files to get a fresh report. No, this also shows Concerto Integration, and then RIMS Newsletter ... but my dataset and my stored procedure return the correct order for RIMS Newsletter and Concerto Integration. I tried everything, but it always seems to sort by the first key of the table (when the article was created, since I created the Concerto Integration article before the RIMMS Newsletter article).

I don’t know where else to look, I even checked the resulting XML (code view function), and SortOrder in the data set. Even if I delete this SortOrder , it should work, because the stored procedure is already sorted by it, as you see in the code.

What gives?

Answer 1

Wow found this and, as usual, this is an error in SSRS, I remember that such things were errors in SSRS 2005 and SSRS 2008. So even if you change SortOrder in the designer, in my case I changed it to SortOrder, This did not work ... so I right-clicked on the actual report and did "View Code". And I searched for SortExpression, and it did not have a SortOrder value, its almost like in some cases when you update a report from the designer, part of the xml code does not see these updates.

Anyway, I changed it so that XML reads this:

  <SortExpressions> <SortExpression> <Value>=Fields!SortOrder.Value</Value> </SortExpression> </SortExpressions> 

Saved my report and ran it again and voila, now everything is fine. Lesson. I learned to use previous experience with the option "View code" to fix such problems.

That's all:

 <TablixMembers> <TablixMember> <Group Name="Title"> <GroupExpressions> <GroupExpression>=Fields!Title.Value</GroupExpression> </GroupExpressions> </Group> <SortExpressions> <SortExpression> <Value>=Fields!SortOrder.Value</Value> </SortExpression> </SortExpressions> <TablixMembers> <TablixMember> <Group Name="Details2" /> <TablixMembers> <TablixMember /> <TablixMember /> <TablixMember /> </TablixMembers> </TablixMember> </TablixMembers> </TablixMember> </TablixMembers> 

No matter what I did in the designer, part of the sortexpression value will not be updated, this part:

 <SortExpressions> <SortExpression> <Value>=Fields!SortOrder.Value</Value> </SortExpression> </SortExpressions> 

I tried through the constructor, but it never updated the XML, and running the report led to the wrong result. Its only until I manually changed SortExpression to read fields! SortOrder.Value made my report correctly sorted.

Here is the screenshot mentioned in the comments:

Best photo:

Even if I do this at the header level of the same problem:

Answer 2

SQL Server 2012

In row groups, I changed the sorting of the first columns to what I want. Go to the row group> Change Sort as shown in the figures. In addition, I changed the tablix collation.

Answer 3

I had the same problem, it would only sort in the group that was the name, not the sort order I wanted. Changing the sort order of Tablix has not affected. I was able to access it by going to the group properties and selecting Sort. There I changed the "sort by" property from the "name" field of the data set to the "sortorder" field of the data set and fixed it. The sort order at the Tablix level seems to be overridden by the sort order of the groups. Jay