on 09-15-2008 7:48 AM
Hi Guys
I am doing a Crystal Report that will provide the total # of invoices per sales employee, the total value of these invoices as well as the total # of credit notes and total value of these grouped per Sales Employee. The problem i am having is that the credit note repeats for each single invoice, so if there is 10 invoices then the credit notes will repeat for invoice number 1 till 10.
Another issue is that i want to report on the document owner on the same invoices and credit notes, so i will have a duplicate report that will have the same fields as above just using Doc Owner instead of Sales Employee. I have referenced the OHEM first name as the Field that i group by for Doc Owner as the Doc Owner returns a numeric value from OHEM called EMPID, so i use this to return the first name.
Please advise as to what needs to be done in order to get this report to work, i use it to calculate Sales Person commission where they are the Owner and Sales Employees of invoices and credit notes.
Thank You
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Euston
My suggestion would be to run 2 separate select statements for invoices and credit notes. Then you can either use a union (UNION ALL) or alternatively use a stored procedure and place the result set from both into a temp table and run the final select from the temp table.
Let me know if you need assistance with the stored procedure, but first try a union. Inner and outer joins can be a little tricky especially when using GROUP BY.
Kind regards
Peter Juby
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
91 | |
11 | |
10 | |
6 | |
5 | |
5 | |
5 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.