Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

OBIEE 11g Group By

Received Response
22
Views
4
Comments
Uma Mahesh
Uma Mahesh Rank 4 - Community Specialist
edited Aug 13, 2024 6:11PM in Oracle Analytics Cloud and Server

I have a Table with columns

Business_Date
MonthYear
Amount
25-Sep-2016Sep201634325.45
26-Sep-2016Sep201642532.34
01-Oct-2016Oct201667243.36
02-Oct-2016Oct201699347.87

i want to display data in report as like

MonthYear
Max(Amount)
Sep201642532.34
Oct201699347.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.

Answers

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    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
    Uma Mahesh Rank 4 - Community Specialist

    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
    Thomas Dodds Rank 8 - Analytics Strategist

    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
    Uma Mahesh Rank 4 - Community Specialist

    Thanks for your support Thomas