Dynamic image from a URI in Webi

Dynamically displaying binary images from a database in Webi

By Josh, April 18, 2014

I recently stumbled my way into this trick while helping another developer with a personnel reporting solution.  I found that it is possible to display images that are stored in a binary data column (such as an Oracle BLOB field or SQL Server Image or VARBINARY field) in a Webi block as long as you are using the HTML client.

Because of some previous experience that I have had in application and web development, I already knew that it was possible for web browsers to display an image if it is represented as a base64 string, so the idea came to my mind that if Webi doesn’t prevent the browser from displaying a base64 image, and we can somehow convert that binary data to the base64 string, then this should actually be possible.  Surely when you use the Image URL feature Webi is just rendering and delivering an IMG element and doing something like setting its SRC attribute, right?  That could be confirmed using an HTTP Debugger, but I figured I would take the lazy approach and give it a shot without going through all of the trouble. 🙂

I started by testing Webi’s ability to display a simple image by creating a variable that held an image URI string.  I used an online converter to convert a simple avatar image to a base64 string, prepended the string with the data type declarations, and saved the text into a variable.  In this case I was dealing with a GIF image, so I prepended the base64 string with this text:

data:image/gif;base64,

And here is what the final formula looked like in XI 3.1

Creating the Image URI variable with Webi in XI 3.1

Creating the Image URI variable with Webi in XI 3.1

The text in the formula keeps going, but basically just finish it up at the end with another double quote.  From there you can drop the variable into a block somewhere (table, blank cell, etc) and set the content to be read as Image URL

Set the "Read cell content as" property to "Image URL"

Set the “Read cell content as” property to “Image URL”

Save the report and then reopen it with the HTML client and…. magic!!

The image appears when you are viewing with the HTML client

The image appears when you are viewing with the HTML client

Unfortunately, just as with other dynamic images from a URL in Webi, the image will disappear when you export the report to PDF or Excel.

No image is exported to PDF from XI 3.1

No image is exported to PDF from XI 3.1

I also wanted to try out this little experiment with BI4 as well.  Once again I created a variable, pasted the same Image URI, and dropped it into a block.

Setting the image variable in BI4

Setting the image variable in BI4

And make sure to set the content to display as an Image URL

Setting the content display type in BI4

Setting the content display type in BI4

It works there, too!

So the next step to make this useful would be to use a value that you are getting from the database instead of pasting in a constant string every time.  To do so, you would need to convert the image from it’s binary format into a base64 string.  This should be relatively easy to do with Microsoft SQL Server, Oracle, and possibly other databases as well.

For Microsoft SQL Server, the above referenced blog posting suggests to use XQuery to help cast a VARBINARY column as a different data type, like this

select top (10) cast(N'' as xml).value('xs:base64Binary(xs:hexBinary(sql:column("qs.sql_handle")))', 'varchar(512)') as sql_handle_base64 from sys.dm_exec_query_stats as qs;

So if  you can push this same type of behavior to the database (be it Oracle, SQL Server, or something else), then you should be able to pull back a base64 string instead of the binary column itself.  Once you get the syntax correct, it should be easy enough to just stuff that into a Universe object and use it on your reports.

In our case, we were dealing with an Image type in SQL Server (an older binary type that is scheduled to be sunset in favor of  Varbinary), so had to convert the column from Image to Varbinary first.  One way to do this is by simply wrapping one query that converts the data to Varbinary with the one that does the actual binary to base64 string conversion by way of XQuery.

select b.*, CAST(N'' AS xml).value('xs:base64Binary(sql:column("binimg"))', 'varchar(max)') as base64img from (select a.*, CONVERT(varbinary(max), MyImageField) as binimg from dbo.MyImageTable as a) as b;

Of course you can’t really do that in isolation for a single Universe object, so if you are in this boat, you could either create a derived table or a view in the database to do the initial conversion (or both), just making sure that you are getting the base64 string in your final Universe object.  Also don’t forget to put your Data URI declaration in front of your base64 string as part of your Universe object!  You will need to make sure to set the type to whatever type of images you are dealing with (jpeg, gif, png, etc).

 

Dynamically Resizing the Images

Because Webi’s layout is based on the blocks concept, this will really only work well if all of your images are (or should be) roughly the same size as each other.  If the images are all different sizes but you want to try and fit or resize them into a common size, it should also be possible to have the database server dynamically resize them for you as well.

I haven’t tested this yet, but your DBA can thank me for this one later.  😉

I believe it should be possible in SQL Server by writing your own .NET assembly to use GDI or some other Image class to resize the images for you.  There is a great example of walking through this on Stack Overflow.  I assume that something very similar to this should be possible with Oracle if you write something in Java as well.

Cool stuff!

One Comment

  1. Mp says:

    Hello Joshua,

    Nice Post ..really Liked …!!
    Keep the Great Work…

What do you think?

Leave a Reply

Your email address will not be published. Required fields are marked *