Solved

Sum of Distinct ID's not calculating as needed

Posted on 2008-06-11
2
923 Views
Last Modified: 2013-11-16
I have run across an issue that seems so simple but I cannot figure it out.  I have multiple rows from a detail table that consist of a number that I need to summarize. I only want to calculate one of the numbers from each ID but everything that I have tried either will not summarize the formula or it totals all rows.

RefID      Number
1731720      586
1731720      586
1731720      586
1731720      586
      
1731411      586
1731411      586
1731411      586
      
1732298      600
1732298      600
1732298      600

Total      5,902

The total that I am trying to get is 1,772 from the above example which consist of each ID and the number that goes with it.  I cannot do a distinct row because there are other columns within the  rows that do not have distinct values (TimeDate, Actions, etc)

The I cannot use distinct row counts because not all columns within the report are the same. How can I calculate this correctly?
0
Comment
Question by:aelliso3
2 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 21761512
Use a RunningTotals field.

Sum the number field and use 'On change of field' set to the refid.
0
 
LVL 11

Author Closing Comment

by:aelliso3
ID: 31466209
Excellent ... That worked perfectly ...
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

1. Set up your parameter at the report level as usual, check the box Multi-value, and set the Data Type to String 2. Set the Stored Procedure Parameter to varchar(max)  --<---- This part here is the key to it's success Example:    @cst_key var…
Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now