Enquiry about Queries for SRSS

Question

Hello everyone,

I write my problem here because I tried everything that I could, and I don't find a way to make what I want.

I need to make a report, for this, I'm using Microsoft SQL Server Report Builder to prepare Tables and Queries.

Here is one of my tables :

The important information or my enquiry is that I've got an object (ECP), and this object will have different status during his processing. I will display all of his previous status with column : "From State > To state" with the date of the change with the Column "Date of change" like below :

Now, I would like to calculate time elapse between each status In my column "Time Elapsed between status" => In my example, calculate time between (From state=new, To state=CAB) and (From state=CAB, To state=ON_HOLD), so time elapse from 12/12/2018 to 09/01/2019

Which Expression do I need to make it ?

I don't have a clue which method I need to use to calculate time between result of a query in another query with difference between two raws.

I need your help on this guys !

Thanks by advance and let me know if you need further information.

Fabien

Answer 1

Hi,
As Dan said，you could try the following query.

=Datediff(DateInterval.Day,Fields!DOCINDEXDATE.Value,Previous(Fields!DOCINDEXDATE.Value))  

The Previous function only supports field references in the details group. For example, in a text box in the details group, =Previous(Fields!Quantity.Value) returns the data for the field Quantity from the previous row.

For more information about the use of the Previous function, please refer to: Report Builder Functions - Previous Function.

Regards,
Joy

Answer 2

Hello Fabien,

You can accomplish what you want by using the "Previous" function:

=Datediff(DateInterval.Day,Fields!DOCINDEXDATE.Value,Previous(Fields!DOCINDEXDATE.Value))

The Previous function will compare the value of the current row's field with the previous row's field value.

I hope this helps.

--Dan

Answer 3

Hi Fabien,

"Previous" will still work in your example. I assume your data is sorted by "object_ecp" and then date. Add a "Switch" statement like the example below.

The trick is to compare your current Object Number with the prior Object Number.

=Switch
(
Previous(Fields!object_ecp.Value) = Fields!object_ecp.Value, Datediff(DateInterval.Day,Fields!DOCINDEXDATE.Value,Previous(Fields!DOCINDEXDATE.Value)),
True, Nothing
)

I hope this helps.
--Dan

Answer 4

Hello Joy,

I didn't know this function, but that's work perfectly !

Thank you very much for the solution !

Regards,

Fabien

Answer 5

Hello again,

I come back to you for another (the last I guess) question !

Your solution work perfectly, BUT, the problem is that I've got multiple OBJECT (ECP) that I replace in the following image by number 1,2,3.

Is there a way to calculate previous raw like before, only on the object and to not have the calculation made between 2 object (because this is nonsense).

In my example :

For the Object 2, he is calculating time elapsed between his time and the last time of Object 1, but what I want for Object 2 is nothing (null), I would like to reset te count between each object.

Let me know if you have it in your pockets !

And thanks again guys !

Fabien