The Author Online Book Forums are Moving

The Author Online Book Forums will soon redirect to Manning's liveBook and liveVideo. All book forum content will migrate to liveBook's discussion forum and all video forum content will migrate to liveVideo. Log in to liveBook or liveVideo with your Manning credentials to join the discussion!

Thank you for your engagement in the AoF over the years! We look forward to offering you a more enhanced forum experience.

swierzym (1) [Avatar] Offline
#1
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 ? smilie

Maciej