Combining rows in SSRS and only sum some values

Question

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

alejandro zuleta alejandro zuleta · Accepted Answer · 2015-07-13T16:28:18

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.

Combining rows in SSRS and only sum some values

2 Answers