JA - How to get Year from the whole Date

[dpeykov]

Hi All,

 

I'm using Jasper Analysis and I have a problem getting the YEAR datepart only from the whole date.:S

I mean ... in my DB (MySQL) there is a column "date_closed", which contains all the dates, when the opportunities were closed. But in my Analysis View I would like to display Time as a dimension with the following Levels: Year, Quarter, Month.

I tried setting the levelType as TimeYears... and so on, but no results. :(

 

I'd like to ask something else .. how to define a Quarter?!?

 

All The Best,

Dinko Peykov

[dpeykov]

Hi All,

 

I still don't have any solution of the problem, so please help me. Give me any idea .. how can I set a Time period (Month, Quarter, Year), using it in the Analysis View.

 

This one, below, is my last idea, but it is not working properly. :(

<Schema name="New Schema1"> <Dimension type="StandardDimension" name="Product"> <Hierarchy name="" hasAll="true" allMemberName="AllProducts" primaryKey="id_c" primaryKeyTable="opportunities_cstm" defaultMember="HACCP" caption=""> <Table name="opportunities_cstm" schema="" alias=""> </Table> <Level name="ProductName" table="opportunities_cstm" column="product_c" nameColumn="product_c" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never" caption=""> </Level> </Hierarchy> </Dimension> <Dimension type="TimeDimension" name="Time"> <Hierarchy name="" hasAll="true" allMemberName="AllTimePeriods" allMemberCaption="" allLevelName="" primaryKey="id" primaryKeyTable="" defaultMember="" memberReaderClass="" caption=""> <Table name="opportunities" schema="" alias=""> </Table> <Level name="Month" table="opportunities" column="date_closed" parentColumn="date_closed" type="Numeric" uniqueMembers="true" levelType="TimeMonths" captionColumn=""> <ParentExpression> <SQL dialect="mysql"> select month(date_closed) from opportunities; </SQL> </ParentExpression> </Level> </Hierarchy> </Dimension> <Cube name="calendarTest" cache="true" enabled="true"> <Table name="opportunities" schema="" alias=""> </Table> <DimensionUsage source="Product" usagePrefix="" name="Product" foreignKey="id"> </DimensionUsage> <DimensionUsage source="Time" level="" usagePrefix="" name="Time" caption="" foreignKey="id"> </DimensionUsage> <Measure name="SumAmount" column="amount" datatype="Numeric" formatString="Currency" aggregator="sum" caption="" visible="true"> </Measure> </Cube> </Schema>

I hope somebody of U guys will help me solve this problem recently.

 

Best Regards,

D.Peykov