Transcript SharePoint911 Powerpoint Template (gray and orange)

Who Am I?
• Tavis Lovell
•
Rackspace BI consultant on business solutions team
•
Based out of Cincinnati, OH (Technically, I live in KY)
• 9 years consulting
• Certifications/books/speaking
• Contributing Author: Professional SharePoint 2013
Administration
• MCITP – SQL Server BI Developer
• MCTS – Database Development
• SPTechCon / SharePoint Cincinnati / Local User Groups
• Blog
• http://www.tavislovell.com
• Twitter
• @TavisLovell
Other Stuff
Advanced Reporting
Scenarios with
SharePoint Lists
Tavis Lovell
Senior SharePoint BI Consultant
Rackspace
What are we talking about today?
1. Getting distinct values from a SharePoint list with SSRS so
we can populate our parameter dropdowns.
2. Reporting against Libraries that have folders in them with
SSRS
3. Passing filters to listdata.svc (Excel)
Getting distinct values from a SharePoint list with SSRS
Dedup code:
Public Shared Function RemoveDups(ByVal items As String) As String
Dim noDups As New System.Collections.ArrayList()
Dim SpValues
SpValues = Split(items ,",")
For i As Integer=0 To Ubound(SpValues)
If Not noDups.Contains(SpValues(i).Trim()) Then
noDups.Add(SpValues(i).Trim())
End If
Next
Dim uniqueValues As String() = New String(noDups.Count-1){}
noDups.CopyTo(uniqueValues)
Return String.Join(",", uniqueValues)
End Function
Getting distinct values from a SharePoint list with SSRS
To call the RemoveDups code:
=Split(Code.RemoveDups(JOIN(Parameters!YourParameterName.Value, “,”)), “,”)
Working with folders in SharePoint libraries
Full blog post here:
•
http://tavislovell.com/using-ssrs-with-sharepoint-library-folders/
Passing Filters to Listdata.svc
Example with 1 filter:
http://portal.contoso.com/SPCincy2015/_vti_bin/listdata.svc/TroubleTicket/?$filter=
CategoryValue eq 'POS System'
Example with 2 filters:
http://portal.contoso.com/SPCincy2015/_vti_bin/listdata.svc/TroubleTicket/?$filter=
CategoryValue eq 'POS System' or CategoryValue eq 'Mobile Devices'
Passing Filters to Listdata.svc
References:
•
http://leowu70.blogspot.com/2010/11/rest-using-listdatasvc.html
•
http://blog.itayasaservice.com/2012/08/15/tips-for-working-with-listdata-svc/
•
http://docs.oasis-open.org/odata/odata/v4.0/errata02/os/complete/part2-urlconventions/odata-v4.0-errata02-os-part2-url-conventionscomplete.html#_Toc406398092
•
https://msdn.microsoft.com/en-us/library/cc907912.aspx
Where can I download this sweet PowerPoint?
www.tavislovell.com/PowerHourAdvancedReporting.
pptx
SSRS Online Class
Next Class June 17th
Details here: http://sharepoint.rackspace.com/ssrs-training