Top-n query in QBE

Hello,
I cannot find the right way to define a top/bottom-n report (such as Top 10 Customers by Sales). Can anybody point me in the right direction?

Thanks in advance.

This message was edited 2 times. Last update was at 03/02/2012 10:07:26

Hi,
actually this feature is not available..
It’s not so hard to write it (I’m thinking something like, order the data and use the Limit clause), if you want to change the code I can tell you where to act..

If you are using a db that provides the ruwnum column (like Oracle) you can write a calculated field with “rownum” as expression, put in on the select clouses and apply a filter.

Let me know..

Hello Alberto,
Thanks for your reply. Can you please provide more details for both suggestions (code change and rownum on query)?

Thanks in advance.

Hi,

For the calculate fields way, you should:
1) create your query create
2) add calculated field in the select clause
2a) “Add calculated”
2b) Set Alias, type=number, Expression “rownum”
3) put the field in the filter and set the filter (<10)

You should pay attention. The goodness of this solution depends on the query (take a look at this article http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html )

The code way is a little bit tricky.. It’s not hard to code, but you should have time to understand the code structure of SpagoBIQbeEngine..

Hi Alberto,
Thanks for your reply, however unless I am mistaken this is not what I am looking for. Adding the rownum calculated field prevents aggregation from happening on the measure; instead it returns the rownum of the individual fact record before the aggregation took place. So it is not usable for queries such as "Top 10 Customers by Sales" where "sales" is a measure using an aggregate function.

If I am doing this wrong, please let me know.

Thanks in advance.

This message was edited 2 times. Last update was at 14/02/2012 13:54:16

No you are right..

You can use the trick of the rownum only if you are not using aggregation functions…

To do what you are asking for you need something like this:

select rownum, A, B from(
select sum(store_cost) as A, promotion_id as B
from sales_fact_1998
group by promotion_id)

but with the QBE you can’t use a subquery in the from clause…

Why this functionality still absent in spagobi 5,2? It's been like this for 4 years and nothing has changed. You can't make any kind of top xy records with query, what is a big limitation in using QBE.

Hey Friends, Here you will be able to convert you youtube vides to mpe3 songs with the help of myglobalclip this website. Songs converted can be listen offline and you can have great entertainment. The problem of high speed web connect is also over by converting videos to mp3 songs. Try out this mp3 converter for once.