Top N function not returning n rows

Hi,

I have 11 rows of data in database, and when i put a filter on a graph in the report (birt 2.3.1) to return Top 10 rows based on a count, the report only ever returns 8 rows. And these aren't even the Top 8 - it returns some with highest counts, some with the lower counts, and a few missing in between.

The sql statement in data set is returning everything, then i have two filters in graph gui - one to restrict on dates (so that only the 11 records are counted), and then the filter on Top 10. I tried using only 8 records in dataset, and instead the report would only ever show 6 records. If i choose function 'Bottom 10' it showed the missing 2 records, but no other records.

I've searched the forum and internet and can't find anything highlighting what the issue is. It's very odd. Can anyone shine some light on this please?

Find more posts tagged with

Comments

thuston

You do only have 8 unique counts.
What are you charting, because the chart does not match your counts at all?

ayoung

That's the really odd thing - the report is definitely using those records as it's dataset - i can see those rows shown in attachment exactly, when i click Preview Results in the Edit Data Set gui. Its when this dataset is restricted that the counts all change and doesn't return all the rows.

The dataset contains data from other dates also, and i've only shown the data for the dates being filtered on.

Does BIRT have a precedence for which order it filters data in? I'm wondering if it may be sorting and taking Top 10 records, then applying date filter which would reduce rows to 8. But then this doesn't explain the muddled up counts....

ayoung

Ok so i've now moved the date filter into the the dataset query, and this restricts correctly and shows correct counts.

The Top 10 filter is still in the Edit Chart gui, and is now showing 10 rows for the 11/08/2010.

However, all the counts are wrong. For rows 10 to 6, it's subtracted 1 from the count, for row 5 it remains correct, and rows 1-4 have count of 1 added to their counts.

Anyone come across this before?

ayoung

So people know the outcome, the way i managed to get this to work was to move the Top N functionality into the dataset rather than in the chart.

I have no idea why it wouldn't work in the chart, but least now its all working correctly.