Conditionally enable Hyperlink for a field in BIRT Report

MAX092012
edited February 11, 2022 in Analytics #1

I have a BIRT report which has 2 dataset and both of them have a common field displayed. When I click on this common field in first dataset which is a hyperlink then it should automatically move to the second dataset and show the same common field . I'm able to get this working however id the value is not present in the second dataset then an exception is thrown as "the bookmark {0} is not valid" . Hence I would like to understand how to get this hyperlink enabled conditionally, I have attached the report. for reference and here 7 is there in first dataset but if its not there in second dataset then hyperlink should not come for 7 in first dataset.

Comments

  • Since bookmark links don't have any built in checking like that, you'd need to verify it yourself. Doing so won't be a trivial endeavor, since data sets run in the order they are needed. If you're running Analytics Designer 16.4 or later, we have a function to run a data set that you could use to execute the second data set, and parse it to verify if a value exists, and return a null if not.

    If you're running open source or a Designer before 16.4, first you'll need a way to run the second data set first. The easiest way would be to add a data element to the top of your report that's bound to the second data set. You can hide this element with a visibility rule. Second, you'll need a way to access the data when constructing the bookmark, one way is to grab data onFetch of the second data set (of which now executes first) and load it into an array for you to parse through.

    Warning No formatter is installed for the format ipb
  • I created an example based on the shared report before seeing Chad's post (my report is attached). As Chad mentioned, the difficult part is knowing if the link should be enabled. I added a table to the top of the report to get the list of valid links. The full set ofl steps is listed on the report.

    Warning No formatter is installed for the format ipb
  • Hi Jeff,
    Thanks for your response!! I tested the way you had explained however I have few questions. In the OFFICECODE row you had mentioned below conditions,
    BLUE
    var codes = row._outer["a1"].split(">");
    if (codes.indexOf(row["OFFICECODE"]) >= 0){
    false
    } else {
    true
    }
    RED
    var codes = row._outer["a1"].split(">");
    if (codes.indexOf(row["OFFICECODE"]) >= 0){
    true
    } else {
    false
    }
    when I use the above conditions then it always gives the output in RED color when for some records it should be in BLUE color. On investigating further codes.indexOf(row["OFFICECODE"]) returns -1 hence I think in both these conditions it satisfies the ELSE part and may be the RED color condition is executed in the last hence it displays everything in RED ?

  • jfranken
    edited March 12, 2020 #5

    @MAX092012 said:
    Hi Jeff,
    Thanks for your response!! I tested the way you had explained however I have few questions. In the OFFICECODE row you had mentioned below conditions,
    BLUE
    var codes = row._outer["a1"].split(">");
    if (codes.indexOf(row["OFFICECODE"]) >= 0){
    false
    } else {
    true
    }
    RED
    var codes = row._outer["a1"].split(">");
    if (codes.indexOf(row["OFFICECODE"]) >= 0){
    true
    } else {
    false
    }
    when I use the above conditions then it always gives the output in RED color when for some records it should be in BLUE color. On investigating further codes.indexOf(row["OFFICECODE"]) returns -1 hence I think in both these conditions it satisfies the ELSE part and may be the RED color condition is executed in the last hence it displays everything in RED ?

    The code converts the list of office codes to an array, then checks to see if the current row value is in the array. The first thing to check is the list of codes. You can make the list visible by unhiding the row in the outer table just for testing:

    You should see the list at the top of your report (I changed the background color yellow to make it easier to spot):

    Warning No formatter is installed for the format ipb
  • Hi Jeff,
    Thanks for your reply! How did you get 1>2>3>4>5 ? When I try it out in my report then its coming as 12345 . I checked the report which you had shared earlier however I'm unable to figure how this ">" is coming up . Please advise.

  • Double click the aggregation and you will see where the separator is set. Any separator can be used (including mutliple characters). Choose a separator that will never appear in the data. Use the same separator in the visibility expressions.

    Warning No formatter is installed for the format ipb
  • Hi Jeff,
    I gave the separator as you have mentioned and the concatenate appears as below
    But should I remove this separator or keep it in the concatenate function? If I keep it then in the output both BLUE and RED appears for all the records which is incorrect. If I remove the separator then only RED appears though for some records BLUE should appear. I have attached the report for your reference.

  • Your report looks correct. If the visibility code was running correctly, it would be impossible for both elements to display on the same row because the visibility logic is reversed between the two. I'm not sure why the visibility rules are not working. I have encountered a couple of unusual cases in the past, but the code you are using is code I wrote and tested. It worked correctly for me.

    I'll keep looking at your report. In the meantime, I modified your report reducing the visibility expressions to one line of code. Please give it a try and see if the change makes a difference.

    Warning No formatter is installed for the format ipb
  • MAX092012
    edited March 13, 2020 #10

    Hi Jeff,
    I tried your updated condition and it brings the same result ... I tried to display the condition and for each location it prints both True and False . If you also look at the location ( circled in BLUE) its present in level 2 however the same location appears in RED in level 1 . What I also do not understand is how would the concatenate compare whether the location is present between level 1 and level 2 ? Please advise.

  • Try displaying the aggregation value (a1) in each row. If the location code for the row matches one of the concatenated items in a1, you should get false above true and only the blue element with the link should display. If the location code is not in the list, you should get true above false and the red element should display.

    Note that when the visibility rule returns true, the element is hidden. If the visibility rule returns false, the element is displayed. The first line in your screenshot is "true" (corresponding to the blue element), The second line is "false" (corresponding to the red element). So blue should be hidden and red should display which is what is happening.

    Warning No formatter is installed for the format ipb
  • Hi Jeff,
    Thanks for the reply!! I tried to print the aggregation value (row_outer["a1"]) and it remains same for all the locations which is incorrect, also only half location is printed, I'm not sure why its not taking the whole location until the separator.

    Also its picking only the first location and not moving to the next location , is it because the aggregation value and the location are displayed in the header?

  • The aggregation element should display all of the location values on every row. Note that normally you wouldn't want to display the same long list of values on every row. I suggested adding the aggregation because I thought it would be helpful to debug the issue. My eyesight isn't great, but it looks like the aggregation value (small text highlighted in yellow) is getting truncated. Try clicking the element and going to Advanced properties. Change the "Overflow" property so that you can see the full list of location values when the report is run. All values in blue should match a value in the list. Values in red should not have an exact match in the list. It might help to filter the data while testing so that you don't have to compare too many values..

    Warning No formatter is installed for the format ipb
  • Hi Jeff,
    I now understand and all the locations show first false and then True hence the location appears BLUE. But I need to determine the color (BLUE/RED) based on the value present in the second data set. What I do not understand is that a1 is concatenate of all the locations in the first dataset and then validate whether the location is present or not in dataset 1 , where is data validation of second data set? The BLUE color should come up only when the data is same between first and second dataset.

  • I don't understand your question. In the example that I posted, the red and blue elements get their data from the "Employee" data set. The visibility rules compare their values to the outer table data which gets its data from the "Office" data set. The blue element with the link only displays when the value from the "Employee" data set matches a value in the "Office" data set.

    The "a1" aggregation is data from a different data set than the blue and red elements. The point of embedding the table and referencing the outer "a1" aggregation is to be able to compare data from different data sets.

    Warning No formatter is installed for the format ipb
  • Hi Jeff,

    Thanks a lot for your time and help!! It works now..