# Sales report

Hello all,

We need a workaround (if exists, I hope so) to perform the following:

I'll explain myself with this example: we have two customers: A, who has 100 sales and B, who has 300. Now we want analyse the sales. So we go to Sales/SalesReport/Analysis/Customers.

The system layout would return:(by the way the sales amount column it is a system variable).

Customer Sales amount

A...............................100

B...............................300

Now we need to add a column which contains the customer sales %. So, executing the system layout we would like to see:

Customer Sales amount %sales

A...............................100 ......... 25 (=100 / 400 )

B...............................300 ......... 75 (=300 / 400 )

To obtain those values it is clear that we need the total sales amount. So, we add a second repetitve area (with only one repetitive area, it is imposible to obtain the %sales, because the total field has not been calculated yet).

This way the first repetitve area it is only used to obtain the total sales amount (400). And in the second one, we perform a formula to obtain the % sales.

OK. This should be work... But when executing the layout... :

A......... 300 100

B..........300 100

The problem here it is that the system variable stays the same (I suppose thats because its values have been executed in the first repetitive area).

So, my question is: Do you know if is there some system variable that contains the sales total and so use only one repetitive area?

Or do you have some idea to solve this?

Thank you very much for your responses!!

Vanessa

##### ERIC Lapouge replied

Hello,

Even it's true that a SQL query can't return directly the result of an operation on the returned data,

it's always possible to link two queries together (virtual views).

In your case, the following query will do the job:

SELECT I0.DocNum, I0.CardCode, I0.DocDueDate, I0.DocTotal, (I0.DocTotal / Total) * 100 AS Ratio FROM OINV I0 INNER JOIN ( SELECT ISNULL (MIN (I0.DocDueDate), '') StartDate, ISNULL (MAX (I0.DocDueDate), '') EndDate, SUM (I0.DocTotal) Total FROM OINV I0 WHERE ((I0.DocDueDate >= [%0] AND [%0] <> '') OR ([%0] = '')) AND ((I0.DocDueDate <= [%1] AND [%1] <> '') OR ([%1] = '')) ) V1 ON ((I0.DocDueDate >= StartDate AND StartDate <> '') OR (StartDate = '')) AND ((I0.DocDueDate <= EndDate AND EndDate <> '') OR (EndDate = ''))

If you copy and past this SQL statement in your favorite Query Manager,

you'll see that the calculation of the ratio is done by using the result given by the joined view.

The query can easily be modified to fulfill your needs.

Regards,

Eric