{"id":24110,"date":"2024-02-15T14:53:34","date_gmt":"2024-02-15T22:53:34","guid":{"rendered":"https:\/\/docs.extraview.com\/v25\/book\/report-expressions-1\/"},"modified":"2024-02-27T17:51:39","modified_gmt":"2024-02-28T01:51:39","slug":"report-expressions-1","status":"publish","type":"page","link":"https:\/\/docs.extraview.com\/v25\/report-expressions-1\/","title":{"rendered":"Report Expressions"},"content":{"rendered":"

Report expressions are used to calculate or derive new field values which are only placed on report output. For example, you may want to multiply the values held in two columns of a report to derive a new calculated value. Another example may be to subtract one date from another to calculate the number of days between two events. Report expressions are first defined by creating a field in the data dictionary that is placed on the report. The actual calculation or expression is defined in the default value of the field, or in an attribute that is stored with the field within a report. Using the default value allows you to define a calculated field that may be shared across many reports without further configuration. However, you can use a single Expression type field defined in the data dictionary and reuse this on many reports for many purposes. You achieve this by selecting the field for use on any report, then using its Alternative Title<\/b> and Expression<\/b> attributes to modify its purpose for that report.<\/p>\n

Syntax for Report Expressions<\/h3>\n

The general syntax of an expression matches a subset of that in ANSI-99 SQL expressions, with arithmetic, string, and date data types. This includes infix and prefix operators, with standard precedence rules. Also, parentheses may be used to group sub-expressions.<\/p>\n

Note that although the expressions utilize ANSI-99, the different databases upon which ExtraView may be installed do use different syntax for many operations, and the syntax you use within your expressions must be correct for the underlying database. To make matters even more complex, the syntax of some expressions may vary across different versions of the same database.<\/div>\n

Literals of each type are supported and although full ANSI syntax is not promised, it should be sufficient to allow specification of a specific value for each data type. Variable names may refer to fields in the database. Each variable is in the form $$DD_NAME$$<\/b> and may refer to a standard ExtraView field name or special variable. Usually, the specification of a variable name refers to its report value (viz., its title or rendered form). However, inside an expression, the value<\/b> of the variable is used, not its rendered form. Also, see the note on enumerated types below.<\/p>\n

Limitations<\/h3>\n
    \n
  1. Expressions are not supported for use on repeating row fields within your data dictionary<\/li>\n
  2. Text Area, HTML Area, Print Text and Log Area fields are limited in that they can only display (approximately) the first 4,000 characters of any entry.\u00a0 In many circumstances this is sufficient, but if you have really long entries, they will be truncated with report expressions.\u00a0 This is because these field types are actually concatenated in 4,000 chunks in the database and special logic is used to handle word boundaries so that words are always searchable as keywords in the database.<\/li>\n<\/ol>\n

    Variable References to Enumerated Types<\/h3>\n

    Enumerated types (fields with a display type of List, Popup, Checkbox, and Tab) may be referenced only as string variables, in which case their title is used in the expression. Only single-valued list type fields may be referenced.<\/p>\n

    Evaluation of Expressions<\/h3>\n

    As much as possible, report expressions are evaluated by ExtraView by processing at two levels:<\/p>\n

      \n
    • Select list construction within the detail report query; and<\/li>\n
    • Retrieval of the detailed expression values for rendering in the report row<\/li>\n<\/ul>\n

      Syntax checking includes tests for single-quoted strings, double-quoted strings, parentheses and the \u201ccast\u201d function. Syntax checking is done before the attribute is entered and stored as part of the report.<\/p>\n

      Null Values<\/h3>\n

      The existence of a null value in an expression renders the result of the entire expression value to be null.<\/p>\n

      Expression Error Handling<\/h3>\n

      Each report expression is syntax-checked and variable references are validated before allowing the containing report to be updated or run. There may be opportunity for the user to create erroneous expressions, as not all expressions can be determined without reference to the values in the database.<\/p>\n

      Report Field Titles<\/h3>\n

      To support report-specific titles on the expression fields, the ALTERNATE FIELD TITLE<\/b> attribute is supported. This compensates for the difficulty for users to create column headings when they are sharing a data dictionary entry, such as the EXPRESSION type entries. The ALTERNATE FIELD TITLE<\/b> attribute appears within the GUI of the report builder to allow the user to define a title for a report through a simple selection mechanism.<\/p>\n

      Use of Session Variables<\/h3>\n

      Session variables defined in the data dictionary may be used as substitutable variables in a report expression. Each referenced session variable value must be set using a runtime filter for the report. Session variables may also be used within runtime filters to provide a mechanism to provide the same value for the occasions when the same field is used multiple times within filter queries. This can happen with advanced queries that use multiple and<\/b> and or<\/b> conjunctions. Date fields within the ExtraView database are stored as timestamp<\/em> type fields, which is done for internal efficiency and to facilitate a wide number of internal computations. This does not make them very simple to work with in expressions, but the following examples should help significantly. The session variable must have been defined with a compatible display type, such as DATE, DAY, or TEXTFIELD for use within the expression.<\/p>\n

      Date Calculations<\/h3>\n

      A frequently use of expressions is to calculate the difference between two date fields. The internal database representation of date fields use the TIMESTAMP data type. This data type does not lend itself to simple addition and subtraction to calculate differences between dated. The date_diff<\/span> function is an ExtraView function that simplifies date calculations. It is used in this way:<\/p>\n

      \u00a0 \u00a0 date_diff($$DATE_FIELD_1$$, $$DATE_FIELD_2$$)<\/span><\/p>\n

      This returns the difference as a whole number, between the two date fields.\u00a0 This is a Number <\/strong>type expression as the result is returned as a whole number.<\/p>\n

      Examples<\/h3>\n
        \n
      1. Compute the elapsed time, in number of days, from when an issue was created, to when it was last updated, and place the result in a field named DAYS_ELAPSED:

        DAYS_ELAPSED = date_diff($$TIMESTAMP$$, $$DATE_CREATED$$)<\/span>

        Note that you only enter the right-hand side of the expression into the Default Value<\/strong> in the data dictionary, or into the Expression<\/strong> field in the report editors<\/li>\n
      2. Compute a total sale value with sales tax based on a rate of 8%:$$currency_field$$ * 1.08<\/span><\/li>\n
      3. Count the number of selections of a value in a field named my_field<\/b> with a value of Yes<\/b>, when another field named other_field<\/b> has a specific value of val<\/b>:

        case when ($$my_field$$ = ‘Yes’ and $$other_field$$ = ‘val’) then 1 else null end<\/span>

        <\/li>\n
      4. Add seven days to the current date:


        \nOracle and SQL Server – $$SYSDATE$$ + 7
        \n<\/span>MySQL – DATE_ADD($$SYSDATE$$, INTERVAL 7 DAY)
        \n<\/span>\u00a0<\/span>

        <\/li>\n
      5. This is a more complex example, which concatenates several fields together, and places the results in a single output column on a report.\u00a0 The example takes the fields SALUTATION_TITLE<\/code>, FIRST_NAME<\/code>, LAST_NAME<\/code> as the first line of the output.\u00a0 The chr(10)<\/code> provides a linefeed so that the field ADDRESS<\/code> appears on the second line.\u00a0 The next line contains ADDRESS_2<\/code>, then followed in the next line by ADDRESS_3<\/code>.\u00a0 The next line contains a concatenation of the CITY<\/code>, STATE <\/code>and POSTAL_CODE<\/code>.\u00a0 The final line contains the PHONE <\/code>field.\u00a0 The decode statements are standard SQL that allow the suppression of the field, if there is no value returned from the query.

        decode($$SALUTATION.TITLE$$,'','',$$SALUTATION.TITLE$$ || ' ') ||
        \ndecode($$FIRST_NAME$$,'','',$$FIRST_NAME$$) || chr(10) ||
        \ndecode($$LAST_NAME$$,'','',$$LAST_NAME$$) || chr(10) ||
        \ndecode($$ADDRESS$$,'','',$$ADDRESS$$ || chr(10)) ||
        \ndecode($$ADDRESS_2$$,'','',$$ADDRESS_2$$ || chr(10)) ||
        \ndecode($$ADDRESS_3$$,'','',$$ADDRESS_3$$ || chr(10)) ||
        \ndecode($$CITY$$,'','',$$CITY$$ || ', ') ||
        \ndecode($$STATE$$,'','',$$STATE$$ ||' ') ||
        \ndecode($$POSTAL_CODE$$,'','',$$POSTAL_CODE$$) || chr(10) ||
        \ndecode($$PHONE$$,'','',$$PHONE$$)<\/code>

        The end result is that you have composed a single field that contains the address in the expected format, although the components were each stored as individual fields.<\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"

        Report expressions are used to calculate or derive new field values which are only placed on report output. For example, you may want to multiply the values held in two columns of a report to derive a new calculated value. Another example may be to subtract one date from another to calculate the number of…<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"_acf_changed":false,"_lmt_disableupdate":"","_lmt_disable":"","_kad_blocks_custom_css":"","_kad_blocks_head_custom_js":"","_kad_blocks_body_custom_js":"","_kad_blocks_footer_custom_js":"","_kad_post_transparent":"","_kad_post_title":"","_kad_post_layout":"","_kad_post_sidebar_id":"","_kad_post_content_style":"","_kad_post_vertical_padding":"","_kad_post_feature":"","_kad_post_feature_position":"","_kad_post_header":false,"_kad_post_footer":false,"footnotes":""},"class_list":["post-24110","page","type-page","status-publish","hentry"],"acf":[],"yoast_head":"\nReport Expressions - Product Documentation<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/docs.extraview.com\/v25\/report-expressions-1\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Report Expressions - Product Documentation\" \/>\n<meta property=\"og:description\" content=\"Report expressions are used to calculate or derive new field values which are only placed on report output. For example, you may want to multiply the values held in two columns of a report to derive a new calculated value. Another example may be to subtract one date from another to calculate the number of...\" \/>\n<meta property=\"og:url\" content=\"https:\/\/docs.extraview.com\/v25\/report-expressions-1\/\" \/>\n<meta property=\"og:site_name\" content=\"Product Documentation\" \/>\n<meta property=\"article:modified_time\" content=\"2024-02-28T01:51:39+00:00\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/docs.extraview.com\/v25\/report-expressions-1\/\",\"url\":\"https:\/\/docs.extraview.com\/v25\/report-expressions-1\/\",\"name\":\"Report Expressions - Product Documentation\",\"isPartOf\":{\"@id\":\"https:\/\/docs.extraview.com\/v25\/#website\"},\"datePublished\":\"2024-02-15T22:53:34+00:00\",\"dateModified\":\"2024-02-28T01:51:39+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/docs.extraview.com\/v25\/report-expressions-1\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/docs.extraview.com\/v25\/report-expressions-1\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/docs.extraview.com\/v25\/report-expressions-1\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/docs.extraview.com\/v25\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Report Expressions\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/docs.extraview.com\/v25\/#website\",\"url\":\"https:\/\/docs.extraview.com\/v25\/\",\"name\":\"ExtraView Product Documentation\",\"description\":\"ExtraView Documentation\",\"publisher\":{\"@id\":\"https:\/\/docs.extraview.com\/v25\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/docs.extraview.com\/v25\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/docs.extraview.com\/v25\/#organization\",\"name\":\"ExtraView Corporation\",\"url\":\"https:\/\/docs.extraview.com\/v25\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/docs.extraview.com\/v25\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/docs-stg.extraview.com\/wp-content\/uploads\/2024\/03\/favicon.png\",\"contentUrl\":\"https:\/\/docs-stg.extraview.com\/wp-content\/uploads\/2024\/03\/favicon.png\",\"width\":512,\"height\":512,\"caption\":\"ExtraView Corporation\"},\"image\":{\"@id\":\"https:\/\/docs.extraview.com\/v25\/#\/schema\/logo\/image\/\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Report Expressions - Product Documentation","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/docs.extraview.com\/v25\/report-expressions-1\/","og_locale":"en_US","og_type":"article","og_title":"Report Expressions - Product Documentation","og_description":"Report expressions are used to calculate or derive new field values which are only placed on report output. For example, you may want to multiply the values held in two columns of a report to derive a new calculated value. Another example may be to subtract one date from another to calculate the number of...","og_url":"https:\/\/docs.extraview.com\/v25\/report-expressions-1\/","og_site_name":"Product Documentation","article_modified_time":"2024-02-28T01:51:39+00:00","twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/docs.extraview.com\/v25\/report-expressions-1\/","url":"https:\/\/docs.extraview.com\/v25\/report-expressions-1\/","name":"Report Expressions - Product Documentation","isPartOf":{"@id":"https:\/\/docs.extraview.com\/v25\/#website"},"datePublished":"2024-02-15T22:53:34+00:00","dateModified":"2024-02-28T01:51:39+00:00","breadcrumb":{"@id":"https:\/\/docs.extraview.com\/v25\/report-expressions-1\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/docs.extraview.com\/v25\/report-expressions-1\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/docs.extraview.com\/v25\/report-expressions-1\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/docs.extraview.com\/v25\/"},{"@type":"ListItem","position":2,"name":"Report Expressions"}]},{"@type":"WebSite","@id":"https:\/\/docs.extraview.com\/v25\/#website","url":"https:\/\/docs.extraview.com\/v25\/","name":"ExtraView Product Documentation","description":"ExtraView Documentation","publisher":{"@id":"https:\/\/docs.extraview.com\/v25\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/docs.extraview.com\/v25\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/docs.extraview.com\/v25\/#organization","name":"ExtraView Corporation","url":"https:\/\/docs.extraview.com\/v25\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/docs.extraview.com\/v25\/#\/schema\/logo\/image\/","url":"https:\/\/docs-stg.extraview.com\/wp-content\/uploads\/2024\/03\/favicon.png","contentUrl":"https:\/\/docs-stg.extraview.com\/wp-content\/uploads\/2024\/03\/favicon.png","width":512,"height":512,"caption":"ExtraView Corporation"},"image":{"@id":"https:\/\/docs.extraview.com\/v25\/#\/schema\/logo\/image\/"}}]}},"taxonomy_info":[],"featured_image_src_large":false,"author_info":{"display_name":"carl.koppel","author_link":"https:\/\/docs.extraview.com\/v25\/author\/carl-koppel\/"},"comment_info":0,"_links":{"self":[{"href":"https:\/\/docs.extraview.com\/v25\/wp-json\/wp\/v2\/pages\/24110","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/docs.extraview.com\/v25\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/docs.extraview.com\/v25\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/docs.extraview.com\/v25\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/docs.extraview.com\/v25\/wp-json\/wp\/v2\/comments?post=24110"}],"version-history":[{"count":0,"href":"https:\/\/docs.extraview.com\/v25\/wp-json\/wp\/v2\/pages\/24110\/revisions"}],"wp:attachment":[{"href":"https:\/\/docs.extraview.com\/v25\/wp-json\/wp\/v2\/media?parent=24110"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}