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.
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.
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.
If you have any feedback on our support, please click here
TechNet Community Support
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.