July 17, 2009 at 7:37 am
I'm using SSRS 2005. I have a matrix that has row groupings for Company and Category and a column group for Season. The details are =Sum(Qty.Value) and = Sum(Amount.Value) for each Season.
I want to sort by a specific Season/Amount column but I don't know how to accomplish this.
I went into the sort properties of the Category group and specified =Sum(Fields!Amount.Value) but that doesn't work. I've attached an image of what my matrix looks like, I hope that helps.
July 17, 2009 at 12:02 pm
Can you possibly provide an example of what you are looking to get at for the results?
I am thinking, but not certain, that you will want to put the sort expression on the Season and it would look something like this:
=iif(InScope("matrix1_Season"), SUM(Fields!Amount.Value), 0)
Here is a good site to provide you some advanced tips on working with the Matrix in SSRS 2005 - http://www.simple-talk.com/sql/sql-server-2005/advanced-matrix-reporting-techniques/[/url]
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
July 20, 2009 at 7:33 am
That is a great article on simple-talk, I actually already used it.
Attached is what I get when I sort the category group by Sum(Fields!Amount.Value). I want it to be sorted by the dollar amount in the "current" season column. I'm guessing my sort expression needs to be more specific but I'm not sure of the syntax I need to specify and I haven't had much look searching for good examples.
I didn't have any luck with your suggestion. I think but I'm not sure that the sorting on the column group (season) controls the left to right sorting of the columns. For example, should Current come before Previous left to right or visa versa.
Thanks for your thoughts.
July 21, 2009 at 2:26 pm
You're correct that the sorting on Category group is sorting left to right. Your last image file shows categories within Co. #1 in numeric order, and what you need is to sort the individual categories by the amount field instead.
Steve
(aka sgmunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 21, 2009 at 2:42 pm
I guess I forgot to tell you how to do that. There's only one really good way to this that you could easily rely on. Group and sort your data in your T-SQL query instead of using the matrix to do the work. You can still let the matrix sort out the current and previous columns if you wish, but that's not at all necessary. Look up the PIVOT and UNPIVOT operators in Books Online. One of those can easily take separate records with Current and Previous amounts and put them together into a single record with field names for each, and you can also just FULL OUTER JOIN two tables together to achieve that kind of result by getting the data for each season separately at first.
I'm not sure if you can select the sort value for the category field using InScope or not, and additionally, there's a lot less data for the report to crunch if you let SQL Server handle the season aggregation instead.
You might find the report will run faster if SQL Server crunches the data instead of the matrix. I'd need some sample data to work with to develop a query in line with my suggestion, however, so I could have something to test it with.
Steve
(aka smunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 22, 2009 at 5:42 am
Thank you Steve. Those are good tips. I'm doing my sort and grouping within the query now and all is well. I appreciate you're assistance.
Jennifer
July 22, 2009 at 6:55 am
Thansk for the update and feedback.
Steve
(aka smunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 16, 2012 at 4:18 am
Are you sure that the SSRS matrix brings out the rows by the sort that was sent from the SQL query?
I did the sorting in SQL server, then took the data by SP to SSRS. At first the sorting worked (was displayed in the order as was set in the query) but at some point (maybe after I ran the query for large amount of data which was displayed in a multi-page matrix) the sorting stopped working.
Now I can't get the sorting tho be as in the SQL query at all.
Any help appreciated.
Thanks
January 16, 2012 at 4:22 pm
There's always an "it depends" that goes with using a matrix, and it sounds like you may have ran smack into one. You didn't specify any details on how you're using the matrix, and it can do some sorting of it's own under the right conditions. Generally, if you can make SQL Server do the grouping and sorting for you, a matrix isn't usually necessary... Controlling the matrix's sort can be tricky, but without any details on what you're doing with one, there's no easy answer as to what the nature of your problem is. Can you be more specific? After all, it would be easy to say that you said "it stopped working after I messed with it", and then reply with "then stop doing that", or "go back to the version before you messed with it", or some other oversimplified response, which would certainly not be very useful or helpful.
Steve
(aka sgmunson)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 17, 2012 at 3:42 am
I think I found the problem, I had leftovers from all my sort trials. Took that off and it works. Thanks for your time.
January 19, 2012 at 1:16 am
Now I'm facing a similar problem.
I got the sorting from the SQL displaying in the same default order on the SSRS matrix.
The problem is when I format the data (via the matrix textbox format property), the order changes!
(this includes minimal number fomatting such as N or P etc.)
Your help appriciated.
*** edited****:
After some checkings I see the matrix disorder is not from the format rather from some other cause. after I understand it enough I'll try to post what my problem was. so no need to check the format issue.
Thanks
January 19, 2012 at 1:22 am
Any idea for formating the data in the matrix (to display percent) without messing up the default matrix order from the SQL?...
*** edited****:
After some checkings I see the matrix disorder is not from the format rather from some other cause. after I understand it enough I'll try to post what my problem was. so no need to check the format issue.
Thanks
January 19, 2012 at 6:24 pm
Once again, "it depends"... It's very difficult to help someone without sample code for the query and sometimes, the xml for the report itself, or at least a screenshot for relevant elements. However, when I find myself perplexed, I will often just go ahead and save what I was doing and start fresh, and go with a brand new matrix, and then save this effort under a different filename. Sometimes, that's the best way to "undo" other options that may have been inadvertently imposed by other previous actions. If you have more details to share, then you may be able to get more specific guidance.
Steve
(aka sgmunson)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 22, 2012 at 12:03 am
Hi,
I think I got on when the misorder happens. Lets say I havea matrix with countries on the Y axis and 4 consecutive months on the Y axis. The data is the sales amount for each country in the specified month. I would like the matrix (countries and data) to be ordered by the descending sales amount value of the last month shown.
In my SQL I got the rows to be displayed in this order of preference and the SSRS matrix shows them in the default correct order.
The problem is when there are no values for the last month, then the matrix isn't sorted at all.
I would have wanted the matrix (even in the regular case when there are values in the last month) to be sorted by the last month sales value then by the second to last... third to last.... fourth to last... But as I see even though my SQL does return the lines in this order, the SSRS matrix seems to only keep the default sorting for the last month (= first section of SQL lines having the same month string column).
For the meantime I gave up on this but if you have any suggestions I'll be happy to hear. Thanks.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply