Top-N Report with Sorting

Hi, I am fairly new to BIRT so forgive me if this is a newbie question.

I am using a custom datasource (a lucene query) and a scripted dataset, and I need to generate a Top-N report from it. I can do this. However, the ranking of the N groups seems random. I would like the results to be ranked from top n to bottom n.

The way I have set up my top n report is as follows:

- I created a group, and I'm grouping on the column I want top n results for
- I created a count aggregation for the same column, and I put the aggregation in the same row as the group
- I added a top n filter to the group to filter by the aggregation
- I removed all other rows from the report so that all I see is the group

Problem: the Sort tab in the group doesn't give me the aggregation as a sort option.

Any help would be appreciated.

Find more posts tagged with

Comments

mwilliams

Hi jsinai,

What is your BIRT version? Also, do you think you could recreate your issue with the sample database and post it in here?

jsinai

I'm sorry for the late reply, but it took me a while to recreate my issue using the classic cars database.

I've attached a report design that illustrates the problem.

I can generate a top n report that shows the number of products for each product line, but I can only sort by product line, not by the number of products.

The results you will see in the attached design are:

Classic Cars 38
Motorcycles 13
Vintage Cars 24

What I want is:

Classic Cars 38
Vintage Cars 24
Motorcycles 13

Please note that my sql query does not include grouping or sorting. This is intentional, as my real data source and scripted dataset don't support those features.

Any help is appreciated.
Julian

mwilliams

If you go into your group editor and put the sorting key there rather than the sort key you have in there, it should work.

jsinai

Thanks very much, I get it now.

mwilliams

You're very welcome! Always glad to help. Let us know whenever you have BIRT related questions!

vijayshankar245

<blockquote class="ipsBlockquote" data-author="mwilliams" data-cid="73151" data-time="1297288355">
<div>
You're very welcome! Always glad to help. Let us know whenever you have BIRT related questions!

</div>
</blockquote>
Hi
 
I have a similar requirement to show top 5 totals based on a group column. 
I have group like A to z and I have computed two columns like Column 1 and Column 2 aggregated on Group and then I have calculated the total (Column 1 + Column 2).
I have given sorting option in the group as total top n = 5
Also i have filtered the group on total desc. But I didnt get the desired results all I'm getting is blank output,
I want output as below,
 
Group Column1 Column 2 total
 
A 1000 1000 2000
G 980 1000 1980
Z 980 920 1900
E 950 900 1850
H 500 700 1200
 
 
Please help if i have missed anything. I'm using BIRT 4.4.0

pricher

Hi,
 
In the attached example, I have a table grouped on COUNTRY with 2 aggregations (AggregationCol1 and AggregationCol2) and their Total:

vijayshankar245

<blockquote class="ipsBlockquote" data-author="pricher" data-cid="144643" data-time="1468935771">
<div>
Hi,
 
In the attached example, I have a table grouped on COUNTRY with 2 aggregations (AggregationCol1 and AggregationCol2) and their Total:
 
<img src="http://developer.actuate.com/community/forum/public/style_images/developer_actuate_images/attachicon.gif" alt="attachicon.gif"><a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?app=core&module=attach&section=attach&attach_rel_module=post&attach_id=13469'>Snap 2016-07-19 at 09.36.57.png</a>
 
The filtering and sorting needs to be added to the COUNTRY Grouping properties, as shown here:
<img src="http://developer.actuate.com/community/forum/public/style_images/developer_actuate_images/attachicon.gif" alt="attachicon.gif"><a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?app=core&module=attach&section=attach&attach_rel_module=post&attach_id=13470'>Snap 2016-07-19 at 09.39.40.png</a>
<img src="http://developer.actuate.com/community/forum/public/style_images/developer_actuate_images/attachicon.gif" alt="attachicon.gif"><a data-ipb='nomediaparse' href='http://developer.actuate.com/community/forum/index.php?app=core&module=attach&section=attach&attach_rel_module=post&attach_id=13471'>Snap 2016-07-19 at 09.41.10.png</a>
 
Hope this helps,
 
P.
</div>
</blockquote>
Thanks PRicher. But in your example you have used dataset.
we are using linked data model in our report, so the grouping top N option is not working. Getting the attached error. Please help if you know a solution for a linked data model.

pricher

Hi,
 
Can you send your data design and report design?
 
Thanks,
 
P.

vijayshankar245

<blockquote class="ipsBlockquote" data-author="pricher" data-cid="144671" data-time="1469022181">
<div>
Hi,
 
Can you send your data design and report design?
 
Thanks,
 
P.
</div>
</blockquote>
Thanks Pricher
 
I used a filter IsTopN column is true in the Edit group instead of TopN Column less than or equal 5. It works now.