Conditional sum based on visibility

Hi,

I have a report that is conditionally showing a textbox based on the previous entry that is working correctly.

My issue is that the non visible entries are still being added to my Sum statement at the end of the report.

I need a way to exclude an entry based on its visibility.

Any help would be greatly appreciated.

Are you using the Previous aggregate to get the previous entry? If you are not, then you can add a conditional, using the same expression for determining the visibility, to the SUM aggregate. For example, =SUM(IIF(HiddenExpression, 0, Fields!FieldName.Value))|||

Thanks for the response.

I am using the Previous function to control the visibility. The report needs to show the first entry for each particular company code, but not the duplicate entries.

For example: =Previous(Fields!Company_code.Value) = Fields!Company_code.Value

I tried adding a conditional with the same expression to determine if it should be included in the Sum, but that does not work.

Any other suggestions?

Thanks!

|||

Try handling it in the code (Report -> Report Properties -> Code)

Declare a public shared variable (integer/float) in the code and write a public function to sum up the values based on current company code and previous company code. Your code will look something like this in VB.Net:

Public Shared SumTotal as Integer

SumTotal = 0

Public Function CalculateSum(isCompanyCodeSame as Boolean, FieldValue as Integer) As String

If isCompanyCodeSame = False Then

SumTotal = SumTotal + FieldValue

End If

CalculateSum = ""

End Function

and

append this expression to any of the textboxes in your detail row:

Fields!FieldName.Value & Code.CalculateSum(Fields!Company_code.Value=Previous(Fields!Company_code.Value), Fields!FieldToBeSummed.Value)

And use Code.SumTotal to get the sum.

Shyam

|||

Thanks for the code!

It is working correctly now.

|||

I used the same method to stop displaying rows in a table after the 10th row. It works fine in VS 2005 but it acts wierd when I publish it to the production server. I count up the rows that are visible...

Public Shared VisibleRowTotal as Integer=0

Public Function CountVisibleRow(isVisible as Boolean) As String
If isVisible = False Then
VisibleRowTotal = VisibleRowTotal + 1
End If
CountVisibleRow = ""
End Function

Then I added a column in my report table to call the code...

=Code.CountVisibleRow(ReportItems!textbox54.Value) & " " & Code.VisibleRowTotal

Then I based my row visibility on the code value.

=IIF(Code.VisibleRowTotal>=10, True,False)

To get it to work the first time I had to rename the original report on the production server and then upload the new report. Once several users start hitting the report then no rows are visible or it's intermittent.

I'll admit I've have never used custom code in a report before. Is there something different I need to do when uploading an rdl with custom code? Am I handling the custom code properly?

|||
You should change the VisibleRowTotal variable to not be Shared. Having it be shared or static will cause each instance of the report to share the same total value. So, removing the modifier will allow each report instance to execute independently from one another.

Ian|||

Thanks Ian.

I had the issue where my totals were correct for the first time the report loaded, but the totals just kept incrementing when I used different filters on the report.

Taking Shared off of the variable resolved the issue.