05-19-10, 12:23 #1Registered User
- Join Date
- May 2010
Unanswered: Can/how sql sum returned rows then work out % of total on them?
I'm new to SQL programming (basic level) and I have come up against my first big challenge.
I am using a propriatetry product like MS reporting services to create an invoice.
Users enter an invoice into the system, specify the customer & invoice lines (each line can be tax exempt or assigned one of several tax codes).
Then they run the print process which queries the invoice header table and invoice detail table and retrieves all matching rows for that order/job number (created when you save your invoice entry).
The returned data is then inserted into the report template and produces a nice pdf invoice, showing customer details at the top, then listing the invoice lines showing tax amount, tax rate & net line total, at the bottom the tax is summed and an invoice total displayed.
Now, this is where my abilities end.
I now need to add a tax rate breakdown which breaks down the invoice by tax code and produces a percentage of the total attributable to that tax code. So that the customer can see how much they are spending in each tax bracket.
TC_____% of invoice total
My initial thinking was I could run two queries on the invoice detail table;
1. To return the total invoice amount
2. To return the total invoice amount grouped by tax code
Then to do a calculation which does 2/1 for each tax code returned.
But I can't figure out how to assign names to the grouped tax codes returned in the query AND then feed them into the calculation - if its possible to do with assigned field/column names.
So I would very much appreciate any help you can offer on this.
Is my thinking actually possible to do
Is my approach incorrect.
05-19-10, 12:32 #2Resident Curmudgeon
Provided Answers: 54
- Join Date
- Feb 2004
- In front of the computer
Computations like "percent of total" can be done by SQL Server or by the client software. Calculations like this are ugly, inefficient, and should not be done using SQL.
The exact method for doing this kind of calcuation varies based on the reporting product that you choose. Many of them actually include a function or a pre-calculated variable for "percent of total" without requiring you to calculate it.
Check the reporting tool documationation first. Post back here if you can't find an answer there. Knowing which tool you are using would help us answer your question, or direct you to other users of that tool. As a last resort, we can certainly help you to compute the "percent of total" using your reporting tool.
-PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.
05-19-10, 12:48 #3Registered User
- Join Date
- May 2010
Thank you for your reply.
The reporting application is Agresso Report Creator (packaged with Agresso 55) - which I can compare to being a very very cut down application styled on MS Reporting services. It doesn't have a formula builder/wizard funtion but you can write vb script in it as well as sql.
Problem is my vb knowledge is a half that of my SQL skills.
In vb I imagine you would pass the sql query results as params to vb and get it to do the % calculations and spit them out named as tc%0, tc%1, tc%2 and then insert them into the template.
Do you know the 'standard' vb functions names which do % of total calculations? - I could try entering them and see if they are recognised/run by our ARC tool.