Hi I am creating a report in SQL Server reporting services 2005. My report is reporting on 2 parameters: Quantity sold, Cost Price. This data is fetched for top 30 items sold (sum of Cost price in descending order). Now the data is fetched for a whole calendar month. Then for these 30 items, the report needs to show historic data for calendar month immediately preceding this reported month. Also report needs to show historic data for same month last year. e.g. For Oct-09 , items A-z were sold (sum of cost price desc order). Now my report will show for these 30 items, the quantity sold and the sum of cost of these items (grouped by item name) for Oct-09, Sep-09 and Oct-08.
Now i have used a matrix control with following specs- with row grouping = item name....filter = sum(cost price) top30.... sorting = sum(cost price) desc
column grouping1 = year(solddate) ....Filter = 2008 and 2009 column grouping2 = month(solddate) ...Filter = Oct and Sep column grouping3 (static) = quantity sold and cost price
The problem i am having is the report is selecting all items in the table and not just the top 30 (based on sum of cost price in Oct09).
Also, I am not sure if i can use a top 30 criteria in the dataset itself, because this is what i need in my final output- 1) Based on sum of cost price, I need top 30 items and the quantity sold 2) For these 30 items, i need to find the 'sum of cost price' the preceding month and same month last year.
So this means that I need 3 sets of data from this 1 dataset and hence I dont think i can use top 30. So i currently fetch all the data in the dataset, without any grouping. I then group it into 3 seperate sets of data (for required months) in the matrix control.
asked Nov 16, 2009 at 08:14 PM in Default
I would use a query along the lines of:
You may prefer to use a JOIN over the IN clause here, but SQL should understand that these are identical, and I think the IN clause wins for understandability reasons.
answered Nov 17, 2009 at 10:57 AM
use the ROW_NUMBER() function to give you a column that is ordered according to your cost price.
Personally, I would create a procedure that creates the above and then use the procedure as the source of the data for the report.
answered Nov 17, 2009 at 04:59 AM