How to Create report from OLAP data

Jaspersoft Community 'Read-Only' as of July 8, 2022

Transition to New TIBCO Community Just Weeks Away

You can still search, review wiki content, and review discussions in read-only mode. Please email community@tibco.com with questions or issues requiring TIBCO review or response.

I have an OLAP cube and I want to create a report from it but report doesn't work properly, the problem is in groups it doesn't properly sum all the data for every year and every month. can anyone tell me how to choose fields and MDX query to generate report properly?

Schema:

<Dimension name="Date" type="TimeDimension">
<Hierarchy hasAll="true" primaryKey="date_pk" allMemberName="all periods" defaultMember="all periods">
<Table name="date"/>
<Level name="Year" column="year" type="Numeric" uniqueMembers="false"
levelType="TimeYears"/>
<Level name="Month" column="month" ordinalColumn="month" nameColumn="month_name" uniqueMembers="false" type="Numeric"
levelType="TimeMonths"/>
<Level name="Day" column="day" uniqueMembers="false"
levelType="TimeDays"/>
 
</Hierarchy>
 
</Dimension>
 
 
<Cube name="UniTransCube">
<Table name="transaction_uni"/>
 
<Dimension name="Type" primaryKey="type">
<Hierarchy hasAll="True" allMemberName="Sum of Inc and Dec" defaultMember="Sum of Inc and Dec">
<Level name="Type" column="type" uniqueMembers="false"/>
</Hierarchy>
</Dimension>
<DimensionUsage name="Device Type" source="Device Type" foreignKey="device_type_id"/>
<DimensionUsage name="Date" source="Date" foreignKey="date_id"/>
<Measure name="amount" column="amount" aggregator="sum"/>
<Measure name="count" column="account_number" aggregator="count"/>
 
</Cube>
 

 

report jrxml file:

<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="UniTransCubeReport" pageWidth="595" pageHeight="842" columnWidth="515" leftMargin="40" rightMargin="40" topMargin="50" bottomMargin="50" uuid="dfb9af95-b982-463e-ae20-e33b93fec633">
<property name="net.sf.jasperreports.export.pdf.tagged" value="true"/>
<property name="net.sf.jasperreports.export.pdf.tag.language" value="EN-US"/>
<property name="ireport.zoom" value="1.5"/>
<property name="ireport.x" value="0"/>
<property name="ireport.y" value="0"/>
<style name="Sans_Normal" isDefault="true" fontName="SansSerif" fontSize="8" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
<style name="Sans_Bold" fontName="SansSerif" fontSize="8" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica-Bold" pdfEncoding="Cp1252" isPdfEmbedded="false"/>
<queryString language="mdx">
<![CDATA[SELECT
  NON EMPTY {
    [Measures].[amount]
    , [Measures].[count]
  } ON COLUMNS,
  NON EMPTY {
    Crossjoin(
{[Date].Members}
      , {
        Crossjoin(
          {[Device Type].Members}
          , {[Type].Members}
        )
      }
    )
  } ON ROWS
FROM [UniTransCube]]]>
</queryString>
<field name="Type" class="java.lang.String">
<fieldDescription><![CDATA[Rows[Type][Type]]]></fieldDescription>
</field>
<field name="Year" class="java.lang.String">
<fieldDescription><![CDATA[Rows[Date][Year]]]></fieldDescription>
</field>
<field name="Month" class="java.lang.String">
<fieldDescription><![CDATA[Rows[Date][Month]]]></fieldDescription>
</field>
<field name="DeviceType" class="java.lang.String">
<fieldDescription><![CDATA[Rows[Device Type][Device Type]]]></fieldDescription>
</field>
<field name="Amount" class="java.lang.Number">
<fieldDescription><![CDATA[Data(0,?)]]></fieldDescription>
</field>
<field name="Count" class="java.lang.Number">
<fieldDescription><![CDATA[Data(1,?)]]></fieldDescription>
</field>
<field name="YearAmount" class="java.lang.Number">
<fieldDescription><![CDATA[Data(Rows[Date][Year])(0,?)]]></fieldDescription>
</field>
<field name="YearCount" class="java.lang.Number">
<fieldDescription><![CDATA[Data(Rows[Date][Year])(1,?)]]></fieldDescription>
</field>
<field name="MonthAmount" class="java.lang.Number">
<fieldDescription><![CDATA[Data(Rows[Date][Month])(0,?)]]></fieldDescription>
</field>
<field name="MonthCount" class="java.lang.Number">
<fieldDescription><![CDATA[Data(Rows[Date][Month])(1,?)]]></fieldDescription>
</field>
<group name="Year">
<groupExpression><![CDATA[$F{Year}]]></groupExpression>
<groupHeader>
<band height="16" splitType="Stretch">
<textField evaluationTime="Group" evaluationGroup="Year">
<reportElement style="Sans_Bold" x="10" y="0" width="150" height="15" uuid="2134bf9f-8185-4592-b027-a30589eb7c59">
<property name="net.sf.jasperreports.export.pdf.tag.tr" value="start"/>
<property name="net.sf.jasperreports.export.pdf.tag.td" value="full"/>
</reportElement>
<textFieldExpression><![CDATA["سال " + $F{Year}]]></textFieldExpression>
</textField>
<textField evaluationTime="Group" evaluationGroup="Year" pattern="¤ #,###">
<reportElement style="Sans_Bold" x="180" y="0" width="100" height="15" uuid="578b5cb3-220b-4b05-a5bb-29afc6396fe8">
<property name="net.sf.jasperreports.export.pdf.tag.td" value="full"/>
</reportElement>
<textElement textAlignment="Right"/>
<textFieldExpression><![CDATA[$F{YearAmount}]]></textFieldExpression>
</textField>
<textField evaluationTime="Group" evaluationGroup="Year" pattern="#,###">
<reportElement style="Sans_Bold" x="300" y="0" width="100" height="15" uuid="efd1312d-5a30-44d4-80d4-37d8786c876f">
<property name="net.sf.jasperreports.export.pdf.tag.td" value="full"/>
<property name="net.sf.jasperreports.export.pdf.tag.tr" value="end"/>
</reportElement>
<textElement textAlignment="Right"/>
<textFieldExpression><![CDATA[$F{YearCount}]]></textFieldExpression>
</textField>
</band>
</groupHeader>
</group>
<group name="Month">
<groupExpression><![CDATA[$F{Month}]]></groupExpression>
<groupHeader>
<band height="16" splitType="Stretch">
<textField evaluationTime="Group" evaluationGroup="Month">
<reportElement style="Sans_Bold" x="15" y="0" width="150" height="15" uuid="1b3cb4db-378b-4f82-bf6a-48f81aaa79dc">
<property name="net.sf.jasperreports.export.pdf.tag.tr" value="full"/>
<property name="net.sf.jasperreports.export.pdf.tag.td" value="full"/>
<property name="net.sf.jasperreports.export.pdf.tag.colspan" value="3"/>
</reportElement>
<textFieldExpression><![CDATA[$F{Month}]]></textFieldExpression>
</textField>
<textField evaluationTime="Group" evaluationGroup="Month" pattern="¤ #,###">
<reportElement style="Sans_Bold" x="180" y="0" width="100" height="15" uuid="578b5cb3-220b-4b05-a5bb-29afc6396fe8">
<property name="net.sf.jasperreports.export.pdf.tag.td" value="full"/>
</reportElement>
<textElement textAlignment="Right"/>
<textFieldExpression><![CDATA[$F{MonthAmount}]]></textFieldExpression>
</textField>
<textField evaluationTime="Group" evaluationGroup="Month" pattern="#,###">
<reportElement style="Sans_Bold" x="300" y="0" width="100" height="15" uuid="efd1312d-5a30-44d4-80d4-37d8786c876f">
<property name="net.sf.jasperreports.export.pdf.tag.td" value="full"/>
<property name="net.sf.jasperreports.export.pdf.tag.tr" value="end"/>
</reportElement>
<textElement textAlignment="Right"/>
<textFieldExpression><![CDATA[$F{MonthCount}]]></textFieldExpression>
</textField>
</band>
</groupHeader>
</group>
<title>
<band height="30" splitType="Stretch">
<staticText>
<reportElement x="0" y="0" width="515" height="30" uuid="6afcd4b4-0388-4567-a3a2-0f0f3a4fa86f"/>
<textElement textAlignment="Center">
<font size="12"/>
</textElement>
<text><![CDATA[گزارش تراکنش های تک حسابه]]></text>
</staticText>
</band>
</title>
<pageHeader>
<band height="24" splitType="Stretch">
<frame>
<reportElement mode="Opaque" x="0" y="0" width="515" height="15" backcolor="#E0E0E0" uuid="e51a4255-fcd7-401a-bcfd-940ccd08c899">
<property name="net.sf.jasperreports.export.pdf.tag.table" value="start"/>
<property name="net.sf.jasperreports.export.pdf.tag.tr" value="full"/>
</reportElement>
<box>
<pen lineWidth="0.5" lineStyle="Solid"/>
</box>
<staticText>
<reportElement x="20" y="0" width="150" height="15" uuid="0894e6cd-aeb6-438c-8b9d-7323090d134c">
<property name="net.sf.jasperreports.export.pdf.tag.th" value="full"/>
</reportElement>
<textElement verticalAlignment="Middle"/>
<text><![CDATA[تاریخ و نوع]]></text>
</staticText>
<staticText>
<reportElement x="180" y="0" width="100" height="15" uuid="125f05a0-6d21-4889-8713-88ec612c85b6">
<property name="net.sf.jasperreports.export.pdf.tag.th" value="full"/>
</reportElement>
<textElement textAlignment="Right" verticalAlignment="Middle"/>
<text><![CDATA[حجم تراکنش ها]]></text>
</staticText>
<staticText>
<reportElement x="300" y="0" width="100" height="15" uuid="7ef6ba4c-adea-4b9d-9ca5-451e8eda80d4">
<property name="net.sf.jasperreports.export.pdf.tag.th" value="full"/>
</reportElement>
<textElement textAlignment="Right" verticalAlignment="Middle"/>
<text><![CDATA[تعداد تراکنش ها]]></text>
</staticText>
</frame>
</band>
</pageHeader>
</jasperReport>
 
y_mash's picture
36
Joined: Sep 7 2014 - 3:12am
Last seen: 6 years 10 months ago

1 Answer:

I changed my MDX Query and it works fine:

<queryString language="mdx">
< ![CDATA[SELECT
  NON EMPTY {
    [Measures].[amount]
    , [Measures].[count]
  } ON COLUMNS,
  NON EMPTY {
    Crossjoin(
{[Date].Members, [Date].[Year].[2009], [Date].[Year].[2010], [Date].[Year].[2011], [Date].[Year].[2012]}
      , {
        Crossjoin(
          {[Device Type].[Device Type].Members}
          , {[Type].[Type].Members}
        )
      }
    )
  } ON ROWS
FROM [UniTransCube]]]>
</queryString>
 

 

y_mash's picture
36
Joined: Sep 7 2014 - 3:12am
Last seen: 6 years 10 months ago
Feedback