BusinessObjects Board

Convert using values from another table


I use BO 6.5

I have a table with columns: weight and meassure

weight beeing the value and meassure what kind of unit.

150 pound
45 kg
4325 g

I have another table that have: From_Unit, To_Unit, and Multi_factor

LBS KG 0,453592

So if I have in my first table 5 LBS
I have to mutliply this by 0,453592 to get kg.

In my report I want all my values to be in kg.
I was hoping that something like this would help:

=<Multi_factor> where (<From_Unit> = )

If I hardcode “LBS” instead of it works fine.

Thank you in advance.

Skywise :norway: (BOB member since 2007-04-26)

The syntax of the Reporter WHERE clause does not support it. Can’t do it.

See this entry from our Reporter FAQ: Why do I get a Syntax Error (DMB0007) when I try to Sum() Where ( > 2000)?

The syntax has to be WHERE <variable/object> = constant.

The FAQ cited above suggests a work-around for one situation, perhaps you can figure out another for your case.

Alternatively, couldn’t you get the universe designed so that there’s an automatic join on the from_unit, so that you can pull in the correct to_unit and multi_factor objects?

Anita Craig :us: (BOB member since 2002-06-17)

Or to get around it in the report itself, link measure object from first query with from_unit (2nd query)
Then create a combined_measure variable as

= If <measure>="pound" Then "LBS" Else <measure> 

Apply break on combined_measure and use the below expression on the block footer to get value in kgs

= If <combined_measre>="LBS" Then <Multi_factor>*<Weight> Else <Weight> 

Show other column values on the footer and do a fold from format-breaks.

This is based only on the LBS/pound conversion to kgs.


haider :es: (BOB member since 2005-07-18)