0

Is it possible to have a dynamic link within a lookupset function in SSRS?

I am joining several values from one Dataset to another using the Lookupset & join functions. Would it be possible for each value returned to point to a different URL address based on the value selected?

For example, I am using this function to return Tasks as a 1 to many relationship with their relevant projects:

=Join(LookupSet(Fields!Title.Value, Fields!Related_Project.Value, Fields!Title.Value, "TeamTasks"), Environment.NewLine() & Environment.NewLine())

For the returning values, is it possible to link them to their respective URL?

When I set the the expression for Hyperlink as follows:

="https://example/" & Fields!ID.Value

or

= "https://example/" & Lookup(Fields!Title.Value, Fields!Related_Project.Value, Fields!ID.Value, "TeamTasks")

It simply works for the first returned value and is not dynamic within the Lookupset function.

An example of the table would be: 

Project Title     Task
_____________     _____
Project 1         Task1
                  Task2
                  Task3
_____________     _____
Project 2         Task 1

Ideally, for each separate Task clicked, it will link you to the respective URL. The Task column is populated via the Lookupset function within the same Row as Project.

Is there any way to fix this?

Thanks in advance!

Frank - NewToThis
  • 65
  • 10
  • If the URL value is required for each row, why you are using lookupset instead of lookup? – p2k Sep 24 '16 at 12:23
  • Because I need to return multiple values (Tasks) for each Project, separated by a break line. Using the Lookup function would only return one value? Thanks! – Frank - NewToThis Sep 26 '16 at 14:17
  • Can you share the sample data of your 2 datasets? – p2k Sep 26 '16 at 14:32
  • I have edited my original post. Does this provide you with enough information? Thanks!! – Frank - NewToThis Sep 26 '16 at 14:45
  • Does `"https://example/" & Fields!ID.Value` give your URL structure? Does `=Join(LookupSet(Fields!Title.Value, Fields!Related_Project.Value, Fields!Title.Value, "TeamTasks"), Environment.NewLine() & Environment.NewLine()) ` (your first code block) give you the list of tasks correctly without URL? Are you just trying to turn your original list of Tasks into a list of URLs? – SMM Sep 26 '16 at 15:21
  • Currently I use: ="https://evshare/sites/it/distributionsystems/Lists/TeamTasks/Item/displayifs.aspx?List=4fbcabaf-0619-464f-9e57-2d10c54b5d4f&ID=" & Lookup(Fields!Title.Value, Fields!Related_Project.Value, Fields!ID.Value, "TeamTasks") when setting the expression for Hyperlink. It links correctly for the first iteration of Tasks, but does not for the second iteration on. And yes that lookupset function returns the correct tasks. Ideally, I would like the tasks to be displayed as the text that they are, and be clickable, linking to their respective page. Thanks again! – Frank - NewToThis Sep 26 '16 at 15:42

2 Answers2

3

One TextBox can points to only one URL unless you apply some trick.

If your output structure is ready as shown your sample. Follow the below steps:

  1. Right Click on TextBox>Placeholder properties

enter image description here

  1. Select option HTML
  2. Put the expression, similar as below:

enter image description here

I have tested with my sample data. Make changes according to your value.

Update:

From your comment, replace the field Fields!URL.Value to Fields!Title.Value (this is the field https://example/Title1).

To give you the undestanding, What the below code is doing?

"<a href = " & Chr(34) & "https://example/" & Fields!Title.Value & Chr(34) & ">" & Fields!Title.Value & "</a>"

It will generate the HTML links for title values from your source data.

<a href = "https://example/Title1">Title1</a>
<a href = "https://example/Title2">Title2</a>
<a href = "https://example/Title3">Title3</a>

& Chr(34) & - This is to tackle the special character ".
p2k
  • 2,126
  • 4
  • 23
  • 39
  • Thanks for the reply! You'll have to excuse my lack of knowledge / experience but I get the error : `"The Value expression for the text box ‘Textbox171’ refers to the field ‘URL’. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope. Letters in the names of fields must use the correct case."` – Frank - NewToThis Sep 26 '16 at 19:30
  • The expression I am using in the Value field (under Placeholder Properties) is : `=Join(lookUpset(Fields!Title.Value, Fields!Related_Project.Value, "" & Fields!Title.Value & "", "TeamTasks"), "
    ")`     – Frank - NewToThis Sep 26 '16 at 19:34
  • Do you have field called `Fields!URL.Value` your code says `Fields!ID.Value` (in your question)? – p2k Sep 26 '16 at 19:51
  • Yes I have `Fields!ID.Value` but in the Set expression for Hyperlink, not in the Value Expression in the image above. And No I don't have/haven't used `Fields!URL.Value` besides trying what you said. Thanks again! – Frank - NewToThis Sep 27 '16 at 17:58
  • Is the error still there? If yes, please share the expression code and error. – p2k Sep 27 '16 at 18:22
  • Yes afraid so. Currently using this expression: "=Join(lookUpset(Fields!Title.Value, Fields!Related_Project.Value, "" & Fields!Title.Value & "", "TeamTasks"), "
    ")" . Should I be using the Fields!URL.Value field? What is its purpose? Should I be making any changes besides exactly what you said above?     – Frank - NewToThis Oct 10 '16 at 13:27
  • The error I am getting is : "The Value expression for the text box ‘Textbox171’ refers to the field ‘URL’. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope. Letters in the names of fields must use the correct case." Thank you! – Frank - NewToThis Oct 10 '16 at 13:28
  • The expression I was using prior to trying to use html was : "=Join(lookUpset(Fields!Title.Value, Fields!Related_Project.Value, Fields!Title.Value, "TeamTasks"), Environment.NewLine() & Environment.NewLine())" – Frank - NewToThis Oct 10 '16 at 13:29
  • Nopes, If you have selected the HTML option, you should be getting the Link buttons (blue text underlined). – p2k Oct 10 '16 at 17:59
  • Thank you for the help! I have used the expression `=Join(lookUpset(Fields!Title.Value, Fields!Related_Project.Value, "" & Fields!Title.Value & "", "TeamTasks"), "
    ")` and the link now works! Although It displays as underlined and blue (like a typical Hyperlink) is it possible to remove the style but keep the link? Thanks again!     – Frank - NewToThis Oct 10 '16 at 17:59
  • Glad! you got some result:) try with `
    ` or double `

    ` (html line break). It is all about HTML syntax. For Link, Have a look on syntax of HTML. you can customize the Link. http://stackoverflow.com/questions/10853881/how-to-remove-underline-from-a-link-in-html     – p2k Oct 10 '16 at 18:08
  • I hate to be a pest but I've added in text-decoration: none; into the part ` – Frank - NewToThis Oct 10 '16 at 18:21
  • am afraid, if the tag `style="text-decoration:none"` is supported here. Limited set of CSS (style) is supported in SSRS. – p2k Oct 10 '16 at 18:59
0

Adding the prefix in your join criteria will make sure it appears in each separation, and then add one at the beginning. Try to modify your join like this (free-coded).

="https://example/" & Join(LookupSet(Fields!Title.Value, Fields!Related_Project.Value, Fields!Title.Value, "TeamTasks"), Environment.NewLine() & Environment.NewLine() & "https://example/")

SMM
  • 2,225
  • 1
  • 19
  • 30
  • I'm not sure what you mean here. If I simply add the URL to before and after my expression, it just displays the URL I linked in the textbox, not the correct value, and not linking to the correct URL. Thanks ! – Frank - NewToThis Sep 26 '16 at 14:30