Solution: BI Publisher QR Codes
I saw a post on StackOverflow asking How to use variable in chart.apis.google link for QR Code and I was quite intrigued. I didn’t think it could be done at first. But then I figured it out.
I do know that BI Publisher has built in Chart support, so my first thought was to just use that. But BI Publisher charting cant generate QR Codes. None the less, I linked to BI Publisher Report Designers guide to explain those details if anyone stumbled upon this post looking for actual charts.
My second thought was to follow a blog entry that an Oracle developer posted about using QR Codes in BI Publisher. It mentioned making a java class, and using an IDAutomation QR Font. It was quite complex. This is probably the method to go after if you want all the data to remain internal to the Oracle server in the enterprises data-center. I posted the details to that too.
Finally, I remember using dynamic images at Emerson for insertion of Casting images into Work-orders/Shop-packets. I figured why not give it a shot. I created a quick XML test file, and a simple RTF file. I use the concat (concatenate) function in BI Publisher to combined the Google Charts API for QR codes and the variable I wanted encoded.
Test XML
<test_header>
<test_row>
<row_num>1</row_num>
<value>TESTA</value>
</test_row>
<test_row>
<row_num>2</row_num>
<value>TESTB</value>
</test_row>
</test_header>
Test RTF Template
RTF Template
Image ALT TEXT
url:{concat(‘https://chart.apis.google.com/chart?cht=qr&chs=500×500&choe=UTF-8&chld=H&chl=’,value)}
My first attempt failed because of a space in my test data. If you have spaces or special characters in the data you want encoded in the QR code, make sure you URL encode the data that you want to pass into the Google API URL. Oracle allows you to do this with the UTL_URL function. Also note: this post is being made with WordPress, the single quotes are improperly being replaced with smart quotes and/or HTML entities… use care if copying and pasting, and make sure to use the single quote as per screen shot.
Example use of UTL_URL.ESCAPE
select rownum row_num, utl_url.escape(record_value) value from test_table
Once I removed the space from my test data, it worked! I was shocked how easy it could be.
Test Output
PDF Output
Other Considerations
One last thing to note, if you expect this to work on your enterprise server/network/data-center, you’ll probably have to open up the firewall.
hi ,
I want to generate barcode using the same API discussed above, is it possible?
I’m not sure what you mean. If you want to generate a standard CODE 3of9, use a CODE 3of9 font, add to BI Publisher as an embedded font. There’s paid for and public fonts available on the internet.
Thanks for sharing it. Very useful one
This is the command that worked for me: url:{concat(‘https://chart.apis.google.com/chart?cht=qr&chs=500×500&choe=UTF-8&chld=H&chl=’,value)} Was getting errors until I removed the “’” value from this post. Then this worked great! Thanks.
Good catch Brian! WordPress is acting funny with the quote in the string. I attempted to fix it, and also added a note to the post. Thanks for pointing this out! Posting code on WordPress can be tricky.
Hi! Thanks for this post. I think this is really helpful in my requirement in generating QR codes in BI Publisher. However, what I need is a string value to be converted into a QR code. Do I just replace the “value” element with the string variable in my XML?
Correct.
copied the same steps, end output is blank ? any suggestions
Hard to tell without seeing your code.