SQL Server Performance

Problem with Sum()

Discussion in 'SQL Server 2005 Reporting Services' started by swierzym, Oct 18, 2006.

  1. swierzym New Member

    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

Share This Page