[How to Create report from OLAP data]

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>

 

 

[How to Create report from OLAP data]

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>

 

[display name in 'level' in 'dimension' in OLAP View in Persian (Farsi)]

I want to display a Persian (Farsi) name for a level in a dimension in my OLAP View. I can't change the name because it doesn't work with Persian language. how can I set name in other languages for level?

[error after clicking on Hide Empty Rows/Columns]

I have found the solution. the problem was that I had a level in my schema of type bigint in postgresql. I don't know why the values became float. so I added type="Numeric" in that level and it works fine

[error after clicking on Hide Empty Rows/Columns]

I have a simple cube and a simple olap view. it works fine but when I click on "Hide Empty Rows/Columns", it makes an error

if anyone knows what's the problem please help me

my 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" uniqueMembers="false" type="Numeric"

levelType="TimeMonths"/>

<Level name="Day" column="day" uniqueMembers="false"

levelType="TimeDays"/>

 

</Hierarchy>

</Dimension>

 

 

<Cube name="AccountCreateCube">

<Table name="account"/>

 

<Dimension name="Bank Branch" primaryKey="account_branch_code">

 

<Hierarchy hasAll="True" allMemberName="all branches" defaultMember="all branches">

<Level name="Branch Id" column="account_branch_code" uniqueMembers="false"/>

</Hierarchy>

</Dimension>

<DimensionUsage name="Insert Date" source="Date" foreignKey="insert_date_id"/>

<Measure name="count" column="account_number" aggregator="count"/>

</Cube>