Unfortunately the SharePoint Listing RSS feed will not allow you to actually
sort the list. So you end up with a parameter list populated with an XML call like this:
<rssharepointlist xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<listname>MyProjects
<viewfields>
<fieldref name="Project_Name">
<fieldref name="ID">
So as you can see from the Microsoft specification if you go look for it. You cannot sort this at all. You can read about
that here: http://msdn.microsoft.com/en-us/library/ee633650.aspx.
The really annoying part is the start of the second paragraph in the applied filters section. "You cannot change the sort order "
can we all just say *UGH* seriously MS why can't I apply a sort to a SharePoint List in SSRS? Oh I know the whole thing about you apply it to the default view.
But I have notice that getting lost. I have an actual example of it getting lost but I cannot show it here. Confidential names yadda yadda. Just trust me
it gets lost from development on my machine to the deployed state on the server.
So all that being said. What you have in each item in the SharePoint Drop parameters list is an Array of Objects. But you might have one array for the
Values and another array for the User readable titles. Case in point the List above. I have the "ID" Field and I also have the "Project_Name" field.
The users can read the Title Field but the server can only really read the ID field. So we need a way to make this all work.
Well, without further ado. here is your code for doing just that.
Public Shared Function sortObject(ByVal spstr As Object(), _
ByVal lblStr As Object(), ByVal iCol As Integer) As Object()
Dim tempLbl(UBound(lblStr)) As Object
Dim tempVal(UBound(spstr)) As Object
tempLbl = lblStr
Array.Sort(lblStr)
For i As Integer = 0 To UBound(lblStr)
For j As Integer = 0 To UBound(tempLbl)
If lblStr(i) = tempLbl(j) Then
tempVal(i) = spstr(j)
Exit For
End If
Next
Next
If iCol = 1 Then
Return tempVal
Else
Return lblStr
End If
End Function
The call to make this work is something along the line of =Code.sortObject(Parameters!ProjectNum.Value, Parameters!ProjectNum.Label, 1)
. This would entered
in the Value area for the parameter. The Label would be =Code.sortObject(Parameters!ProjectNum.Value, Parameters!ProjectNum.Label, 2)
.
Where ProjectNum is populated with the dataset from the RSSharePointList dataset above.
Then you just build your new parameter based upon this one with the Code.sortobject
calls above. Hide this parameter and you are good to go.