Top N filter, sort Alphabetically

[bkrav]

I have a report that shows the drug name (group 1), and a the sum of the quantity.

I need to show the top 25 quantites, but have the report sorted alphabetically.

I am using CR11 and pulling from an Access 2003 DB.

Thanks!

 

[lbass]

Create a command as your datasource:

Select top 25 table.`drug`,
(
select sum(A.`qty`)
from table A
where A.`drug` = table.`drug`
) as sumqty
From
(
Select distinct table.`drug`,
(
select sum(A.`qty`)
from table A
where A.`drug` = table.`drug`
) as sumqty
From table
Order by 2 DESC
)

Then in the report, go to report->record sort and add {command.drug} as your sort field.

-LB

 

[bkrav]

Thanks for your quick reply, but I am not completely following you. This is the command that I have -

SELECT RX.RX_Site_Code, RX.RX_Drug_Name, Sum(RX.RX_Qty) AS SumOfRX_Qty, Sum(RX.RX_Price) AS SumOfRX_Price
FROM Site INNER JOIN RX ON Site.Site_Code = RX.RX_Site_Code
WHERE (((RX.RX_Date) Between #7/1/2005# And #6/30/2006#) AND ((Site.Site_Roll_Up_Region)="new mexico"))
GROUP BY RX.RX_Site_Code, RX.RX_Drug_Name
ORDER BY Sum(RX.RX_Qty) DESC;

It imports the records the way I need (so I can only show the top 25), but I still cannot figure out how to have the drug names sorted alphabetically, but only showing the top 25 by quantity.

 

[lbass]

So currently you are using topN to display only the top25 or group suppression, right? But then you can't sort alphabetically. My suggestion returns only the top 25 so that there IS no topN sort, and you can then sort using record sort. Why not give it a try? This would be an alternative to your current command. You'd have to build in your where criteria into the subselects, and change the Order by to Order by 3, if you use the same field order as in your current command.

-LB

 

[bkrav]

Got it working.

This is the command I used-
select top 25 RX.RX_Site_Code, RX.RX_Drug_Name, Sum(RX.RX_Qty) AS SumOfRX_Qty, Sum(RX.RX_Price) AS SumOfRX_Price, RX.RX_Site_Code
FROM RX
GROUP BY RX.RX_Site_Code, RX.RX_Drug_Name, RX.RX_Site_Code
HAVING (((RX.RX_Site_Code)={?Site Code}))
ORDER BY Sum(RX.RX_Qty) DESC;

I didn't know that you could use top 25 in the command like that. Thanks!

 

[lbass]

Thanks for sharing your command, which is actually superior, because simpler (*).

-LB