Sorting a Drill Down Report by summary values in Crystal 2008
I'm a newbie to 2008 but have been using Crystal 7.0 for years, so am trying to find the best 'new' way to represent report data I have.
The data consists of various Product details (code, description, colour, size, Category, Sub Category) and Sales details (Units Sold, Net Sales, Gross Profit) for each Product, across several stores.
I looked at the drilldown example report, and this looks to be what I need.
I've got the drill down working, but am having trouble sorting it. What I want to do is show the N best sellers (decided by the user) across all stores, by either Net Sales Amount or Units or Gross profit, as buying for the stores is done centrally.
The data is retrieved from a stored procedure on a SQL 2005 database. Whatever I do, I can't seem to get it to order correctly. I can get the top 10 best sellers by Net Sales using the strred proc code, but they don't show by order in the report. What I need to do is sort by the sum of Net sales, but can't work out how to do this as it appears not to let you select a sum in the sort experts.
Alternatively, a cross tab that spanned a couple of pages would probably work, but I can't find any way of getting the Product details in one row without playing around with a concatenated string i.e. I want code, description, colour, size, Category, Sub Category as one row of the tab, with columns of Units Sold, Net Sales, Gross Profit as columns for each store. I am having the same problem with the cross tab as well - I want to order the rows by the Net Sales total across all stores (i.e. Product with most sales at the top), but can't work out how to do it as the report orders it by Product Name.
Can anyone point me in the direction of a sample or give me some clues ?