OBIEE 11g Group By

Uma Mahesh

I have a Table with columns

Business_Date	Month	Year	Amount
25-Sep-2016	Sep	2016	34325.45
26-Sep-2016	Sep	2016	42532.34
01-Oct-2016	Oct	2016	67243.36
02-Oct-2016	Oct	2016	99347.87

i want to display data in report as like

Month	Year	Max(Amount)
Sep	2016	42532.34
Oct	2016	99347.87

i've tried MAX(AMOUNT BY MONTH) but it now works for me

can anyone help me with this.

Thanks in advance,

Uma Mahesh.

Thomas Dodds

Your logical date dimension needs a hierarchy where month is keyed logically higher to date ... then BI server will aggregate the measure based on the aggregation rule supplied in the fact logical column and from/to the levels you've specified in the Content tab of the fact logical table source properties.

Once it's modeled right, the aggregation happens automatically to the grain of the query.

Uma Mahesh

hi Thomas,

Thanks for Quick reply

but i have some dimension columns too in that table like ACCOUNT_NO

what i want exactly is a Report With ACCOUNT_NO filed and MAX of AMOUNT AS Per Dates in a MONTH

Thomas Dodds

So you have mixed logical dimensional attributes?   you should have a fact (amount) joined to both a date (year, month, date) and an account (account number, first name, last name, etc) dimension.

OBIEE requires at the very least a well-formed logical star model to perform to its advertised features and performance levels.

Uma Mahesh

Thanks for your support Thomas