CODEWITHSUNDEEP
reporting-servicesssrs-2012
Ignotus
Ignotus

Reputation: 301

Combining rows in SSRS and only sum some values

In SSRS BI 2012 I have made a report as shown below. When a job gets interrupted, for example over the weekend, there is another line with the same version number in the report. In the example below it is version 3.

Is there a way to merge the lines if the version number of a specific orderNo is the same and then sum Netto and ActualTime and not Bruto and PlannedTime?

OrderNo    Ver Netto   Bruto   PlannedTime ActualTime
123456     1    1000    1050    01:50       01:45
           2    1000    1050    01:50       01:45
           3    500     1050    01:50       00:30
           3    500     1050    01:50       00:45

Upvotes: 0

Views: 139

Answers (2)

alejandro zuleta
alejandro zuleta

Reputation: 14108

Regarding your question:

Is there a way to merge the lines if the version number of a specific orderNo is the same and then sum Netto and ActualTime and not Bruto and PlannedTime?

I think it is possible if you customize the textbox using an expression and scoping columns total. Check this

It's easier if you get your data as you need it from your datasource. Assuming Bruto and PlannedTime columns will keep the same for a particular Version and you have a table like this: enter image description here

You can use the following query from your dataset:

select OrderNo,Ver,Sum(Netto) as Netto
,Max(Bruto) as Bruto, Max(PlannedTime) as PlannedTime,
DATEADD(ms, SUM(DATEDIFF(ms, '00:00:00.000', ActualTime)), '00:00:00.000') as ActualTime
from Interruption
group by OrderNo, Ver

From this dataset you can use a table component. Format your time columns as your requeriment something like this.

enter image description here

Using a table component I built this:

enter image description here

Give a try, I hope this can help you.

Upvotes: 1

Holmes IV
Holmes IV

Reputation: 1739

Yes, what you need to do is set up a group based on version number. Then for the columns of Netto, ActualTime instead of normal columns set them to functions. For functions with the Sum Function SUM(...) for the Bruto and and Planned Time, I assume you are not summing because you they are the same ? In that case just set the function to take the first value.

Upvotes: 1

Related Questions