I am trying to create a report on changes in assigned users per account. Actually account managers over time period.
The report should be like:
AccountXYZ AssignedUserFullName AssignementBeginningDate AssignementEndDate
I can see that in accounts_audit table I have enough data.
I know that for accounts module accounts_assigned_user is a relation and assigned_user_id field is being audited.
parent_id is actually account_id.
before_value_string is a user_id before the assignment change.
after_value_sting is a new assigned user_id.
date_created is a date when the change occurred.
So I should be able to easily get Account Name and user Full Name.
Even though it looks very easy I don’t know how to make it from inside the application.
I have tried with free versions of KReporter and AlineaSol which both have reporting on audited fields per module.
But I don’t know how to implement join statements to query per id and get name instead.
Does anybody have any idea?
Should I use some mysql reporting tool, create stored procedure and avoid using these tools, or there is a way to do it from the application that I am just not aware of?
I prefer to make reports inside the CRM because of the schedulers and user rights etc.