Report Viewer: Create a Hyperlink to SharePoint

If you’re not aware yet, the upcoming version of SharePoint (2013) does not include a design view in SharePoint Designer anymore, which means no more no-code data view web parts.

SSRS reports are a great replacement for the data view web part in many cases.  In this post, I’ll show you how to create a conditionally formatted list of tasks using Report Builder 3.0, and link each task back to that SharePoint list item.  This will be displayed in the report viewer web part on the homepage.  Also note that you can create reports on one site that show data that’s in another site!

For a little background on what SSRS is and some basic steps on creating reports in Report Builder 3.0, refer to my blog post: Easy Reporting off of SharePoint Data, which also includes a video presentation and PowerPoint.

So, once you’ve gotten to the point where you’ve got your report created as a table, from your list of SharePoint tasks, here are the steps to create some conditional formatting and add that hyperlink.  My table is a task list, and I’m showing the Title, Priority, Assigned To, Due Date, and Task Status.

1. My first goal is to show the priority as red and bold if it’s high priority.  Remember that in task lists, the value for high priority is (1) High.  Right click on the Priority cell (the white one, not the table header).  Choose Text Box Properties.
2. Click Font on the left side, and this is what you’ll see.  Notice that each option has a little function button next to it.  This function button is what can be used in order to create conditions.  Also, if you click around the other areas such as border and fill, you’ll notice many other options for formatting.
   
3. Click the Function (fx) button next to the Color box.
4. Clear out what’s already in the expression box.  We’re going to create an IF statement, which is a similar concept to what you’ve seen in SharePoint calculated columns, in Excel, and in Access.
   For those of you who are advanced, here is the end result expression:
   =IIf(Fields!Priority.Value = “(1) High”,”Red”,”Black”)
   To go though the actual steps of creating the function, continue…
   You can find the IIF statement (and syntax) in the Program Flow group under Common Functions.
   
5. Click on Fields (Project Tasks is the name of my SharePoint list).  Double click on Priority.
   
6. Just type the text in, for the value = (1) High, and then click the Constants category on the left.
   
7. Then, since we want high to be red, click the red color on the right, which will put the word “Red” in the formula.  Otherwise, if it’s not red, we just want it to be black.  Here’s the full syntax:
   
8. Click OK, click OK.  Then, in Report Builder, click the Run button to preview it.
   
9. Click the Design button at the top.  The next thing we’ll do is make the status field have a yellow background if it’s not completed yet.  Right click on the Status cell (not the column heading), and choose Text box properties.
10. Click Fill on the left.  If we wanted to show an actual image according to different conditions, this is where we would do it, but we’re just going to do the color.  Click the Function (fx) button next to the Fill Color box.
    
11. Clear out the expression box and put in the following:
    =IIf(Fields!Task_Status.Value <> “Completed”,”Yellow”,”Transparent”)
    
12. Basically, if the task status is not equal to <> the word completed, then it’s yellow, otherwise, it’s transparent (no color).  Click OK, click OK. Run.
    
13. Okay, now what good is a list of important tasks, if you don’t have a way to actually open them up?  Time to make the title into a hyperlink.  Back in Design view, right click on the Title field (the white, not the green header), and choose Text Box Properties.
14. Click Action on the left, and choose Go to URL.
    
15. Click the Function (fx) button next to the Select URL box.  You’ll need to know the URL to get to the display form for a specific task.  Mine is:
    http://team.contoso.com/Lists/ProjectTasks/dispform.aspx?ID=__
    (to read more about how hyperlinks are structured in SharePoint, read my post here)
    The __ at the end is what needs to display the ID of whatever item I click on.
16. It’s not as easy as just concatenating together your URL and your item’s ID.  There’s a bug.  If you just do that, then your query string (ID) will end up showing twice at the end of the url when using this report in the report viewer web part, which will not allow your item to open property.  You have do to this extra funkiness with JavaScript.
    =”javascript:window.navigate(‘http://team.contoso.com/Lists/ProjectTasks/dispform.aspx?ID=&#8221; & Fields!ID.value & “‘);”
    
17. Click OK, click OK.  Notice that your title still doesn’t look like a hyperlink.  Well, it is, but it’s not going to be obvious to anyone.
    
18. With your Title cell still selected, just click the little underline button in the ribbon, just like you would in MS Word.  Then, use the font color next to it, to change the font to blue.  Click Run.  Click Save, save it to your library where you keep reports, and close Report Builder.
19. Go to your site’s home page, or any other page, really, in the browser, and Edit Page.  Click to add/insert a web part.  In the SQL Server Reporting category, insert the SQL Server Reporting Services Report Viewer.
20. Open the web part toolpane.  In the Report box, navigate to your report in the library you just saved it in.  Click OK.  Stop editing the page and/or save it.

This is what the final result looks like in the browser:

Note that there are many more options in the web part settings, which allow you to control what shows on the toolbar, etc.  You can also change the setting that determines what window that links will come up in, so you can use _blank if you want the task to open up in a new window.  This is even pretty, printable, and exportable.