The following code is not mine, but from personnel within our company who really helped me out. Thanks Dan!
We have a few reports built within SQL Reporting Services 2005. One of these reports lists financial information for our projects. A request was made to create a link between the project on the report, and the project’s folder within our DFS share.
This is the custom code used on the report to produce those results. Depending on how folders are stored, it could be very useful for others as well.
For this to make sense, here’s a quick primer on how we store our files:
Each project has a 5 digit unique identifier within our DFS. It looks like this: “34321 deptcode short description”. These are organized by the major ‘ten thousandth’ value. For example:
Jobs >
30000
31000
32000
33000
Now for the code. To begin, you need to add the custom code to your report:
Use the “Code” tab, and paste the code in. Here’s the code I used, with comments:
'Start the function, with the value passed from the reportserver as jobnum Public Function Extract_jobs_path(ByVal jobnum As String) As String 'Find the first two digits of the folder name by taking the last 5 digits of the record and keeping first 2 Dim Prefix_job As String = Strings.mid(jobnum,5,2) Dim Result As String = "" 'Make Director1 equal to the root folder of the job number (ie 33000), using the first two digits grabbed Dim Director1 As New System.IO.DirectoryInfo(\\domain.ca\files\Jobs\ & Prefix_job & "000") If Director1.Exists = True Then 'Grab the first 5 characters of the job number Dim job_number As String =strings.mid(jobnum,5,5) 'Set Folder1 as the root folder, same as Director1 Dim Folder1 As String() = System.IO.Directory.GetDirectories(\\domain.ca\files\Jobs\ & Prefix_job & "000") 'For every subfolder of the root, search the first 5 digits and see if they match job_number For Each subFolder As String In Folder1 If Strings.Mid(subFolder, 27, 5) = job_number Then Result = subFolder Exit For End If Next End If 'If there was a match, switch around the slashes, add in the file:/// to build the link, 'and return the value If Result <> "" Then Result = Replace(Result, "\", "/") Dim Poz1 As Integer = InStr(Result, "domain.ca") If Poz1 > 0 Then Return "file:///" & Result End If End If End Function
Now all you need to do is call the code within a spot in your report. We have a table with a specific cell that has the code inserted into the “Navigation” properties of that cell, using this:
=Code.Extract_jobs_path(Fields!ProjectID.Value)
Clicking on that cell automatically opens the UNC path in Windows Explorer.