PDA

View Full Version : business view formula problem



begunrom
02-14-2008, 06:51 AM
In the business view i need a calculated column. The formula is easy, TableA.fieldA * TableB.fieldB.

In the view, i cannot define this relationship (or at least i do not know how). How can i achieve this?

begunrom
02-19-2008, 09:56 AM
I want to illustrate my problem by using the standard metadata.xmi as delivered with pentaho 1.6.2.GA.
I made a business view "Order margin", this is a copy of business view "Order" where i added the "Buy Price" field out of the products table.

The goal is to have a column margin, that is OrderDetail.Price Sold - Products.Buy Price

How can this be modeled in Metadata?

MattCasters
03-02-2008, 04:33 PM
Gunther,

The formula syntax used is the one from open office.
As such square brackets go around the table column names:

"[TableA.fieldA] * [TableB.fieldB]" should do the trick in Calculation / Formula / Value.

If you specify it as :

"SUM( [TableA.fieldA] * [TableB.fieldB] )", make sure to check the "Is the formula exact?" option.

HTH,

Matt

MattCasters
03-17-2008, 07:32 PM
There was a problem with these multi-column formulas alright. It only got fixed recently...

http://wiki.pentaho.org/display/PentahoDoc/02.+Pentaho+Metadata+Formulas

Matt

begunrom
03-18-2008, 07:22 AM
Matt,
Thanks for the additions.
What problem has been fixed? Was it related to the SQL that was generated? (From clause incomplete?)

MattCasters
03-18-2008, 07:56 AM
Gunther, that is correct. The formula of a business column used to be tied to a single business table and as such to a single physical table.
That is obviously not the way to go. All involved tables needed to be determined from a given formula expression. That way, they will all appear in the from clause and the correct join conditions can be set.

Matt