I'm not sure if you want to do this in sql or in rs.
In sql you could wrap another statement around your existing one:
select top 5 FaultCodeID,count(*) as count
from
(
.... your sql ...
) as query
group by FaultCodeID
order by count desc
Otherwise if the sum is already in your report, you should be able to
use the Top N
in the group properties.
HTH
Jim
[quoted text, click to view] Benw wrote:
> I have been frustrated with this for weeks. I have a report that is grouped
> by type with a sum of all the types that happened in a certain period.
> like
>
> carpet 34
> drywall 31
> siding 29
> etc...................
>
> I have a whole list grouped by that type and sorted by the number or (Count)
>
> SELECT smServCall.ServiceCallID, smServCall.ShiptoId,
> smServFault.FaultCodeId, smServFault.Crtd_DateTime, smServCall.CallStatus,
> smServCall.CallType
> FROM smServCall INNER JOIN
> smServFault ON smServCall.ServiceCallID =
> smServFault.ServiceCallId
> WHERE (smServCall.CallType = 'CLOSING')
>
> PS.....FaultCOdeId is my type
> I cant figure out how it can give me just the types with the 5 highest
> counts. I know it is Top(N) but where and how I cant figure out. I will end
> this with my datasert query in hopes of some help.. Thanks
How would I do that in my group properties. I have a sort descending of
countdistinct(servicecallID.value) My group is the faultCode. I just cant
figure out the the syntax to use in an actual formula in reporting services
for Top(N)
[quoted text, click to view] "jhcorey@yahoo.com" wrote:
> I'm not sure if you want to do this in sql or in rs.
> In sql you could wrap another statement around your existing one:
>
> select top 5 FaultCodeID,count(*) as count
> from
> (
> .... your sql ...
> ) as query
> group by FaultCodeID
> order by count desc
>
> Otherwise if the sum is already in your report, you should be able to
> use the Top N
> in the group properties.
>
> HTH
> Jim
>
> Benw wrote:
> > I have been frustrated with this for weeks. I have a report that is grouped
> > by type with a sum of all the types that happened in a certain period.
> > like
> >
> > carpet 34
> > drywall 31
> > siding 29
> > etc...................
> >
> > I have a whole list grouped by that type and sorted by the number or (Count)
> >
> > SELECT smServCall.ServiceCallID, smServCall.ShiptoId,
> > smServFault.FaultCodeId, smServFault.Crtd_DateTime, smServCall.CallStatus,
> > smServCall.CallType
> > FROM smServCall INNER JOIN
> > smServFault ON smServCall.ServiceCallID =
> > smServFault.ServiceCallId
> > WHERE (smServCall.CallType = 'CLOSING')
> >
> > PS.....FaultCOdeId is my type
> > I cant figure out how it can give me just the types with the 5 highest
> > counts. I know it is Top(N) but where and how I cant figure out. I will end
> > this with my datasert query in hopes of some help.. Thanks
>
Don't see what you're looking for? Try a search.
