CODEWITHSUNDEEP
reporting-servicesssrs-2008ssrs-2008-r2
UpwardD
UpwardD

Reputation: 767

Sorting Row In SSRS

I am trying to get the "Total" row of a report in SSRS to stay put at the buttom but all to no availed. My Total field is being calculated from a query like so:

    Select A
         , B
         , C
    FROM D
     UNION 
    SELECT 'Totals'
         , SUM(B)
         , SUM(C)
   FROM D

How do I sort this to be the last row in the report rather than at the top?

Upvotes: 1

Views: 105

Answers (3)

Trubs
Trubs

Reputation: 3032

Modify your script so that the sort order is contained within the results, Like so...

Select SortOrder = 0
         , A
         , B
         , C
    FROM D
     UNION 
    SELECT 1
         , 'Totals'
         , SUM(B)
         , SUM(C)
   FROM D

And then set the Sorting in your group enter image description here

But @TPhe's solution is far better option...

Upvotes: 0

Arthur D
Arthur D

Reputation: 86

At the bottom of the SSRS page you should see something called Row Groups. Right click on Row Groups and go to Group Properties. Click Sort, and then experiment with sorting options so that you see your totals at the bottom of your table.

Also, consider adding something to your union that defines it as the total or non-total function. I.E. Select 1 as Sort UNION ALL Select 2 as Sort

Then sort your data set by this "Sort" column. Hope this helps!

Upvotes: 1

TPhe
TPhe

Reputation: 1671

I would definitely remove the union and total query from your query and then use the features of SSRS to calculate the total for you. It will be easier and more flexible that way. Here is more information on adding a totals row in SSRS. If there is some reason you can't, let me know.

Upvotes: 2

Related Questions