How to Create report from OLAP data

[y_mash]

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]

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>