CODEWITHSUNDEEP
aggregate-functionsreportbuilder3.0
Gloria Santin
Gloria Santin

Reputation: 2136

Create Expression in Report Builder 3.0 Report to sum a column

I created a report with 3 columns, Department, Ticket Count, Ticket Number. It groups Department names and the second column shows 1 instance of the Ticket Count. The last column shows all of the ticket numbers.

I added a row that shows the grand total of all of the departments displayed.

This is the data set results :

Department   TicketCount TicketNumber
D1           3           12345
D1           3           22345
D1           3           32345

I group the Department and the TicketCount so that the display is like this:

 Department   TicketCount TicketNumber
    D1           3           12345
                             22345
                             32345

I want to add a ticket total at the end but the result is always adding all of the ticket counts and not just one. So the Total displayed is 9 not 3.

I need to create an expression that picks the distinct TicketCounts of the departments and sums them.

The function DistinctCount returns the correct number of counts when I have multiple departments but not the values.

I tried the RunningValue function but it adds all of the values in the column.

=RunningValue(Fields!ReopenedTicketCount.Value, sum, Nothing)

I need to create a function that sums the distinct values of the ticket counts of each department.

Can anyone point me in the direction as to the functions that I need to use?

Upvotes: 1

Views: 197

Answers (1)

Gloria Santin
Gloria Santin

Reputation: 2136

I figured this out. I just used the COUNT function on the third column to get the grand total.

Upvotes: 1

Related Questions