Adding Custom Function Generic JDBC Data Soruce

Steve Remington shared this question 1 year ago
Answered

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.

Comments (5)

photo
1

Hi Steve,

If all of these rows returned null, my guess is that there is some error thrown during processing. Can you see if there is a stacktrace in the logs for this?

Regarding the appropriate tag, this should have no effect on how the query is run, only on what list it shows up under. I will dig a bit and see if I can find what the exact tag is for generic connections.

Regards,

Nathan

photo
1

Hi Nathan,

I tried my custom function code on another Yellowfin instance and it worked. I then tried in back on the original Yellowfin instance. It also worked. I'm not sure why it did not work initially but all is good now. Go figure. ¯\_(ツ)_/¯

Thanks for your reply.

photo
1

Glad to hear it!

photo
photo
1

Hi Steve,

Found the complete custom function db type list and will create an article for it shortly. In the mean time:

"GenericJDBC"

Should work, and it will ignore case.

Regards,

Nathan