Forum

Expand all | Collapse all

Advanced Reporting 101: Calculated Fields

  • 1.  Advanced Reporting 101: Calculated Fields

    Posted 10-08-2014 10:57

    Calculated fields allow you to manipulate the information in the database and make it more friendly or getting specific calculations in the source itself without having to do it in the report.

     

    The calculated fields in Advanced Reporting use the DomEL Syntax: Advanced Reporting 101: The DomEL Syntax used by Calculated Fields

     

    Two examples of custom fields you will create the most:

    Dates: date fields in CSM are stored as Epoch format. They are an integer field with the number of seconds past 1970-01-01 00:00:00. To make it become a real date you need to create a calculated field with:

     

     

    To create a calculated field you need to go to the Domain Designer: Advanced Reporting 101: Accessing the Domain Designer

     

    Go to the Calculated Field tab:

    Untitled.png

     

    Configure the three fields as per your need:

    Field Name is the name of your new field. Just keep in mind you can't use spaces and some special characters can be a problem.

    Type is the type of your new field. Types are self explanatory.

    Expression is the expression you want to use to calculate your new field. Here you can have math expressions, concatenations, use SQL Functions, etc. It is mainly SQL code but there is also the possibility to use DomEL Syntax

     

    When you are building the expression you can type in the tables and fields references or you can double click a field to add it to the expression automatically.

     

    When you save your calculated field:

    • If it uses only one table's fields it will be created inside that table.
    • If it uses multiple tables' fields it will be created inside the join tree where those tables are.
    • If it uses multiple tables and they are not joined... well, you can't do this
    • If it doesn't use any existent field it will be created as a constant in the Constant tree

     

    Let's say you need to create these fields:

    Ticket created date in date format

    Ticket Requester as Last Name, Name

     

    These are the configurations you need:

    Field NameTypeExpression
    date_createdTimestampEPOCH_TO_UTC_DATETIME(dbo_VAPP_ITEM.CREATED_DATE)
    affected_userStringconcat(dbo_VAPP_ITEM.person2_last_name,',', dbo_VAPP_ITEM.person2_first_name)

    You can simply copy and paste the expression above to the Expression field

     

    date_created:

    Untitled.png

     

    affected_user:

    Untitled.png

     

    Once you click Save Field it will be saved in the VAPP_ITEM table because the origin fields are all from there.

     

    Next you just need to make this fields visible using the Display tab: link to be added



  • 2.  Advanced Reporting 101: Calculated Fields

    Posted 11-19-2014 19:41

    Hi Daniel:

    If I want the Class Category Type and Item as one field in my report will I use the "concat" as well for this?



  • 3.  Advanced Reporting 101: Calculated Fields

    Posted 11-20-2014 04:45

    Yes Pam. Use Concat.

     

     

    Daniel Sasajima



  • 4.  Advanced Reporting 101: Calculated Fields

    Posted 03-27-2015 12:31

    Hi Pamela,

     

    you can use every function your underlying database (Oracle or MSSQL) supports. On top, please have a look at the domain expression language (The DomEL Syntax | Jaspersoft Community)



  • 5.  RE: Advanced Reporting 101: Calculated Fields

    Posted 9 days ago
    Hello,

    Thank you for sharing domain expression language, I was looking for Jaspersoft Community.
    If anyone want to know more about oracle, go through this link: https://mindmajix.com/oracle-ebs-training

    Thanks & Regards,
    Anita

    ------------------------------
    Anita Basa
    Analyst
    Anita Basa
    New York NY
    ------------------------------



  • 6.  Advanced Reporting 101: Calculated Fields

    Posted 08-16-2016 05:49

    Hi Daniel,

     

    I have created a calculated field (Calculated_Open_Date) using EPOCH_TO_UTC_DATETIME function and added that in Display section of the domain (Date Format: None, Summary Calculation: CountAll, Field or measure: Dimension). When I am creating the Add Hoc View using that domain and trying to add the Calculated Open Date field in the Column section, it is showing the error that:

    "An error occurred while performing the previous request"

     

    It is showing no parsing error during calculated field creation.

     

    Kindly suggest the solution for this.

     

    Thanks & Regards,

    Balram

     



  • 7.  Advanced Reporting 101: Calculated Fields

    Posted 08-16-2016 08:25
      |   view attached

    It is showing the following error:

    Kindly suggest.

     

    Regards,

    Balram



  • 8.  Advanced Reporting 101: Calculated Fields

    Posted 08-17-2016 11:02

    Hi Balram.Deswal please open up a new thread for this discussion so we can investigate this separate from the 101 document.