Displaying Top N subtotal as well as grand total

I am currently migrating Crystal Report XI reports over to the
Reporting Services and came across a problem.
The Crystal Report displays Top N records, subtotal of these Top N
records, and the grand total of all the records. SQL stored procedure
underneath returns all the records.
>From what I have been reading in online groups and helps, these types
of features are not directly supported in the Reporting Services. The
workaround I have found so far involves returning two sets of results
("Top N" results and "All" results) and combining the two result
sets.
Since some query takes up significant amount of time, I would like to
avoid calling the stored procedures twice if necessary. Does anyone
know how I can avoid this and work with just one result set?
Thanks in advance.
ShoheiBring all the records and use filter "Top N" to filter for Top N records only.
Amarnath [MVP]
"Shohei.Yamauchi@.gmail.com" wrote:
> I am currently migrating Crystal Report XI reports over to the
> Reporting Services and came across a problem.
> The Crystal Report displays Top N records, subtotal of these Top N
> records, and the grand total of all the records. SQL stored procedure
> underneath returns all the records.
> >From what I have been reading in online groups and helps, these types
> of features are not directly supported in the Reporting Services. The
> workaround I have found so far involves returning two sets of results
> ("Top N" results and "All" results) and combining the two result
> sets.
> Since some query takes up significant amount of time, I would like to
> avoid calling the stored procedures twice if necessary. Does anyone
> know how I can avoid this and work with just one result set?
> Thanks in advance.
> Shohei
>|||If you are using SQL Server 2005, you can use ROW_NUMBER () in a Data
set so that you can get top N