substringで文字列を切り出すYellowfinカスタム関数の例

[Yellowfin](https://yellowfin.co.jp) には [カスタム関数](https://wiki.yellowfin.co.jp/pages/viewpage.action?pageId=2293774) という機能があります。
標準の [計算フィールド](https://wiki.yellowfin.co.jp/pages/viewpage.action?pageId=2293964) や [高度な関数](https://wiki.yellowfin.co.jp/pages/viewpage.action?pageId=2293963) では対応できない計算式を組みたいとき、直接 [フリーハンドSQL](https://wiki.yellowfin.co.jp/pages/viewpage.action?pageId=2293964#id-%E8%A8%88%E7%AE%97%E5%BC%8F-%E3%83%95%E3%83%AA%E3%83%BC%E3%83%8F%E3%83%B3%E3%83%89SQL) を書くことが考えられますが、その場合はレポート作成者がSQLに精通している必要があります。
カスタム関数はそのデメリットを解消するもので、事前に必要な関数を管理者側で定義しておくことによって、レポート作成者はSQLの知識がなくとも複雑な処理をレポートに組み込めるようになります。

この記事では、文字列の切り出しを行うカスタム関数の例をご紹介します。

## 作成するカスタム関数

SQLのSUBSTRING関数を使用して文字列を切り出すカスタム関数を作成します。
以下の3つの引数を指定すると、切り出された文字列が返ってくるようにします。

|引数  |説明  |
|---|---|
|String  |文字列を切り出したいカラム  |
|STSRT  |切り出しの開始位置（先頭は1）  |
|LENGTH  |切り出す文字数  |

## custom-functions.xmlの編集

カスタム関数を追加するには、`[Yellowfinのインストールフォルダ]/appserver/webapps/ROOT/WEB-INF/` の配下にある `custom-functions.xml` を編集します。以下の例を追記してください。

### substring関数の例

```xml:custom-functions.xml
  <!-- SUBSTRING FOR EACH DATABASE  -->
   
  <function>
    <name>*SUBSTR,Derby</name>
    <argument>
      <index>1</index>
      <name>String</name>
      <datatype>text</datatype>
    </argument>
    <argument>
      <index>2</index>
      <name>STSRT</name>
      <datatype>numeric</datatype>
    </argument>
    <argument>
      <index>3</index>
      <name>LENGTH</name>
      <datatype>numeric</datatype>
    </argument>
    <sql>
      <![CDATA[
         SUBSTR($1,$2,$3)
      ]]>
    </sql>
    <database>Derby</database>
    <groupby>SUBSTR($1,$2,$3)</groupby>
    <return>text</return>
  </function>

  <function>
    <name>*SUBSTR,PostgreSQL</name>
    <argument>
      <index>1</index>
      <name>String</name>
      <datatype>text</datatype>
    </argument>
    <argument>
      <index>2</index>
      <name>STSRT</name>
      <datatype>numeric</datatype>
    </argument>
    <argument>
      <index>3</index>
      <name>LENGTH</name>
      <datatype>numeric</datatype>
    </argument>
    <sql>
      <![CDATA[
         SUBSTR($1,$2,$3)
      ]]>
    </sql>
    <database>PostgreSQL</database>
    <groupby>SUBSTR($1,$2,$3)</groupby>
    <return>text</return>
  </function>
  
  <function>
    <name>*SUBSTR,Oracle</name>
    <argument>
      <index>1</index>
      <name>String</name>
      <datatype>text</datatype>
    </argument>
    <argument>
      <index>2</index>
      <name>STSRT</name>
      <datatype>numeric</datatype>
    </argument>
    <argument>
      <index>3</index>
      <name>LENGTH</name>
      <datatype>numeric</datatype>
    </argument>
    <sql>
      <![CDATA[
          SUBSTR($1,$2,$3)
      ]]>
    </sql>
    <database>Oracle</database>
    <groupby>SUBSTR($1,$2,$3)</groupby>
    <return>text</return>
  </function>
   
  <function>
    <name>*SUBSTRING,SQLServer</name>
    <argument>
      <index>1</index>
      <name>String</name>
      <datatype>text</datatype>
    </argument>
    <argument>
      <index>2</index>
      <name>STSRT</name>
      <datatype>numeric</datatype>
    </argument>
    <argument>
      <index>3</index>
      <name>LENGTH</name>
      <datatype>numeric</datatype>
    </argument>
    <sql>
      <![CDATA[
          SUBSTRING($1,$2,$3)
      ]]>
    </sql>
    <database>SQLServer</database>
    <groupby>SUBSTRING($1,$2,$3)</groupby>
    <return>text</return>
  </function>
   
     
  <function>
    <name>*SUBSTRING,MySQL</name>
    <argument>
      <index>1</index>
      <name>String</name>
      <datatype>text</datatype>
    </argument>
    <argument>
      <index>2</index>
      <name>STSRT</name>
      <datatype>numeric</datatype>
    </argument>
    <argument>
      <index>3</index>
      <name>LENGTH</name>
      <datatype>numeric</datatype>
    </argument>
    <sql>
      <![CDATA[
          SUBSTRING($1,$2,$3)
      ]]>
    </sql>
    <database>MySQL</database>
    <groupby>SUBSTRING($1,$2,$3)</groupby>
    <return>text</return>
  </function>
```
custom-functions.xmlの書き方について、詳しい説明は [オンラインマニュアル](https://wiki.yellowfin.co.jp/pages/viewpage.action?pageId=2293774) をご参照ください。

今回の例では、Derby、PostgreSQL、Oracle、SQLServer、MySQLに対応しています。
どれも同じような記述をしていますが、関数の名前がSQLServerとMySQLは`SUBSTRING`であるのに対して、DerbyとPostgreSQLとOracleは`SUBSTR`である点に差異が生じています。
分析データベースに上記以外を使用している場合は、その製品に合わせた関数の記述を行ってください。

## Yellowfinの再起動

custom-functions.xmlの編集後はYellowfinの再起動が必要です。
再起動後、以下の画面のように、追加したカスタム関数が定義済みの計算フィールドとして使えるようになります。

![image (2).png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/116818/ffe9ad7b-442c-abf4-32c6-a824a7a4bcb9.png)