x

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

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.

more ▼

asked Nov 16, 2009 at 08:14 PM in Default

Khushi gravatar image

Khushi
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort oldest

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.

more ▼

answered Nov 17, 2009 at 10:57 AM

Rob Farley gravatar image

Rob Farley
5.7k 15 18 20

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Nov 17, 2009 at 04:59 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.4k 75 78 108

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1945
x547

asked: Nov 16, 2009 at 08:14 PM

Seen: 3293 times

Last Updated: Nov 16, 2009 at 08:14 PM