Report Viewer: Create a Hyperlink to SharePoint

Posted on by 17 comments

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.

CropperCapture[90]

  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.
    CropperCapture[91]
  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.
    CropperCapture[92]
  5. Click on Fields (Project Tasks is the name of my SharePoint list).  Double click on Priority.
    CropperCapture[94]
  6. Just type the text in, for the value = (1) High, and then click the Constants category on the left.
    CropperCapture[95]
  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:
    CropperCapture[97]
  8. Click OK, click OK.  Then, in Report Builder, click the Run button to preview it.
    CropperCapture[98]
  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.
    CropperCapture[99]
  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.
    CropperCapture[100]
  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.
    CropperCapture[101]
  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.
    CropperCapture[102]
  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:

CropperCapture[103]

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.

tagged with , , , ,

17 comments

  • Tom Resing
    August 3, 2012 8:25 am

    You make the report viewer web part sound so good that I can picture recommending it over the data view web part even without the 2013 changes! Nice walkthrough. The visuals really help.

    Reply
  • Marc D Anderson
    August 3, 2012 1:12 pm

    Laura:

    I’ve always found the reports that SSRS emits to be incredibly bloated and slow in the browser. What’s your take on that?

    Also, do you think that this is a good “end user” replacement for the DVWP? Would they even have access to these tools?

    M.

    Reply
  • Ian Morrish
    August 4, 2012 12:34 am

    I think the 3 of us are going to miss the DVWP the most. Just can’t believe they ripped this out….

    Reply
  • Mike Walsh
    August 4, 2012 10:00 am

    One more step in making SharePoint so complicated that it is impossible for anything but a team of specialists to handle.

    DVWP/DVFP will be sadly missed. It makes you wonder if the SPD team have the best interests of the SP product at heart following as it does their SP 2010 can only be used with SP 2010 sites misstep.

    One stupidity per SPD version?

    Reply
  • Laura
    August 6, 2012 1:49 pm

    Marc,
    Yes, Report Builder 3.0 is an end user tool.

    Ian and Mike,
    Just keep encouraging people to chime in here, on this thread that Marc started. Hopefully Microsoft will read it. http://social.technet.microsoft.com/Forums/en-US/sharepointitpropreview/thread/8f8e2cb3-a90f-4653-9d22-050f9f0d8612

    Reply
  • Matt
    August 7, 2012 11:40 am

    Hi Laura,

    Great Information! As usual… I think you would be the perfect person to answer this question…

    Is it possible, in an SSRS report, to have the parameter dropdown option fed by the SharePoint User Profile? Much like InfoPath has the option to connect to the User Profile and utilize that data in forms, can that work in an SSRS report as well?

    I have an SSRS report that I’m trying to pass data to from another web part and the infopath form webpart keeps crashing. I thought that if the report could be fed teh information from the UP, it’s one less item hanging around on the page.

    Thanks!

    Matt

    Reply
  • Laura
    August 23, 2012 2:34 pm

    Matt,
    Yes, you can deploy the report to sharepoint, and put it on the page in a report viewer web part. Then, put a current user filter web part on the same page, and use a web part connection to pass the parameter to the report.

    Reply
  • Fran
    February 15, 2013 12:00 pm

    First off Laura I think you are great! BUT I cannot get the link to work. It keeps opening up the form but not the correct data, (no data in form). I have customized the form in Infopath, does that make a difference in the URL or what am I doing wrong? I am appending the link you gave above to the base of the list link.

    Thank you!

    Reply
  • Julia
    March 5, 2013 7:43 am

    Great Post
    I have a problem though with displaying Hyerpling ( hyperlink/Item column type frm sharepoin tlist) into an ssrs report,
    I only want to display the descripton, but end uo all the time with the whole URL+Descriptpon
    any workaround for that ?

    Thanks !

    Reply
  • Laura
    June 13, 2013 10:58 am

    Fran,
    All I can think is that you’re not sending the ID correctly. Can you try hard coding an ID into the URL and see if that works, and then try using the ID field and see if that works?

    Julia,
    Sorry, I don’t understand your question.

    Reply
  • Sanka
    November 12, 2013 11:30 pm

    Laura,

    This is not related to the above but SSRS ,

    Is there a way to access folders of documents libraries using report designer because reports do not access folder level items. 🙂

    Thanks

    Reply
  • Tim
    January 28, 2014 1:35 pm

    Laura,

    Long time follower, first time responder.
    Thank you so much of this! I’ve been frustrated with links in Report Builder, but I had one aditional requirement to open links in a new window/tab so a friend here helped me to modify your link to this:
    =”javascript:window.open(”http://team.contoso.com/Lists/ProjectTasks/dispform.aspx?ID=” & Fields!ID.Value & “‘, ‘_blank’); void(0)”

    Note. the “void(0)” is necessary or the original page will refersh to “[object]”, refer to:
    http://support.microsoft.com/kb/257321

    Reply
  • Laura
    February 3, 2014 2:19 pm

    Sanka,
    No, I have tried that before and never found a way to do it.

    Tim,
    Thanks for the tip!

    Reply
  • Matt
    August 26, 2014 11:42 am

    Laura,

    Thanks for the help, this was just what I needed. However, as you surely already know, due to the existance of the javascript function in the hyperlink, this solution will not work if the user exports the report to say PDF, or MS word and then attempts to click on the link from there because those tools of course don’t know how to interpret the javascript function as IE does.

    Any ideas on how to get the links to still function after exporting the report to word or PDF? Thank you in advance!

    Reply
  • Mohan
    September 16, 2014 6:41 am

    Thank you so much Laura

    for the hyper link parameter issue its display in browse twise.
    i am strugling for the same issue. but after read your post solved my issue.

    thanks

    Reply
  • Laura
    March 12, 2015 10:25 am

    Awesome, I’m so glad to help!

    Reply
  • Karena
    August 13, 2015 11:14 am

    This is brilliant, thanks! One question: how would I change the link to force it to open in a new window?

    I have another link I got that to work on with =”javascript:void(window.open(‘”+ “http://xxxx/” + Fields!Field.Value + “‘,’_blank’))” and that works.

    But I’ve been unable to combine that with your javascript and get it to work. Many thanks!

    Reply

Leave a Reply