Crystal Report - Sales Commission

[Former Member]

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

[Former Member · Accepted solution]

Hi Euston

Have a look at this thread I replied to yesterday for an example of writing to Temp tables through a Stored Procedure.

Let me know if you need further assistance with this.

Kind regards

Peter Juby

[Former Member · Accepted solution]

Thanks Peter

I will check this today and get back to you, much appreciated.

[Former Member]

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

[Former Member]

Hi Peter

Thanks For The Response

i have tried the union all and this also doesnt seem to work 100%, please advise as to the use of the temp tables and how this is done? Thank You