Field value is repeated - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Field value is repeated

Author

Topic

Notoook
Starting Member

18 Posts

Posted - 2009-01-28 : 06:58:17

Greetings all,

In the dataset there is a table that inner joins master/details tables.
That is tableA ID is repeated twice (or more in some cases) in tableB rows. Now i've a list that contains a table inside the Body section of the report. The table has a group on the tableA ID. What i need is that, how can i obtain the value without the duplication inside a RunningValue aggregate? I've tried this expression
= (RunningValue(Fields!CreditValue.Value), sum, "list1_Group")) / RowNumber("table1_Group1")

but it is not working. Thanks a lot.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 07:15:51

what's value you're trying to display? tableA.ID? are you dispalying tableB values also along with it? also what have you grouped on?

Notoook
Starting Member

18 Posts

Posted - 2009-01-28 : 07:39:49

Table A
------------
ID |Debit |Credit |AccountID
1 |500 |0 |1
2 |100 |0 |5

TableB
----------
ID |TableA_ID |AccountID |AnalysisDimValue
19 |1 |1 |250
20 |1 |1 |250
21 |2 |5 |30
22 |2 |5 |70

DataSet_Table
-------------
TableA_ID |Debit |Credit |AccountID |AnalysisDimValue
1 |500 |0 |1 |250
1 |500 |0 |1 |250
2 |100 |0 |5 |30
2 |100 |0 |5 |70
0 |270 |0 |5 |0

in the dataset table the debit value is repeated.And the last row values result from another sql statement to get the debit value for a certain account and appended to this result set using UNION ALL

List is grouped on AccountID

Table has the DataSet Name = Dataset_Table and is grouped on TableA_ID, in the group header i want to accumelate the summation of "Debit" value. The expression =(RunningValue(Fields!CreditValue.Value), sum, "list1_Group")) / RowNumber("table1_Group1") works fine for the rows that has the TAbleA_ID not equal to 0. and failed otherwise.

Sorry for the long post.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 08:44:29

wont this be enough in group header?
SUM(Fields!CreditValue.Value,"list1_Group")

Notoook
Starting Member

18 Posts

Posted - 2009-01-28 : 09:03:55

No, Sum function is not valid as i want an accumulative summation for every row add the value of the previous Fields!CreditValue.Value to the current row's Fields!CreditValue.Value.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 09:27:31

if 0 in denominator is your problem just use

=(RunningValue(Fields!CreditValue.Value), sum, "list1_Group")) / IIF(RowNumber("table1_Group1")>0, RowNumber("table1_Group1"),Nothing)

Notoook
Starting Member

18 Posts

Posted - 2009-01-28 : 09:57:11

All i need is that: the input of RunningValue to be divided by the RowNumber("table1_Group1"), (Fields!CreditValue.Value / RowNumber("table1_Group1")) to remove the doubled or tripled running total. But of course RunningValue is an aggregate, cant call another aggregate inside it, & can't reference any text box inside it either.

The division has to occur before the accumulative sum runs.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 09:59:29

so what should be report output out of posted data above?

Notoook
Starting Member

18 Posts

Posted - 2009-01-29 : 02:59:21

This is the desired output:

Balance has the formula
= RunningValue((Fields!DebitValue.Value - Fields!CreditValue.Value), Sum, "list1_Group1") / RowNumber("table1_group1")

The rows inside the dataset is duplicated or tripled, so the division on the row number is important. But for the AccountID = 5, has 1 row duplicated (which the TableA_ID not equal to zero) and 1 row not duplicated (which the TableA_ID equals to 0). As a result, it calculates the Balance = (270 + (100 + 100)) / 2. All i want is a way to divide first then calculate the running value, that is
= (270 + (100 + 100)/2).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 12:47:23

sorry image is not visible

Notoook
Starting Member

18 Posts

Posted - 2009-02-01 : 05:27:11

Here is the image

http://3.bp.blogspot.com/_gbkwVBGuZIA/SYFg-NrrzJI/AAAAAAAAANk/r0BHgIoBv0w/s200/Desired+output.JPG

and i've solved it with subreport to view tableB data, put it inside the TableA details section.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources