Sort Outer Group by Inner Group!

[gogopoppy]

Hi there,

I had this question asked of me and I don't think its possible.

We have a report grouped by Sales Person and we have an inner group based on month. We see the best month for each salesperson (the innergroup has a summary and its set to show Top 1).

This works great - shows each salespersons top month. However if possible we would like to sort the salesperson (the outer group) by the Top month (the inner group). So the sales person with the highest top month revenue would show first.

Anybody had this problem before? Any suggestions would be very helpful as I don't even know where to start with this.

Thanks

G

 

[lbass]

Create a SQL expression similar to this:

(
select sum(`SalesAmount`)
from Sales A
where A.`Salesperson` = `Sales`.`Salesperson` and
month(A.`SaleDate`) = month(`Sales`.`SaleDate`) and
year(A.`SaleDate`) = year(`Sales`.`SaleDate`)
)

Place this in the detail section and then insert a maximum on it at the Salesperson group level. Then you will be able to use a groupsort on the SQL expression in descending order.

-LB

 

[gogopoppy]

Hi Lbass,

Tx for the reply.

I'm trying to understand the SQL statement - to figure out what its doing and I can't.
You have the sales table (in my case its the Orders table) as Table A - however then you have it linking to Sales again. Is the table linking to itself?

Sorry if I'm being dumb but I don't think I'm getting it.

G

 

[kskid]

Yes, it is.

 

[gogopoppy]

Thanks KsKid,

I'll have another look at it with this in mind and see if it makes more sense.

G

 

[lbass]

It is a method for creating a faux group within the subquery.

-LB