Adding Custom Function Generic JDBC Data Soruce
I am using a Generic JDBC driver as a data source. All aspects of reporting writing work correctly.
I want to add a custom function that masks all but the first two and last two characters of a text field (e.g. "1234567890" -> "12XXXXXX90").
I have written and successfully tested the SQL function to achieve the desired output.
I added the following snippet of XML to the custom-functions.xml file immediately before the </custom-functions> tag at the end of the file. I save custom-functions.xml and restarted the Yellowfin service.
<function> <name>Mask Field</name> <argument> <index>1</index> <name>Field to Mask</name> <datatype>text</datatype> <!-- numeric, text, datetime --> </argument> <sql> <![CDATA[ concat(substr($1,0, 2), repeat('Z',length($1)-4), substr($1,-2)) ]]> </sql> <groupby>concat(substr($1,0, 2), repeat('Z',length($1)-4), substr($1,-2))</groupby> <!-- <database></database> none specified == all --> <return>text</return> </function>
The "Mask Field" function appeared in the list of available pre-defined functions and I was able to select "Mask Field" and add the "Field to Mask" argument.
When I create a test report that contained the field to be masked and the calculated field the report runs but the calculated field based on the "Mask Field" custom function is blank for every row in the report.
If I take the SQL script created by Yellowfin for the test report and execute it directly in the database command line and the correct results are returned. See below:
SELECT table_name . stat_date , table_name . subs_id , concat(substr( table_name . subs_id ,0, 2), repeat('Z',length( table_name . subs_id )-4), substr( table_name . subs_id ,-2)) FROM table_name WHERE ( table_name . subs_id IN ('5137610776') )Why is the result of the custom function not appearing in my report?
Do have to set a specific value for the <database></database> tag? If so what is it for a JDBC driver? I tried <database>JDBC</database> but the custom function did not appear in the pre-defined function list.
Thanks in advance for your advice.