Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » Trying to Sum the distinct values
Trying to Sum the distinct values [message #757339] Thu, 17 November 2011 14:41 Go to next message
Todd  is currently offline Todd
Messages: 4
Registered: November 2011
Junior Member
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 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

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 Go to previous messageGo to next message
Todd  is currently offline Todd
Messages: 4
Registered: November 2011
Junior Member
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 Go to previous message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

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.
Previous Topic:Oda Customer Connector setup
Next Topic:How to show total of stacked bars
Goto Forum:
  


Current Time: Sat Aug 02 00:51:48 EDT 2014

Powered by FUDForum. Page generated in 0.05850 seconds