Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  Development Tools  Reporting Services Development  Problem with Sum()

Author  Topic  swierzym Starting Member 1 Post Posted - 2006-10-18 : 16:18:27 Hello, I have problem with aggregation methods in RS 2005. I have a query thar returns data set: Department, Storage, Article, Invoice, Quantity, Value, ValueX 3,1,'Art A','Inv 1',2,30,5 3,1,'Art B','Inv 1',4,40,7 3,1,'Art A','Inv 2',1,15,5 3,1,'Art B','Inv 2',2,20,7 3,2,'Art A','Inv 3',1,18,3 3,2,'Art B','Inv 3',2,16,4 ValueX - this is value returned by UDF. It is unique for every "Article & Storage group". In RS my report looks like this: (grpDepartment) Department, Sum(Quantity), Sum(Value), Sum(ValueX, scope?) (grpStorage) +Storage, Sum(Quantity), Sum(Value), Sum(ValueX, scope?) (grpArticle) ++Article, Sum(Quantity), Sum(Value), ValueX (detInvoice) +++Invoice, Quantity, Value And the data looks like this: (grpDepartment) 3, 12, 139, 31(24+7) <- should be 19(12+7) (grpStorage) 1, 9, 105, 24(5+5+7+7) <- should be 12(5+7) (grpArticle) ”Art A”, 3, 45, 5 (detInvoice) ”Inv 1”, 2, 30 (detInvoice) ”Inv 2”, 1, 15 (grpArticle) ”Art B”, 6, 60, 7 (detInvoice) ”Inv 1”, 4, 40 (detInvoice) ”Inv 2”, 2, 20 (grpStorage) 2, 3, 34, 7(3+4) <- ok only because there is 1 detail row for each Article (grpArticle) ”Art A”, 1, 18, 3 (detInvoice) ”Inv 3”, 1, 18 (grpArticle) ”Art B”, 2, 16, 4 (detInvoice) ”Inv 3”, 2, 16 The Sum() error is because I sum ValueX from details, and I should sum it from "grpArticle". Unfortunately I cannot. I've tried to use Sum(ValueX,"grpArticle") but I can use it only in "details" and "grpArticles". In "upper" groups ("grpStorage" and "grpDepartment") it generates error. Any help in here ? :| Maciej