Sum Distinct

Question

Sum Distinct

giontech

Ten Centuries

Points: 1094
More actions
August 7, 2008 at 6:57 pm

#195623

Is there a way to do sum distinct in reporting services?
I have a matrix with two column groupings and subtotals for each of them, but I can't find a way to do this..
Thank you very much in advance.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

giontech (8/7/2008)
Is there a way to do sum distinct in reporting services?
I have a matrix with two column groupings and subtotals for each of them, but I can't find a way to do this..
Thank you very much in advance.

The sum of all individual items plus the sum of all subtotals is exactly twice the sum of the individual items... sum everything up and divide by 2.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 2

giontech (8/7/2008)
Is there a way to do sum distinct in reporting services?
I have a matrix with two column groupings and subtotals for each of them, but I can't find a way to do this..
Thank you very much in advance.

what do you mean by "sum distinct"? doesn't the standard grouping/totalling in your matrix produce an overall total like the attachment?

jvElecenter SSCarpal Tunnel Points: 4502 More actions · Answer 3

Hi there

I know what the questionaire means. Have now the same problem.

I have a grouping on delivery address and I need to know the quantity of collis delivered. But I can't just add up the quantity of colli's because several orderlines are present in one colli number.

So what should the function be ? It like Running Value.

See data exemple

Delivery address 1 :

# colli's : 1 - Collicode PRM78901

# colli's : 1 - Collicode PRM79701

# colli's : 10 - Collicode PDA20010

Deliver address 2 :

# colli's : 1 - Collicode SRT79350

You see at delivery address 3 different colli codes, so the sum I need is 12 colli.

Anyone any idea ?

Running value doesn't work because then I need to group on the collicode and my customer doesn't want that !

So a countdistinct isn't good neither Because that would result in 3.

Kind regards

JV

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

jvElecenter (5/13/2011)
Hi there
I know what the questionaire means. Have now the same problem.
I have a grouping on delivery address and I need to know the quantity of collis delivered. But I can't just add up the quantity of colli's because several orderlines are present in one colli number.
So what should the function be ? It like Running Value.
See data exemple
Delivery address 1 :
# colli's : 1 - Collicode PRM78901
# colli's : 1 - Collicode PRM79701
# colli's : 1 - Collicode PRM79701
# colli's : 10 - Collicode PDA20010
Deliver address 2 :
# colli's : 1 - Collicode SRT79350
You see at delivery address 3 different colli codes, so the sum I need is 12 colli.
Anyone any idea ?
Running value doesn't work because then I need to group on the collicode and my customer doesn't want that !
So a countdistinct isn't good neither Because that would result in 3.
Kind regards

Just to be clear, can you post the expected results for your data example, please?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)