SQL server Reporting Services 2005 - Conditional Row Filtering based on column grouping

Question

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.

Answer 1

I would use a query along the lines of:

WITH Top30Products as
(
SELECT TOP (30) ProductID
FROM Sales
WHERE SaleDate >= DATEADD(month,DATEDIFF(month,0,GETDATE())-1,0)
AND SaleDate < DATEADD(month,DATEDIFF(month,0,GETDATE()),0)
GROUP BY ProductID
ORDER BY Sum(CostPrice) DESC
)
 SELECT *
 FROM Sales s
 WHERE ProductID IN (SELECT ProductID FROM Top30Products)
 AND SaleDate >= DATEADD(month,DATEDIFF(month,0,GETDATE())-2,0)
 AND SaleDate < DATEADD(month,DATEDIFF(month,0,GETDATE()),0)
UNION ALL
 SELECT *
 FROM Sales s
 WHERE ProductID IN (SELECT ProductID FROM Top30Products)
 AND SaleDate >= DATEADD(month,DATEDIFF(month,0,GETDATE())-13,0)
 AND SaleDate < DATEADD(month,DATEDIFF(month,0,GETDATE())-12,0)
;

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.

Answer 2

use the ROW_NUMBER() function to give you a column that is ordered according to your cost price.

eg

USE [adventureworks]
GO
WITH    top30
          AS ( SELECT
                name ,
                productnumber ,
                [StandardCost] ,
                ROW_NUMBER() OVER ( PARTITION BY [StandardCost] ORDER BY [StandardCost] DESC ) AS ROWNO
               FROM
                [Production].[Product] AS p
             )
    SELECT
        *
    FROM
        [top30]
    WHERE
        ROWNO < 30
    ORDER BY
        [StandardCost] DESC

Personally, I would create a procedure that creates the above and then use the procedure as the source of the data for the report.

Jonathan

SQL server Reporting Services 2005 - Conditional Row Filtering based on column grouping

Follow this question

Related Questions