October 3, 2006 at 2:29 pm
I am hinding duplicate values which are unnecessary. My requirement is thatI have to add the sum of all the visible row values as a group. Please help me in completing it.
If I do a sum I am getting all the values even the hidden values. Please help me.
October 4, 2006 at 2:32 am
Would the easiest solution not be to do a DISTINCT on the original query and then sum as usual?
October 4, 2006 at 6:21 am
It's true, even if you were to filter your data set inside the report, the entries are still in the dataset, when in doubt get all the data you need in your dataset.
October 4, 2006 at 7:34 am
Thanks for the reply. I thought about it before I post this question and also checked with the execution plan. This values generated for this report is by a query which is joined by atleast 10 tables. If I calculate all the values its taking lots of resources and hence too much load.
If I am grouping in a report I was able to display only one value.Yes values are still in the dataset which is making in complicated. Is there a way to SUM all the visible row values by group.
Please help.
October 4, 2006 at 3:57 pm
don't think you can do a DISTINCT SUM. Try a DISTINCT COUNT on one of your duplicated fields that should give you a count of the rows, you'll also have to get the scope correct check BOL.
Martin
October 5, 2006 at 7:44 am
Thanks for the reply Martin. By doing DISTINCT COUNT I can only get the count of number of distinct rows. I want to add the visible row values.
For example: Say, I have to pay 100$ for a soap. I am paying the said amount by four checks each 25 dollars which satisfies 100$. If I run a report I am getting four seperate rows with each check number and the total paid. The amount 100$ is repeating four times, so I am hiding the repeated columns and displaying only one value.
This senario is for number of items. I want to add all the visible row values per each group. Please help.
October 6, 2006 at 5:21 am
INSTEAD of applying SUM on your aggreate item (100$) in your example, why don't you SUM up the line item column (25$) in your aggreate expression? Could be I am missing something.
Prasad Bhogadi
www.inforaise.com
October 6, 2006 at 8:43 am
Thanks for THE Helpful link James. I am working on it and will update you accordingly.
Thanks Prasad for the suggestion. The values that I get from the database are repeated. Per say for 1 Transaction I have different work reports with same values. In the report I am hiding the repeated columns.
ex: The values are displayed in two rows per transaction
Financial organisation (GROUP (Company))
12/31/2003 606.60 approved 123 (Transaction Number) (1st Row)
5643577(Wok Report) (2nd Row)
01/05/2004 1000.00 approved 124 (Transaction Number) (to Satisfy $1000 three work reports were raised)
5643500(Wok Report)
5643501(Wok Report)
5643502(Wok Report)
Total 1606.60 (Total per Company group)
I want to add the visible row values only. Because the 1000.00 was repeating for 01/05/2004 I am hinding the repeated values.
Please help.
October 9, 2006 at 10:20 am
I need to do a sum of Payment values for distinct values of Transactions Nos. Can anybody please shed some light on this topic.
October 10, 2006 at 12:04 pm
I dont' know if this will help, but I did the following to compute the median. This worked for me because there were no rows being excluded. (is there a way to designate the numbers that are visible vs those that are not?)
From there you could add your distinct payment values to an arry like the example link suggested. Then you can get the count of the group passed into another function. Here's what I did for the median.
The general program flow is this.
There are the following member variable to maintain info as we work through the processing of the report
Public GroupMedianArray(-1) As Decimal
Public startInt As Decimal
Any aggregate function (such as count or sum) will be called for every member in the dataset before any other parts of the report are rendered. So, you will call this (AddToMedianArray) function from anywhere in your report. Call the function using the following code snippet in the expression of any cell in the report. “=Count(Code.addToMedianArray(Fields!TotalDue.Value))” This will then add each number to the array. As you can see above the GroupMedianArray is set to -1 initially, this is because we increment the size of the array every time we add a number.
Public Function addToMedianArray(ByVal fieldValue As Decimal) 'as Decimal
Dim g As Integer
'Add to Group Array
g = uBound(GroupMedianArray) + 1
ReDim Preserve GroupMedianArray(g)
GroupMedianArray(g) = fieldValue
startInt = 0
End Function
At this point we have an array with every number in this column in the array. From here we need to pass the count of that group into a function. Put the following into a group header. “=Code.GroupMedianValue(Count(Fields!TotalDue.Value))” This function will use the startInt member variable above to keep track of where the first number for that group is in the array. Then a sub array will be made using the start int and the number given to us by the count parameter. After building this array it is sorted, and then the median is computed. Here’s the code.
Public Function GroupMedianValue(ByVal count As Decimal) As Decimal
Dim subArray(count - 1) As Decimal
Dim i As Decimal
For i = 0 To (count - 1)
subArray(i) = GroupMedianArray(startInt + i)
Next
Array.Sort(subArray)
startInt += count
Dim g As Decimal
If subArray.Length Mod 2 = 1 Then
g = subArray((subArray.Length / 2) - 0.5)
Else
Dim val1 As Decimal
Dim val2 As Decimal
val1 = subArray(subArray.Length / 2) * 0.5
val2 = subArray((subArray.Length / 2) - 1) * 0.5
g = val1 + val2
End If
Return g
End Function
October 10, 2006 at 3:07 pm
Thanks for the response James. I'll work on it and update you acccordingly.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply