y_mash Posted March 2, 2015 Share Posted March 2, 2015 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 ROWSFROM [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> Link to comment Share on other sites More sharing options...
Solution y_mash Posted March 4, 2015 Author Solution Share Posted March 4, 2015 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 ROWSFROM [uniTransCube]]]></queryString> Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now