BusinessObjects Board

Newbie - Help needed in rolling up totals

building reports desktopintelligence
#1

Hi there,
I need some help in trying to summarise some data.

Here’s what I have…

Product Account Line Price O/S Qty Value
A ABC £5.00 5 £25.00
A DEF £5.00 5 £25.00
When I then start to remove some of the columns to start summarising the data (e.g. the account no) we get some understandable problems…

Product Account Line Price O/S Qty Value
A REMOVED £10.00 10 £100.00

Not sure if it’s clear, but the line price doubles & then calculates the total value by multiplying the line price by the o/s qty - this is incorrect.

One option I came up with was to count the number of lines of a product then divide the total value by the number of lines. This works, but when you have a product being sold at different prices it doesn’t!

Any ideas how I could get around this.

I hope I’ve given enough detail.

Cheers

Achillies (BOB member since 2010-08-06)

#2

A) Quantity as well as Value should be defined in the universe as proper measures using the SQL aggregate function SUM, see here for details.
B) Price should not be a measure, but a dimension object instead

Asking your universe designer to fix it would be the best solution.

Andreas :de: (BOB member since 2002-06-20)

#3

Thanks for getting back to me. I’ve got in touch with IT again & they agree about the resolution coming from the original data. They’re going to see what they can do.

Appreciate your response.

Achillies (BOB member since 2010-08-06)

#4

You are welcome.

Andreas :de: (BOB member since 2002-06-20)