SSRS Not sorting correctly

Question

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
END

If 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

GO

I 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?

Answer 1

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:

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

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:

<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>

Whatever I did in the designer would not update the sortexpression value portion, this portion:

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

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: