PDA

View Full Version : invoice line gross/net formula calculation



joebordes
04-18-2009, 08:35 PM
Hi,

I must have what is the most common metadata problem there is but I can't seem to get the formula correct.
I have an invoice line table which contains quantity and unitprice. There is no gross nor net totals. So I import the lines table and add two new fields. After various tries, I define them as:
-----------------
name: gross
aggregation: none
type: numreric
length=precision:-1
fieldtype:Fact
formula:[quantity]*[listprice]
is exact:true
-----------------
name: net
aggregation: none
type: numreric
length=precision:-1
fieldtype:Fact
formula: ([quantity]*[listprice])-COALESCE([discount_amount];COALESCE([discount_percent]*[quantity]*[listprice]/100;0))
is exact:true
-----------------

My first surprise was that I could not "reuse" the previously defined fields, for example, the net field would be easier like this:
([gross])-COALESCE([discount_amount];COALESCE([discount_percent]*[gross]/100;0))

But it's ok and it works inside the metadata editor (query editor)

Now on the business view I import this table, setup the relations and export the business view but when I choose any of the calculated fields in the adhoc reporting I get a mysql error about wrong syntax "[bc_gross]" and similar names which correspond to the business view. As if the brackets weren't being eliminated or something and the names in the BV were being used instead of the physical column names.

I have tried various combinations of field names, "formula exact" values and a bunch of other things that have come to my mind but I can't seem to find what I am doing wrong.

At the moment I have added a VIEW directly into the database and work with that but I would like to know what I am doing wrong.

Thanks.

wgorman
04-28-2009, 01:27 AM
Hello,

The field name should appear in the query model that ad hoc generates like [BIZCAT.bc_gross], somehow the category (view) isn't being included in the model. Could you post the query model xml (MQL), which is embedded in the .waqr.xaction file, that might shed light on the problem. It may be a bug in ad hoc.

Thanks,

Will