[Logo] SpagoWorld Forums
  [Search] Search   [Recent Topics] Recent Topics   [Hottest Topics] Hottest Topics   [Members]  Member Listing  
[Groups] Back to home page  [Moderation Log] Moderation Log   [Register] Register /  [Login] Login 
Top-n query in QBE  XML
Forum Index » Technical Help - SpagoBI Server - FREE INQUIRY
Author Message
spagalo.spw

Newby

Joined: 01/02/2012 06:18:23
Messages: 12
Offline

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.
aghedin

SpagoBI hero
[Avatar]

Joined: 14/07/2010 09:25:00
Messages: 465
Offline

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..

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


Alberto Ghedin

~~~ sis felix ~~~
spagalo.spw

Newby

Joined: 01/02/2012 06:18:23
Messages: 12
Offline

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

Thanks in advance.
aghedin

SpagoBI hero
[Avatar]

Joined: 14/07/2010 09:25:00
Messages: 465
Offline

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..

Alberto Ghedin

~~~ sis felix ~~~
spagalo.spw

Newby

Joined: 01/02/2012 06:18:23
Messages: 12
Offline

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.
aghedin

SpagoBI hero
[Avatar]

Joined: 14/07/2010 09:25:00
Messages: 465
Offline

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…

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


Alberto Ghedin

~~~ sis felix ~~~
martinmolnar.spw

Newby

Joined: 29/09/2016 12:41:45
Messages: 21
Offline

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.
vishal23.spw



Joined: 21/11/2017 06:42:01
Messages: 6
Offline

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.
 
Forum Index » Technical Help - SpagoBI Server - FREE INQUIRY
Go to:   
Powered by JForum 2.1.9 © JForum Team