2

I have an SSRS Report, in the database there is a column by name Total_running_hours. There are more than one record for a single Cycle_number like more than 1 row with same Cycle_number but different Block_numbers and the value in Total_running_hours field will be same for all the rows with same Cycle_number.
Eg. 1 Cycle number with 4 diff block_numbers contain same Total_running_hours for all 4 rows.

Now the problem is, in the group footer if I put this field then it will show the Total_running_hours value only once which is correct, but my final requirement is,

I need to get the sum of this field in the Report footer which need to display the sum group wise. No matter how many rows are there for a single Cycle_number it has to take only once and display the result.

I tried in different ways like

=sum(ReportItems!textbox204.Value) // name of text box in Group footer

Error: Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope.

=sum(Fields!total_running_hours.Value,Group_name)

Error: The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.

Can any one please help me in getting the sum Group wise

Thank you in advance.

Dadapeer Pvg
  • 147
  • 12

1 Answers1

0

I found solution for this Problem.

We cannot simply sum the Total_Running_hours value as this would give us duplicates and the incorrect answer. We cannot sum the reporting services group as it goes out of scope There is no SUM DISTINCT available in Reporting Services 2005 so we can't get the distinct value that way.

Since the query may not return a particular Cycle_Number Type we cannot use that as a filter.

The solution found was to add a column of the row number within a windowed set partitioned by the Cycle_Number like this

ROW_NUMBER() OVER (PARTITION BY Cycle_Number ORDER BY Cycle_Number ) AS 'RowNumber'

Then in the reports’ footer total column we put an expression that only takes the first row’s value to sum and converts all other rows to zero in that windowed set.

=SUM(IIF(Fields!RowNumber.Value=1,Fields!Total_Running_hours.Value,0))

After using this if u found any error in textbox like #Error

Then try this

=SUM(IIF(Fields!RowNumber.Value=1,CDbl(Fields!Total_Running_hours.Value),CDbl(0.0)))

Dadapeer Pvg
  • 147
  • 12