none
How to find out if a particular field has changed from previous week.

    Question

  • Hello Everybody,

    I have to develop a report which should display if employees have changed their costcenter this week(Mon - Sun).

    There is a cost center field for each employee and sometimes the employee changes his costcenter.I want to find out the employees who changed their costcenter this week compared to the previous week.

    I am not sure how I can put this into a report.

    Can somebody help me with this.Thankyou.

    Wednesday, April 10, 2013 7:00 PM

All replies

  • I'm assuming you're capturing this as an audit trail in some table.  If so, you can do a nested report to show this information or a separate report if you want to just report the changes.


    Scott

    Wednesday, April 10, 2013 8:07 PM
  • Thanks ScottMcc for your reply.

    Can you elaborate on your reply. How can I achieve this using a nested Report? I have to compare previous weeks data with cureent weeks data and the data is changing everyday.

    Thankyou

    Wednesday, April 10, 2013 8:29 PM
  • You need to store the previous data somewhere (may be some archive table). Then you can use the report to display the compared results.

    The archiving of data will depend on how much old data you want to archive.

    Whatever process is changing data in your tables (I assume some ETL load process) will first archive current data and then load new data.

    You can then write a stored procedure to compare data in the current table and archive table and return the records which have changed.

    Report always picks up data on what is available on the table at that time, it will never store data itself.

    - Girija

    Wednesday, April 10, 2013 9:38 PM
  • Hi Kannari,

    You can use the Previous() function which returns the value or the specified aggregate value for the previous instance of an item within the specified scope. Then, you can use insert an extra column into the tablix to indicate which employees have different costcenter comparing to the previous record. For example, you can use the following expression to control the values displayed in the new column:

    =IIF(Fields! Costcenter.Value=Previous(Fields!costcenter.Value), “No”,”Yes”)

    If it is aggregation values, you can use the following expression:

    =IIF(Sum(Fields! Costcenter.Value)=Previous(Sum(Fields!costcenter.Value)), “No”,”Yes”)

    If you use SSRS 2008 R2 or higher, you can also insert indicators into the new column, and use different colors to indicate whether the Costcenter values change or not in adjacent two rows.

    References:

    Regards,
    Mike Yin

    If you have any feedback on our support, please click here


    Mike Yin
    TechNet Community Support

    Friday, April 12, 2013 6:29 AM
    Moderator
  • Hi Mike,

    Thanks a lot for your reply.

    I tried using the Previous function,but what happens is the new field displays the costcenter from previous row.For example if there are 10 employees in a report the first employee have "null" for the field,the second employee have the first employees costcenter value.

    This is not what I want.If the first employee costcenter is "abc" during the last week and this week he changed his costcenter to "def" I want def to display in the report.

    Do you think we can do this?I appreciate your help

    Thankyou once again.

    Monday, April 15, 2013 8:52 PM