Ranking in Crosstab Query

[keyur1719]

Hi,

I have a crosstab query which is based on a simple select query. Here is how the query works..

The base query gets it date from employee table and their incentives table for the given quarter. This query feeds to the Crosstab query for displaying the quarterly data. I have a field in the crosstab query which calculates the sum of all three months in the quarter.

I want to give ranks to this quarterly total in the crosstab query. I tried using the rank function posted by someone on this forum with the subquery. But subquery doesnt seem to be working in the crosstab query.

Can anybody help.. its urget...

Thanks

Keyur

[MMcCarthy]

Hi Keyur

Please post the SQL of your crosstab query.

Also explain how you want the data ranked.

Mary

[keyur1719]

Hi Mary,

For the confidentiality reasons I can not give you the exact sql of my crosstab. However below given is the same syntax I am using except the query and column names.

TRANSFORM Sum(Query1.Coiu mn1) AS QuarterlyTotal
SELECT Query.Column2, Query1.Column3, Query1.Column4, Query1.Column5, Query1.Column6, Sum(Query1.Colu mn1) AS NetIncentives
FROM Query1
GROUP BY Query1.Column2, Query1.Column3, Query1.Column4, Query1.Column5
PIVOT Query1.Column6;

I have a query that fetches data of the incentives earned by each employee from the table in the vertical format for a given quarter, say it Query1.

I am then using Query1 to make a crosstab query, say Query2, in which I am showing incentives earned by each employee in monthly basis (i.e. horizontal format) and I am showing the total for the quarter in the last column. I want to give ranks to employees on this Quarterly total.

Looking forward for your quick reply..

Thanks

Keyur

[MMcCarthy]

Keyur

What kind of Ranks? Just 1, 2, 3, 4 ....

Or do you want to rank them in ranges like

High (>10,000)
Medium Between (1,000 and 9,999)
Low (< 1,000)

What kind of ranking are we talking about?

[keyur1719]

Keyur

What kind of Ranks? Just 1, 2, 3, 4 ....

Or do you want to rank them in ranges like

High (>10,000)
Medium Between (1,000 and 9,999)
Low (< 1,000)

What kind of ranking are we talking about?

Hi,

Ya just simple ranks like 1,2,3,4 ....

I want to show the top incentives earners in the particular quarter

thanks

Keyur

[MMcCarthy]

[CODE=sql]
TRANSFORM Sum(Query1.Coiu mn1) AS QuarterlyTotal
SELECT Query.Column2, Query1.Column3, Query1.Column4, Query1.Column5, Query1.Column6, Sum(Query1.Colu mn1) AS NetIncentives
FROM Query1
GROUP BY Query1.Column2, Query1.Column3, Query1.Column4, Query1.Column5
ORDER BY Sum(Query1.Colu mn1) DESC
PIVOT Query1.Column6;
[/CODE]
Will the Order by give you what you want ?

[keyur1719]

[CODE=sql]
TRANSFORM Sum(Query1.Coiu mn1) AS QuarterlyTotal
SELECT Query.Column2, Query1.Column3, Query1.Column4, Query1.Column5, Query1.Column6, Sum(Query1.Colu mn1) AS NetIncentives
FROM Query1
GROUP BY Query1.Column2, Query1.Column3, Query1.Column4, Query1.Column5
ORDER BY Sum(Query1.Colu mn1) DESC
PIVOT Query1.Column6;
[/CODE]
Will the Order by give you what you want ?

Hi,

Sorry for the late reply as was on leave for 4 days.

I tried your suggestion, it gives following error.


"Cannot have aggregare function in ORDER BY clause (sum(Query1.Col umn1))"

Regards

Keyur