.NET Function to produce link to UNC path

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:

Report Properties

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.

One thought on “.NET Function to produce link to UNC path

Leave a Reply

Your email address will not be published.