Measure object

[Former Member]

Hi All,

I would like to create measure object in my view.

I would like to create measure with top 25 row data.

I have 3 columns in my table like Month/year, tcode,sum and I would like to see only top 25

Could any one pls help how to create the measure fo this.

Thanks

Muvva

[Former Member · Accepted solution]

Hi,

Whate are the BI products you are using (WebI, Crystal, other)? What is the database? Which modeling tool (Universe, Business View, other)?

You provided too few information to help you.

Didier

[Former Member · Accepted solution]

Hello Didier,

I am using the BOBJ 3.1, xcelsius 2008.

my database is Oracle 9i and using the Universe.

Thanks

Raghav

[Former Member · Accepted solution]

Hi,

You can define a predefined filter that uses Oracle features like this in your universe:

ROWNUM <= @Prompt('Enter value for Top n:','N',,,)

To be used correcetly, you need to have the ROWNUM restriction applies to the whole query including all GROUP BY to be sure to retrieve onlly the top n (aggregations must be applied prior to this restriction).

The best is to build a derived table that contains the query you want and then use this predefined filter on the result of this derived table, like this:

SELECT t.a, t.b, t.n FROM
(SELECT a,b, sum(n) FROM table WHERE .... GROUP BY a, b) t
WHERE ROWNUM <= @Prompt('Enter value for Top n:','N',,,)

I used this feature several times in the past.

Didier