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.

conditional group summing problem

I am having a little difficulty with aggregate functions in a group. I have two columns, one for the current year and one for the previous year. the functions are as follows:

=Sum(iif(Fields!YEAR_DESIGNATION.Value="C",Fields!AMOUNT.Value,0)) // Current year sales

and

=Sum(iif(Fields!YEAR_DESIGNATION.Value="P",Fields!AMOUNT.Value,0)) // Previous year sales

The YEAR_DESIGNATION field is based on a sql server case statement that returns a "P" or a "C" depending on the year (invoice date). Anyway, the data looks perfectly normal, each row has a "P" or a "C" along with a value in the amount field. For some reason if the grouping contains both P" and "C" rows, I get #error where the data should be. If a row only contains all P's or all C's the totals work. I have done this before but for some reason I can't get this to work.

P.S. All fields have data (there are no nulls)

It sounds like the AMOUNT field value is not of type System.Int32, but either Int16 or Double or Decimal. Assuming you are conditionally aggregating double values, you have to ensure that the datatypes are always the same:

=Sum(iif(Fields!YEAR_DESIGNATION.Value="C", CDbl(Fields!AMOUNT.Value), 0.0))

-- Robert

|||That was it! Thanks again. Your always a very big help Robert.

conditional group summing problem

I am having a little difficulty with aggregate functions in a group. I have two columns, one for the current year and one for the previous year. the functions are as follows:

=Sum(iif(Fields!YEAR_DESIGNATION.Value="C",Fields!AMOUNT.Value,0)) // Current year sales

and

=Sum(iif(Fields!YEAR_DESIGNATION.Value="P",Fields!AMOUNT.Value,0)) // Previous year sales

The YEAR_DESIGNATION field is based on a sql server case statement that returns a "P" or a "C" depending on the year (invoice date). Anyway, the data looks perfectly normal, each row has a "P" or a "C" along with a value in the amount field. For some reason if the grouping contains both P" and "C" rows, I get #error where the data should be. If a row only contains all P's or all C's the totals work. I have done this before but for some reason I can't get this to work.

P.S. All fields have data (there are no nulls)

It sounds like the AMOUNT field value is not of type System.Int32, but either Int16 or Double or Decimal. Assuming you are conditionally aggregating double values, you have to ensure that the datatypes are always the same:

=Sum(iif(Fields!YEAR_DESIGNATION.Value="C", CDbl(Fields!AMOUNT.Value), 0.0))

-- Robert

|||That was it! Thanks again. Your always a very big help Robert.