Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: MeasureExpression syntax

  1. #1

    Default MeasureExpression syntax

    Hello, we are dealing with statistical data over here. I have to measure how many ocurrences of several events happened, so I have to count within a column how many times one ocurrence happens rather than sum up the values of the column, but stil is one of my measure. there are six different events (1 to 6)
    I am trying to use MeasureExpression to count how many rows have this ocurrence (simbolized by the number 1).

    However, I am not sure about the syntax to use as I cannot find documentation about it. when I am trying to add the measure to be displayed in the table i got an error:
    '$query01.result.overflowOcurred'


    What I want to do is to add 1 to the count for every ocurrence of 1 (or 2 or 3, etc the values go from 1 to 6), and then be able to graph it.
    This is what I have so far:

    <Measure name="event 1" aggregator="count" datatype="String" formatString="Standard">

    <MeasureExpression>
    <SQL dialect="mysql">
    (case when 'detail'.'when_happened' = 1 then 1) /*do not know better*/
    </SQL>

    </MeasureExpression>
    </Measure>

    Hope to find some help about the syntax but some logic would be also appreciated.

    Thanks in advance
    Fran

  2. #2
    wgorman Guest

    Default

    Instead of using a count aggregator, why not use the sum aggregator like so:
    Code:
     <Measure name="event 1" aggregator="sum" datatype="String" formatString="Standard">
       <MeasureExpression>
         <SQL  dialect="mysql">
             (case when 'detail'.'when_happened' = 1 then 1 else 0 end)
        </SQL>
       </MeasureExpression>
    </Measure>
    By summing the 1's and 0's you get a valid count back.

  3. #3
    Join Date
    Nov 1999
    Posts
    1,618

    Default

    Also note that for MySQL, the quotes should be back-quotes:

    Code:
    case when `detail`.`when_happened` = 1 then 1 else 0 end
    Julian

  4. #4

    Default It worked, thanks

    Thank you, now is working.

    I have to get all the different events and display a chart.

  5. #5
    Join Date
    Mar 2008
    Posts
    5

    Exclamation Analisys - Mondrian - JPivot - Oracle - MySql

    Hi

    I'm use tow tools for make the pivot i'm used the jpivot only on apache -tomcat and i'm use pentaho cube designer, and workbench-3.0.2.10813.

    In the firts case i use the follow definition of my cube (File Name:CubeMysql.xml)CuboMySql.xml

    <Schema name="CuboMySql">
    <Cube name="CuboMySql" cache="true" enabled="true">
    <Table name="orderfact">
    </Table>
    <Dimension foreignKey="PRODUCTCODE" name="Producto">
    <Hierarchy name="Producto" hasAll="true" allMemberName="All producto" primaryKey="PRODUCTCODE">
    <Table name="products">
    </Table>
    <Level name="products.PRODUCTNAME" table="products" column="PRODUCTNAME" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
    </Level>
    </Hierarchy>
    </Dimension>
    <Dimension foreignKey="TIME_ID" name="Tiempo">
    <Hierarchy name="Tiempo" hasAll="true" allMemberName="All tiempo" primaryKey="TIME_ID">
    <Table name="time">
    </Table>
    <Level name="time.MONTH_NAME" table="time" column="MONTH_NAME" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
    </Level>
    <Level name="time.QTR_NAME" table="time" column="QTR_NAME" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
    </Level>
    </Hierarchy>
    </Dimension>
    <Measure name="numero_ordenes" column="ORDERNUMBER" datatype="Integer" formatString="#,##0" aggregator="count">
    </Measure>
    <Measure name="Promedio_venta" column="TOTALPRICE" datatype="Numeric" formatString="#,##0.###" aggregator="avg">
    </Measure>
    <Measure name="numero_productos" datatype="String" formatString="#,##0" aggregator="count">
    <MeasureExpression>
    <SQL dialect="generic">
    (case when `detail`.`when_happened` = 1 then 1 else 0 end) </SQL>
    </MeasureExpression>
    </Measure>
    </Cube>
    </Schema>

    And i use to connect the follow file conexionmysql.jsp.txt

    <%@ page session="true" contentType="text/html; charset=ISO-8859-1" %>
    <%@ taglib uri="http://www.tonbeller.com/jpivot" prefix="jp" %>
    <%@ taglib prefix="c" uri="http://java.sun.com/jstl/core" %>
    <jp:mondrianQuery id="query01" jdbcDriver="com.mysql.jdbc.Driver" jdbcUrl="jdbc:mysql://localhost:3306/sampledata?user=pentaho_user&password=pentaho_user" catalogUri="/WEB-INF/queries/CuboMySql.xml" >
    SELECT {[numero_ordenes]} ON COLUMNS, { [Tiempo] } ON ROWS FROM [CuboMySql]
    </jp:mondrianQuery>
    <c:set var="title01" scope="session">Una tabla dinamica simple que usa el servidor Mondrian OLAP </c:set>

    And the follow is the conect file PruebaMysql.jsp.txt PruebaMysql.jsp

    When i want to see this in Apache i get the follow problem:

    org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspTagException: javax.servlet.jsp.JspException: An error occurred while evaluating custom action attribute "test" with value "${query01.result.overflowOcurred}": Unable to find a value for "overflowOcurred" in object of class "com.tonbeller.jpivot.mondrian.MondrianResult" using operator "." (null)

    Thi is the sample example.

    For use Oracle i create cube using cube designe, publish the cube with this tool. Verify the cube using workbench-3.0.2.10813 and with this create the MDX and excute without errors but when want to see this in pentaho i dont get information.

    Thanks a lot

    JohnB

  6. #6
    Join Date
    Apr 2012
    Posts
    1

    Default

    Hello,

    I'm trying to create a query for the measure using MeasureExpression in pentaho workbench.

    <Measure name="Mark" column="poinsaslong_p" aggregator="count" visible = "true" >

    <MeasureExpression>
    <SQL dialect="postgres">
    <![CDATA[ SELECT session_mark_value_t.poinsaslong_p FROM public.session_mark_value_t WHERE session_mark_value_t.poinsaslong_p > 449]]>
    </SQL>
    </MeasureExpression>
    </Measure>

    What am I doing wrong?

    Thanks for your help

    Nastya.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.