Doing math in a Report

Details of workarounds and useful tips not found in the main documentation

Doing math in a Report

Postby alvin » Thu Jul 05, 2012 12:50 am

So I have this query, it is a price list,
Code: Select all
SELECT inventory.sku sku, inventory.description_short description_short, ROUND(inventory.full_price * .12 + inventory.full_price * 7.86,2) AS Price
FROM inventory
 JOIN inventory_history
    ON inventory.sku = inventory_history.sku
WHERE 1 = 1     [ AND store_id IN ({branch,VALUE}) ] 
ORDER BY  inventory.sku ASC


But I would like to break out the math functions, give the user a drop down for the tax rate, and currencies. In the query above currency is 7.86 and tax is 12%

Tax rate info is here:
The name, 'tax_rates.description_short'
The rate, 'tax_authorities.tax_rate'
The join: tax_authorities.tax_auth_id = tax_rates.rate_accounts

How can I put these math functions into criteria?
Alvin
alvin
 
Posts: 45
Joined: Tue May 15, 2012 1:47 pm
Location: USA, Guatemala, Honduras

Re: Doing math in a Report

Postby peterdeed » Thu Jul 05, 2012 6:48 pm

Hi Alvin

Im not sure where the currency comes from .. can you give me more info ...

But for tax you would certianly have a criteria based on something like :-
Code: Select all
SELECT tax_rates.description_short name, tax_authorities.tax_rate rate
FROM tax_autorities INNER JOIN tax_authorities.tax_auth_id = tax_rates.rate_accounts
WHERE 1 = 1


then youd probably have to go into presqls

at a guess, If the users selects a single tax value and a single currency from the drop downs (basedon the tax one abvoe and another currency one) then this can be fed into PreSQLs...
I think you can create a temp table containing the tax rate and populate this with including the users selection by using a similar SQL to the one above but including the users criteria with something like [ AND tax_rate = {tax_rate} ].
The same goes for another table for the currency

Then i guess you have two two temporary tables each with a single line.. then you just join these tables in to the main query without any "where" joins and then use the columns from these tables in the math

The tax_rates table doesnt exist in my phreedom maybe i havent set anything up

Peter
peterdeed
 
Posts: 1828
Joined: Thu Oct 13, 2011 11:00 pm

Re: Doing math in a Report

Postby alvin » Thu Jul 05, 2012 11:53 pm

Im not sure where the currency comes from .. can you give me more info ...

Sorry, currencies.value has the rate, currencies.code has the three digit currency code, it is good enough for a currency name.

The tax_rates table doesnt exist in my phreedom maybe i havent set anything up

'tax_authorities.tax_rate' has the rate. 'tax_rates.description_short' has the name to use.

For what it is worth I will send you some tables...

Thanks

Alvin
Alvin
alvin
 
Posts: 45
Joined: Tue May 15, 2012 1:47 pm
Location: USA, Guatemala, Honduras

Re: Doing math in a Report

Postby alvin » Fri Jul 06, 2012 4:25 pm

Well Peter, I can not quite make this work,

Having a drop down for the tax is not so good, the tax can not be tied to a branch, yet anyway.

You want to print a price list for a branch, because not all branches carry the same products. You might want to print a list including tax or not. A radio button would be better, add tax or not.

For now I think it best to just go in to db and add the entry by hand to contact.tax_id, then you could pull this like:
Code: Select all
SELECT tax_id
FROM contacts
WHERE contacts.type = b
[ AND short_name IN ({branch,VALUE}) ]

or something


Anyway I can add criteria, and make temp tables, but I do not know where to do the math?

Sorry...
Alvin
alvin
 
Posts: 45
Joined: Tue May 15, 2012 1:47 pm
Location: USA, Guatemala, Honduras


Return to Tips, Tricks and Hints

Who is online

Users browsing this forum: No registered users and 2 guests

cron