Converting Binary to Text in Crystal Reports
If you have a field in your database that is in binary format and you’re also developing a Crystal Report off of that data, then you’ll need a way to convert that data into text…unless of course you want to leave it as binary format.
If you wish to convert binary data to text then this blog will help you out as there’s a sample report attached that has a Custom Function that will do the conversion. You can also convert text into binary if you need to. The way that the Custom Function works is that it takes 8 characters at a time of the binary data record and then converts that set of 1’s and 0’s into a text character. The function will loop through the record and the final output will be the translation of the entire record. It doesn’t matter if there are spaces in your binary data. For converting text to binary, the function loops through the record one character at a time converting that character into its binary equivalent.
As mentioned before, there is a sample report that has the Custom Function in the Report > Formula Workshop > Report Custom Functions folder. To use the report, extract the contents of the BinaryCharConverter file and change the .txt extension to .rpt. Also attached to this blog post is the binary and text data that the report was based off of. The chartable file is an Excel file so please change the .txt extension to .xls.
If you don’t have Crystal Reports Server or BusinessObjects Enterprise and still want to do this type of conversion (i.e. you don’t have a Repository to add the Custom Function to), on the Report Footer there’s a formula that you can copy to your reports that will also work. Both the formula and the function will convert some line based commands like chr(9) or chr(13) properly as well.
I am looking for a way to red and translate BLOB fields into plain text. Will this function help me to read blob fields, or do I need something else? Also, does the text have to be in groups of eight with a space separating the groups?
hey Jack, if your blob field is text but stored as binary then this should in fact work. if it doesn't then please past in a txt file a sample of your data.
if your blob field is text but stored as a different format then let me know...i.e. hex or something else...then those can also be converted but the function would have to be edited to include that translation set.
it doesn't matter if there are spaces or not but the binary must be in groups of 8 for the custom function as it is now. if you didn't have groups of eight then there'd be no way to translate it unless you had a delimiter such as a space or symbol. if you do have a delimiter then the function can be altered of course to work with that data.
cheers,
jamie
Hi Jamie,
I tried this, but CR11 keeps giving me the error "Cannot use a BLOB field in a formula." (It gives me that error any time I try to use a blob field in a formula.) There are specific fields I am trying to report on within the database for the ITIL Service Management software my company uses. Luckily, there aren't as many BLOB fields as there were in previous versions, so I'm not severely hampered, just inconvenienced.
I think at this point I'm stuck talking with the SQL admin over the database piece of the application, and see if he has any ideas or things he can try on the database side.
Thank you for the feedback,
Jack Linde
hi Jack,
try this with your blob field...create a new formula that contains only the blob field, and absolutely nothing else...i.e. the entire formula syntax for {@myblobfield} would be something like
{yourtable.yourblobfield}
now go back to your formula that contains the custom function and reference the new formula from above instead of referencing the blob field directly.
when you create a formula that contains only the blob field, then you should be able to use that formula in other formulae, group selection filters, summaries, etc.
cheers,
jamie
I have word documents / Images stored in Oracle database (in BLOB), my requirement is to dynamically display these word documents / Images in crystal report. Just wondering if this can be achieved using your function. If not, can you redirect me to some other option by which I can achieve this functionality.
Please note I tried inserting OLE object with no luck as it only shows first page of the document.
I am using CR 2011 along with .NET frmaework 4.0 for report development.
Thanks,
Sajid
hi Sajid,
unfortunately this will only work for text / string type database fields and not for docs or images.
i'm not sure of any way around the one page ole limitation either.
cheers,
jamie