Post reply

Sorting dynamic value per count in ssrs matrix

  • May 25, 2017 at 3:37 pm

    #330627

    For the below matrix year and values are coming dynamically ..i wanted to display the 2017 column values in descending order.. i tried doing in sql but its not reflecting the samein matrix ..tried to sort matrix level that also not working... any insight would really helps!!

  • May 25, 2017 at 9:48 pm

    #1944292

    Got it to work... probably a kludge, but ...Summarized in T-SQL first:
    SELECT SalesRep
        , SUM(Sales2016) AS Total2016
        , SUM(Sales2017) AS Total2017
        , SUM(Sales2017) - SUM(Sales2016) AS Delta
    FROM
    (SELECT SalesRep
        , SalesYear
    --    , SalesYTD
        , CASE WHEN SalesYear = 2016 THEN SalesYTD ELSE 0 END As Sales2016
        , CASE WHEN SalesYear = 2017 THEN SalesYTD ELSE 0 END AS Sales2017
    FROM dbo.TerritoryYTD) x
    GROUP BY SalesRep;
    Then in SSRS, I added a

      tablix

    (not a matrix). and added (SalesRep, SalesYear, Sales2016 and Sales2017).
    Then I sorted the tablix on Sales2017 DESC.

  • May 26, 2017 at 6:24 am

    #1944314

    Set the sort expression on the row group to
    =Sum(IIf(Year=2017,1,0))

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply