You say, "we enjoy the option of specifying sorting for
multiple columns within the matrix, table or list within which we
are working." But, when I add interactive sorting to the headers of a matrix, the up and down toggles appear in the preview, but the rows are not sorted after clicking.
This works perfectly fine on tables, as you say, but I have yet to see it on a matrix.
The challenge often lies in properly defining the scope within which the sort expression is evaluated - not as intutive as one might think sometimes ... but interactive sorting does, indeed, work for the matrix data region.
I hope this helps. Please advise if you have any further questions / comments. And thanks for sharing your input with the MS BI community!
Thanks for taking the time to write the article. I've read through and tried to reproduce your results with no luck.
On page 2, step 10, you write "In the dropdown selector that appears immediately underneath the Choose data region or grouping label, type the following:." I find that rather unintuitive that you should have to type in the dropdown that has no options to select, see screenshot, Reporting Services Matrix Sort.PNG, attached. That's the designer's fault, not yours. However, when I do type, I receive an error on preview, see screenshot, Reporting Services Matrix Sort error.PNG, attached.
I believe I've followed your steps exactly, though you don't state if you using the service pack. I am not.
While it may not affect my answer in this specific case, every article I write is based upon the assumption of the most recent service pack at the time of publication. I used to insert a statement to this effect, along with tons of additional verbage re: assumptions as to the environment, but have recently attempted to reduce size of the articles; I'll make it a point to put that back in, as I can see how it might cause confusion going forward.
Service packs are important in RS: I've seen the capability to add expressions to several designer fields appear with SPs - particularly with RS2k, but the tendency to add functionality in SPs seems to be increasing (what some have called a tendency toward "dribbleware," I suppose).
That being said, I think the issue you're having is something simple. You appear (thanks for the images!) to have inserted a "=" sign in the front of the "matrix1_Sales_Reason" entry in the Choose data region or grouping selector. The image I show does not contain the "=," although there appears to be a typo in the text showing it in the code.
I'll get that corrected in the article. In the meantime, take it out in your .rdl file and let me know if that doesn't fix it.
Now that it's working, I've noticed some interesting behavior of this type of sorting. Taking the role of the Comptroller, sorting on the name of the Sales Reason is not very interesting to me. What I'd really like to see is something based on the results, like the total amount of sales. What's interesting is that when I change the sort expressiong to, =Fields!Internet_Sales_Amount.Value, it can't be in order for every top level column grouping. This is more a limitation of the way we are showing the data, but it is an interesting effect.
The opportunties to expand upon the samples to which I'm limited in articles of this size / scope are vast. I do far more advanced stuff for clients every day. The tool extends our capabilites dramatically beyond those we are afforded in other Enterprise BI tools, in that it can be customized to a high degree with expressions in many cases, and has a very open architecture / API if we have a need or desire to go even further.
I hear you about answering Comptroller questions. As a CPA with a heavy "practical" financial / accounting background, I see the integrated MS BI solution as a massive leap forward in analytical exploration.
I agree. These are great tools for business analysts.
I think an interesting article topic would be a suggestion of best practices for when to use a matrix versus a table. A discussion of how sorting provides different value when analyzing data in these two data regions may be helpful to the community.
I applaud your efforts to educate the users of these tools. With such a new tool, it can often be difficult to find such great examples of prior work as you present.
You appear to grasp quite well the fact that the integrated MS BI solution consists of multiple layers ("Tools" plural, as you say). One of my constant messages is that successful implementations of the MS BI depend upon multi-dimensional architects, as I state at the following link:
Reply With Quote