Eclipse Community Forums: BIRT » Trying to Sum the distinct values

Home » Archived » BIRT » Trying to Sum the distinct values

Trying to Sum the distinct values [message #757339]

Thu, 17 November 2011 14:41

Eclipse User

New to this and need some help. Does anyone have a way to total the distinct values from a column of a data set in the report.

sample data

Date Amount Budget(this is a montly budget)

5-11-2011 $50 500
5-12-2011 $65 500
5-14-2011 $100 500
6-12-2011 $20 600
6-13-2011 $30 600

I need a way to take that monthly budget and put together a Year to date budget. The data set links the month budget to each daily transaction so it is repeated 1000's of times a day. I know I could put in a new data set and filter it, but that will not work with the overall report I have. I need to use this data set and figure out a way to get the YTD Budget total calculated.

Re: Trying to Sum the distinct values [message #757833 is a reply to message #757339]

Fri, 18 November 2011 11:59

Eclipse User

In your example data points what do you want summed? All the fields or
sum all for one day?

Jason

On 11/17/2011 2:41 PM, Todd wrote:
> New to this and need some help. Does anyone have a way to total the
> distinct values from a column of a data set in the report.
>
> sample data
>
> Date Amount Budget(this is a montly budget)
>
> 5-11-2011 $50 500
> 5-12-2011 $65 500
> 5-14-2011 $100 500
> 6-12-2011 $20 600
> 6-13-2011 $30 600
>
> I need a way to take that monthly budget and put together a Year to date
> budget. The data set links the month budget to each daily transaction so
> it is repeated 1000's of times a day. I know I could put in a new data
> set and filter it, but that will not work with the overall report I
> have. I need to use this data set and figure out a way to get the YTD
> Budget total calculated.
>

Re: Trying to Sum the distinct values [message #758036 is a reply to message #757833]

Mon, 21 November 2011 14:37

Eclipse User

I need to SUM the Budget Column to get a YTD Budget Total. The main issue is that the Budget column is the monthly budget total not daily and the transactions are run through daily (could have 1000 a day). The monthly budget shows up 1000 times a day so I can't just sum that column.

Re: Trying to Sum the distinct values [message #758111 is a reply to message #758036]

Mon, 21 November 2011 16:56

Eclipse User

Todd this can be done by first adding a group for month (you can hide
the header and footer so they do not show up in the output). Then use
an aggregate on the group to aggregate MAX value for the budget column
for the month and finally add a final aggregate that sums up all the MAX
aggregates. Take a look at the attached example. I added a computed
column to the bindings to figure out the month first, then grouped, then
added first agg, then the budget total. Finally I hid the group header
and footer using visibility expression.

Jason

On 11/21/2011 2:37 PM, Todd wrote:
> I need to SUM the Budget Column to get a YTD Budget Total. The main
> issue is that the Budget column is the monthly budget total not daily
> and the transactions are run through daily (could have 1000 a day). The
> monthly budget shows up 1000 times a day so I can't just sum that column.

Attachment: distinctmonth.zip
(Size: 1.96KB, Downloaded 333 times)

Previous Topic:	Oda Customer Connector setup
Next Topic:	How to show total of stacked bars

Goto Forum:

-=] Back to Top [=-

Current Time: Wed Jul 23 02:24:23 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter