Hello, I am trying to create a sales report. I want to have three columns in the same table that show 'year 1 sales', 'year 2 sales' and 'year 3 sales'. How can I get this in the same table? The problem I am having is that all three columns will get there information from the same source so I cant set a parameter to break it down in 3 ways. When I try to set up a parameter using 'year 1 sales' I get an invalid column error. Thanks, Daniel

Looks like you need to create CROSS TAB report...See some examples CREATE TABLE Foo ( foo_type CHAR(1) PRIMARY KEY, foo_value INT); INSERT INTO Foo VALUES('A', 1); INSERT INTO Foo VALUES('B', 2); INSERT INTO Foo VALUES('C', 3); INSERT INTO Foo VALUES('D', 4); -- pivot using CASE SELECT MAX(CASE WHEN foo_type = 'A' THEN foo_value END) AS A, MAX(CASE WHEN foo_type = 'B' THEN foo_value END) AS B, MAX(CASE WHEN foo_type = 'C' THEN foo_value END) AS C, MAX(CASE WHEN foo_type = 'D' THEN foo_value END) AS D FROM Foo; -- pivot using PIVOT operator (SQL Server 2005/2008) SELECT A, B, C, D FROM Foo PIVOT (MAX(foo_value) FOR foo_type IN (A, B, C, D)) AS P; Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/ MS SQL optimization: MS SQL Development and Optimization MS SQL Blog: Large scale of database and data cleansing MS SQL Consultants: Improves MS SQL Database Performance

Hi Danny, did you tried with column grouping option in tablix control or can use matrix control with predefined column grouping, as the per your post .. you can set the "Year sales" field as column group value with which you can get the result as per requirement. below link consists of few information about matrix column grouping http://msdn.microsoft.com/en-us/library/ee240753.aspx please let me know if the requirement is different from what is told above. Cheers, Arun Gangumalla Please mark as answered if it resolves your issue or helpful.

Below is what I tried, however I am getting n error, 'Cannot perform an aggregate function on an expression containing an aggregate or a subquery.' Do you know why this is? Thanks. select a.product, a.description, a.physical_qty, (a.physical_qty - a.allocated_qty) as free, MAX(CASE WHEN d.dated between '01/01/10' and '31/12/10' THEN sum(d.quantity) END) AS A, MAX(CASE WHEN d.dated between '01/01/11' and '31/12/11' THEN sum(d.quantity) END) AS B from scheme.stockm a left outer join scheme.podetm b on a.product = b.product left outer join scheme.bmassdm c on a.product = c.product_code left outer join scheme.opsahistm d on a.product = d.product where a.warehouse = 'L1' group by a.product, a.description, a.physical_qty, a.allocated_qty, d.quantity, d.dated order by a.product

Try select a.product, a.description, a.physical_qty, (a.physical_qty - a.allocated_qty) as free, SUM(CASE WHEN d.dated between '01/01/10' and '31/12/10' THEN d.quantity END) AS A, SUM(CASE WHEN d.dated between '01/01/11' and '31/12/11' THEN d.quantity END) AS B from scheme.stockm a left outer join scheme.podetm b on a.product = b.product left outer join scheme.bmassdm c on a.product = c.product_code left outer join scheme.opsahistm d on a.product = d.product where a.warehouse = 'L1' group by a.product, a.description, a.physical_qty, a.allocated_qty, d.quantity, d.dated order by a.productBest Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/ MS SQL optimization: MS SQL Development and Optimization MS SQL Blog: Large scale of database and data cleansing MS SQL Consultants: Improves MS SQL Database Performance

Thanks for the feedback. I am having a slightly different problem now though. The way the data is grouping is vastly incorrect, with both MAX and SUM. Is there some thing that could be causing this? I believe it is a date type change I need to make maybe? Not sure how to apply it though with the new case expression there. Thanks, Daniel

Danny, Arun's suggestion should work for you. Instead of using a table for the display, use a matrix. Add the sales year as a column group and create your row groups as you have in your table. In the matrix, the data in the data cells is filtered by both the row group(s) and column group(s) that cross sect the cell. It is designed to do exactly what you want to do.Please Mark posts as answers or helpful so that others can more easily find the answers they seek.