Create a kreporter field to store custom SQLs instead of writing them each time in the report. This is useful when a complex custom SQL is needed in many reports.

Simple example

Create a field that calculates the weighted amount of opportunities

custom/Extension/modules/Opportunities/Ext/Vardefs/kreporterdefs.php

$dictionary['Opportunity']['fields']['weighted_amount'] = array(
    'name' => 'weighted_amount', 
    'vname' => 'LBL_WEIGTED_AMOUNT', 
    'type' => 'kreporter', 
    'source' => 'non-db', 
    'kreporttype' => 'currency', 
    'currency_id' => 'currency_id',
    'eval' => '{t}.amount * {t}.probability / 100',
    'evalSQLFunction' => 'X' //set to allow the use of SQL function set in manipulate i.e. sum
);

Think of repair/rebuild to make the new variable available for Kreporter

Advanced Example

{p1} and {p2} correspond respectively to value and value to columns in filter (select tab)

$dictionary['Account']['fields']['vkbur'] = array (
      'name' => 'vkbur',
      'vname' => 'LBL_VKBUR',
      'type' => 'kreporter',
      'source' => 'non-db',
      'kreporttype' => 'varchar',
      'eval' => 
      array (
        'presentation' => 
        array (
          'eval' => 'select group_concat(kaccountsdetails.vkbur) from kaccountsdetails where
                kaccountsdetails.account_id = {t}.id and kaccountsdetails.deleted = 0',
        ),
        'selection' => 
        array (
          'equals' => 'exists(select * from kaccountsdetails where vkbur = \'{p1}\' and account_id = {t}.id and deleted=0)',
          'between' => 'exists(select * from kaccountsdetails where vkbur >= \'{p1}\' and vkbur <= \'{p2}\' and account_id = {t}.id and deleted=0)',
          'starts' => 'exists(select * from kaccountsdetails where vkbur like \'%{p1}\' and account_id = {t}.id and deleted=0)',
          'contains' => 'exists(select * from kaccountsdetails where vkbur like \'%{p1}%\' and account = {t}.id and deleted=0)',
        ),
      ),
    )

results matching ""

    No results matching ""