I would like to create an HTML block that is formatted as a bulleted list and uses a data table as the data source for the bullets.  Is this possible?

With help from @Tony Agent and Balaji Soundararajan, I've come up with the following HTML syntax:

<div class="kpi-bar-widget-item" style="height:200px;width:865px;padding:5px 10px 5px 10px;border-radius: 6px 6px 6px 6px;border:1px solid gray;background-color:white;"><span class="kpi-bar-text1">OpEx Budget Commentary</span> <br/><span style="display:block;margin-top:5px;"><ul><li><%=Commentary%></li></ul></span></div>

The idea is for this HTML block to read the "CIO Commentary-OpEx" table, which has one only one field called "Commentary" and three records in it, and display those records as bullet points.  In my configuration window, I've pulled in the Commentary field from the aforementioned table as a Value, and the result is one bullet point that reads "{Various}" which leads me to believe that it's trying to group the data instead of listing the records out individually.

I want it to look like this:

But instead it looks like this:

Hi James,

Unfortunately this will not work from my experience, as each LI needs to be wrapped with an open and close tag, so for example <LI>Bullet 1</LI><LI>Bullet 2</LI> etc...

One idea, have each comment its own column in the data set with only 1 row for each value. As an example:

<div class="kpi-bar-widget-item" style="height:200px;width:865px;padding:5px 10px 5px 10px;border-radius: 6px 6px 6px 6px;border:1px solid gray;background-color:white;"><span class="kpi-bar-text1">OpEx Budget Commentary</span> <br/><span style="display:block;margin-top:5px;"><ul><li><%=Commentary 1%></li><li><%=Commentary 2%></li><li><%=Commentary 3%></li></ul></span></div>

This then will report the only unique value for each commentary column:

My data set looks like this:

I've always just put the bulleted text in the data table instead of having a separate row for each bullet. Is that possible in your case? If not, you'd have to do some kind of transform that concatenates the rows with some delimiter then do a substitution in the report.

Hey James,

It actually is possible to do this, though the method is definitely not intuitive!

1) Take your HTML component and separate our your dynamic text portion to make things a bit easier to read

<div class="kpi-bar-widget-item" style="height:200px;width:865px;padding:5px 10px 5px 10px;border-radius: 6px 6px 6px 6px;border:1px solid gray;background-color:white;"><span class="kpi-bar-text1">OpEx Budget Commentary</span> <br/><span style="display:block;margin-top:5px;"><ul><li>

<%=Commentary%>

</li></ul></span></div>

2) Change the "=Commentary" to "=UniqueValues(CIO Commentary-OpEx:Commentary)"

<div class="kpi-bar-widget-item" style="height:200px;width:865px;padding:5px 10px 5px 10px;border-radius: 6px 6px 6px 6px;border:1px solid gray;background-color:white;"><span class="kpi-bar-text1">OpEx Budget Commentary</span> <br/><span style="display:block;margin-top:5px;"><ul><li>

<%=UniqueValues(CIO Commentary-OpEx:Commentary)%>

</li></ul></span></div>

This will cause the HTML to return all values from the CIO Commentary-OpEx Commentary field, although they will all be surrounded by quotations, and concatenated together.

3) Substitute suffix quotations for new bullets

Do this by wrapping your UniqueValues() function with a Substitute function as follows:

<div class="kpi-bar-widget-item" style="height:200px;width:865px;padding:5px 10px 5px 10px;border-radius: 6px 6px 6px 6px;border:1px solid gray;background-color:white;"><span class="kpi-bar-text1">OpEx Budget Commentary</span> <br/><span style="display:block;margin-top:5px;"><ul><li>

<%=Substitute(UniqueValues(CIO Commentary-OpEx:Commentary),""",""","<li>")%>

</li></ul></span></div>

What this will do is replace any instance of quotation mark and comma (",) with a new bullet line (<li>)

4) Substitute beginning and ending quotation marks with blanks

Do this by wrapping our formula with one final Substitute function as follows:

<div class="kpi-bar-widget-item" style="height:200px;width:865px;padding:5px 10px 5px 10px;border-radius: 6px 6px 6px 6px;border:1px solid gray;background-color:white;"><span class="kpi-bar-text1">OpEx Budget Commentary</span> <br/><span style="display:block;margin-top:5px;"><ul><li>

<%=Substitute(Substitute(UniqueValues(CIO Commentary-OpEx:Commentary),""",""","<li>"),"""","")%>

</li></ul></span></div>

...And with that, you should be good to go!

There are however, some technical considerations to be aware of:

This is not a 100% HTML compliant solution

When you substitute <li> in step 3, modern browsers will be smart enough to create new bullets, but you never actually close the tag with a corresponding </li>. This should be fine in most cases, but some older versions of IE may handle this in unexpected ways--test this on your company's own supported browsers to determine if it is an issue.

This is will not work if your comment rows contain quotations or commas

This solution does two different substitute functions based on commas and quotations, if your comment fields contain their own commas and quotations then the results will not be as expected.

Hope this helps!

- Michael Verkruyse

Thanks, Michael.  This works great!

I have a follow up question on this one, @Michael Verkruyse.  The HTML block seems to be bringing in the records from my comments table and sorting them in alphabetical order.  I would prefer them to be displayed in the order that they are listed in the table because the person who enters the comments is going to list them in order of importance.  Short of starting each comment with a number to ensure it sorts correctly, is there a better way to do this?

Hey James,

Since the HTML block isn't a table, it's limited in its ability to sort or leverage the !SORT functionality. One additional difficulty is that the UniqueValues() function returns the value as a string/label, so the ascii order of characters takes precedence; that means that even if we add a number to the front of our comments, we'll run into the following issue:

Example Comments:

1 - This is the first comment!

2 - This is the second comment!

...

25 - This is the twenty-fifth comment!

UniqueValues() Return Order:

1 - This is the first comment!

10 - This is the tenth comment!

11 - This is the eleventh comment!

12 - This is the twelfth comment!

...

19 - This is the nineteenth comment!

2 - This is the second comment!

20 - This is the twentieth comment!

21 - This is the twenty-first comment!

...

There are some ways around this, but my suggestion would likely be based on whether the report contains any other items, or if it's just this HTML object?

That makes sense.  For my use case, the HTML block will probably never have more than 4 or 5 bullets, so while I completely understand the ascii sorting issue, I think we could use it here and probably never run into any problem.  If I wanted to use this concept elsewhere, though, it would be something to keep in mind.

For this use case, the report does not contain any other items other than this HTML block.

This was extremely helpfull, thank you!  I only needed to enter the <br/> to space out the lines a little better.

This formula works well with the exception that it alphabetically sort the lines in the data set.  Is there a HTML to fix this?  I tried to pull each line individually as well, which worked except when 1 month has 6 lines and the next has 4, then I receive "Null" for the HTM line. @Jonathan Strauss

@Tony Agent

Audrey, I came here to see if someone has answer to the "Null" issue :-( It has a "-" now....

Shaun,

It has to have the “-“, without it you’ll see the word BLANK.

Audrey

Audrey Mitch

Solutions Architect

Mobile | Text: 585.217.6263

audrey.mitch@harpdata.com<mailto:audrey.mitch@harpdata.com> |www.harpdata.com

I know! I came here to see if there is an alternate :-(

GL

Audrey Mitch

Solutions Architect

Mobile | Text: 585.217.6263

audrey.mitch@harpdata.com<mailto:audrey.mitch@harpdata.com> |www.harpdata.com

Since you're using an HTML component, I believe you can just test the result using an IF statement such as the below:

<%=IF(tablename.column=""," ","<li>"&tablename.column&"</li>")%>