Eclipse Community Forums: BIRT » Area Chart - Only show top 5 and summation of remainder

Home » Archived » BIRT » Area Chart - Only show top 5 and summation of remainder

Area Chart - Only show top 5 and summation of remainder [message #888828]

Mon, 18 June 2012 13:18

Eclipse User

Hi all,

I'm trying to create a percentage area chart or stacked bar chart that will show the grouped results of only the top 5 groups (ideally by the sum of the entire date range) and a summation of all other groups. Showing all the groups makes the chart illegible.

The input data is a variable number of groups and different group titles, depending on user selection. I currently have a chart that is using the Optional Y Series Grouping and sorting by quantum, but can't find a way to only show top 5 groups.

I'm using BIRT 3.7.2

All thoughts and ideas welcome!
Thanks for the help

Re: Area Chart - Only show top 5 and summation of remainder [message #888969 is a reply to message #888828]

Mon, 18 June 2012 16:51

Eclipse User

Have you tried to use a top N filter on the chart?

Jason

On 6/18/2012 1:18 PM, Ronan Murphy wrote:
> Hi all,
>
> I'm trying to create a percentage area chart or stacked bar chart that
> will show the grouped results of only the top 5 groups (ideally by the
> sum of the entire date range) and a summation of all other groups.
> Showing all the groups makes the chart illegible.
> The input data is a variable number of groups and different group
> titles, depending on user selection. I currently have a chart that is
> using the Optional Y Series Grouping and sorting by quantum, but can't
> find a way to only show top 5 groups.
> I'm using BIRT 3.7.2
>
> All thoughts and ideas welcome!
> Thanks for the help

Re: Area Chart - Only show top 5 and summation of remainder [message #888994 is a reply to message #888969]

Mon, 18 June 2012 17:24

Eclipse User

I have tried that and had some issues. My chart is set up as:
X-axis: dates
Y-axis: sales volume (integer)
Y-Grouping: product (string) (Sorted descending on sales volume)

I have the filter set up on Product - Top n - report parameter (integer)

However this always gives me a single Product category rather than top 5. I also tried it unsorted and get same result.

Will the top n filter also give me (or is it possible to include) a summation of all the categories that are not in the top 5? As we are looking at percentage area charts we would misrepresent the data otherwise, and it is an important element of the report.

Apologies if it is a rudimentary question, I'm new to the program!

Thanks again

Jason Weathersby wrote on Mon, 18 June 2012 16:51

Have you tried to use a top N filter on the chart?

Jason

On 6/18/2012 1:18 PM, Ronan Murphy wrote:
> Hi all,
>
> I'm trying to create a percentage area chart or stacked bar chart that
> will show the grouped results of only the top 5 groups (ideally by the
> sum of the entire date range) and a summation of all other groups.
> Showing all the groups makes the chart illegible.
> The input data is a variable number of groups and different group
> titles, depending on user selection. I currently have a chart that is
> using the Optional Y Series Grouping and sorting by quantum, but can't
> find a way to only show top 5 groups.
> I'm using BIRT 3.7.2
>
> All thoughts and ideas welcome!
> Thanks for the help

Re: Area Chart - Only show top 5 and summation of remainder [message #889253 is a reply to message #888994]

Tue, 19 June 2012 00:12

Eclipse User

This is most likely because the filter is happening before the
aggregation. Take a look at the topn example attached that aggregates
in the data set and then filters on the chart.

Jason

On 6/18/2012 5:24 PM, Ronan Murphy wrote:
> I have tried that and had some issues. My chart is set up as:
> X-axis: dates
> Y-axis: sales volume (integer)
> Y-Grouping: product (string) (Sorted descending on sales volume)
>
> I have the filter set up on Product - Top n - report parameter (integer)
>
> However this always gives me a single Product category rather than top
> 5. I also tried it unsorted and get same result.
>
> Will the top n filter also give me (or is it possible to include) a
> summation of all the categories that are not in the top 5? As we are
> looking at percentage area charts we would misrepresent the data
> otherwise, and it is an important element of the report.
>
> Apologies if it is a rudimentary question, I'm new to the program!
> Thanks again
>
>
>
> Jason Weathersby wrote on Mon, 18 June 2012 16:51
>> Have you tried to use a top N filter on the chart?
>>
>> Jason
>>
>> On 6/18/2012 1:18 PM, Ronan Murphy wrote:
>> > Hi all,
>> >
>> > I'm trying to create a percentage area chart or stacked bar chart that
>> > will show the grouped results of only the top 5 groups (ideally by the
>> > sum of the entire date range) and a summation of all other groups.
>> > Showing all the groups makes the chart illegible.
>> > The input data is a variable number of groups and different group
>> > titles, depending on user selection. I currently have a chart that is
>> > using the Optional Y Series Grouping and sorting by quantum, but can't
>> > find a way to only show top 5 groups.
>> > I'm using BIRT 3.7.2
>> >
>> > All thoughts and ideas welcome!
>> > Thanks for the help
>
>

Attachment: TopNPercent.zip
(Size: 56.79KB, Downloaded 438 times)

Re: Area Chart - Only show top 5 and summation of remainder [message #889257 is a reply to message #888994]

Tue, 19 June 2012 00:12

Eclipse User

Attachment: TopNPercent.zip
(Size: 56.79KB, Downloaded 287 times)

Re: Area Chart - Only show top 5 and summation of remainder [message #889261 is a reply to message #888994]

Tue, 19 June 2012 00:12

Eclipse User

Attachment: TopNPercent.zip
(Size: 56.79KB, Downloaded 242 times)

Re: Area Chart - Only show top 5 and summation of remainder [message #889265 is a reply to message #888994]

Tue, 19 June 2012 00:12

Eclipse User

Attachment: TopNPercent.zip
(Size: 56.79KB, Downloaded 269 times)

Re: Area Chart - Only show top 5 and summation of remainder [message #889787 is a reply to message #889253]

Tue, 19 June 2012 11:14

Eclipse User

Jason,

That makes sense and when I checked it is the issue. Unfortunately I'm complicating this as I'm working off a database join and need the extra column that would be aggregated out to do the join on.

Sounds like I'll need to find another approach to present the data.

Thanks for the help

Re: Area Chart - Only show top 5 and summation of remainder [message #889844 is a reply to message #889787]

Tue, 19 June 2012 17:43

Eclipse User

Ronan,

There are other ways around this all though it may involve a bit of
scripting. In fact you can replace all of the data a chart uses at run
time. I am not certain you want to do this. If you need some kind of
example let me know. BTW pie charts have min slice value that combines
smaller values into a "others" type category as well.

Jason

On 6/19/2012 11:14 AM, Ronan Murphy wrote:
> Jason,
>
> That makes sense and when I checked it is the issue. Unfortunately I'm
> complicating this as I'm working off a database join and need the extra
> column that would be aggregated out to do the join on.
>
> Sounds like I'll need to find another approach to present the data.
>
> Thanks for the help

Re: Area Chart - Only show top 5 and summation of remainder [message #889845 is a reply to message #889844]

Tue, 19 June 2012 17:48

Eclipse User

BTW another more elegant solution is to create a birt data cube off of
the data set and then drag the cube to the report. Ann a filter to it
and then convert it to a chart view. Take a look at the attached example.

Jason

On 6/19/2012 5:43 PM, Jason Weathersby wrote:
> Ronan,
>
> There are other ways around this all though it may involve a bit of
> scripting. In fact you can replace all of the data a chart uses at run
> time. I am not certain you want to do this. If you need some kind of
> example let me know. BTW pie charts have min slice value that combines
> smaller values into a "others" type category as well.
>
> Jason
>
> On 6/19/2012 11:14 AM, Ronan Murphy wrote:
>> Jason,
>>
>> That makes sense and when I checked it is the issue. Unfortunately I'm
>> complicating this as I'm working off a database join and need the extra
>> column that would be aggregated out to do the join on.
>>
>> Sounds like I'll need to find another approach to present the data.
>>
>> Thanks for the help
>

Attachment: TopNChart.zip
(Size: 3.98KB, Downloaded 294 times)

Re: Area Chart - Only show top 5 and summation of remainder [message #889846 is a reply to message #889844]

Tue, 19 June 2012 17:48

Eclipse User

Attachment: TopNChart.zip
(Size: 3.98KB, Downloaded 323 times)

Re: Area Chart - Only show top 5 and summation of remainder [message #890210 is a reply to message #889846]

Thu, 21 June 2012 09:20

Eclipse User

Jason,

A very elegant solution! Thank you for the help.

Jason Weathersby wrote on Tue, 19 June 2012 17:48

BTW another more elegant solution is to create a birt data cube off of
the data set and then drag the cube to the report. Ann a filter to it
and then convert it to a chart view. Take a look at the attached example.

Jason

On 6/19/2012 5:43 PM, Jason Weathersby wrote:
> Ronan,
>
> There are other ways around this all though it may involve a bit of
> scripting. In fact you can replace all of the data a chart uses at run
> time. I am not certain you want to do this. If you need some kind of
> example let me know. BTW pie charts have min slice value that combines
> smaller values into a "others" type category as well.
>
> Jason
>
> On 6/19/2012 11:14 AM, Ronan Murphy wrote:
>> Jason,
>>
>> That makes sense and when I checked it is the issue. Unfortunately I'm
>> complicating this as I'm working off a database join and need the extra
>> column that would be aggregated out to do the join on.
>>
>> Sounds like I'll need to find another approach to present the data.
>>
>> Thanks for the help
>

Re: Area Chart - Only show top 5 and summation of remainder [message #1058004 is a reply to message #890210]

Fri, 10 May 2013 09:08

Eclipse User

This has been very helpful, but I am still not sure how to add a piece to the pie chart showing the values of everything other than the Top N. How do I add in the script for that?

Re: Area Chart - Only show top 5 and summation of remainder [message #1058049 is a reply to message #1058004]

Fri, 10 May 2013 11:50

Eclipse User

So, you want to have a slice for each of the top N, then have a slice for "everything else" as well? What's your BIRT version?

Re: Area Chart - Only show top 5 and summation of remainder [message #1058052 is a reply to message #1058049]

Fri, 10 May 2013 12:09

Eclipse User

Yes, that is exactly what I want. I found another post that explained how to change the minimum slice size with a script, but I cannot figure out how to get the size of the value from the crosstab of the Nth value.

I tried doing this in the crosstab:
function onCreateCell( cellInst, reportContext )
{
if( cellInst.getCellType() == "measures")
{
importPackage(Packages.java.lang );
var cur = cellInst.getDataValue("Incident Number");
if( reportContext.getGlobalVariable("min_inc")==null)
reportContext.setGlobalVariable("min_inc",cur);
}
}

But, I do not get any value in min_inc.

I am using BIRT version 2.5.1.

Re: Area Chart - Only show top 5 and summation of remainder [message #1058177 is a reply to message #888828]

Mon, 13 May 2013 00:52

Eclipse User

Take a look at this report.