Reporting from LTM

Hi,

We use Crystal for reports via JDBC with MS Sqlserver as the LTM/LMRK DB. Some derived fields are generated on the fly on the application side (we had some issues with Time to Fill metrics, for example, because they were generated on the fly from other fields).
We had to pull the necessary fields and essentially recreate that derived field's function outside the system for reporting purposes. Other times you need to search for the table that contains the derived field - but this should be in your TM Table Definition docs.

If your GHR system holds term information, and employee data, you shouldn't have issues with the fields you mentioned. Your problem sounds like it could be a driver/DB rights issue as far as accessing encrypted tables/data.

As far as Term reason codes, I will have to check on that table, but you should be able to ask AMS for the TableDefinition document for your specific version, it's very helpful.
Employee mailing address is located on the EMPLOYEEADDRESS table, you'd want to query on where EMPLOYEEADDRESS.ACTIVE=1 and EMPLOYEEADDRESS.EMPLOYEEADDRESS = 1
The field we use is PADAADDRESSLINE1. We don't use any lines 2's or anything, but I'm sure your requirements will vary.

We do use the create reports feature in LTM for delivering ad hoc reports to certain client groups. We use the reports for recruitment metrics mostly such as -Time to Fill , Terminations, Hires, New hires and similar. You can remove the ability to view and create reports by security role (we restricted some user role's abilities to create reports or make them public), and we haven't really played with releasing reports using "custom groups" at all, but I have heard it is possible.

Something to note about reports is that if you have restricted field-level information via security, they can still see the report, but the fields which are restricted for them will appear blank. So they might see a weird-looking report with some columns blank and others with data in them, but we've found that this works satisfactorily for our client groups with many simple reports.


Thanks,
Peter

Hi,

 

Thanks for the advice. Unfortunately, employees can enter different addresses in the system for MakeEmployeeMailAddress and MakeEmployeePayrollAddress and it does happen. I need to determine which of the fields is checked when this situation occurs and I can only see those fields using Lawson Query Builder. I finally got it to display all of the classes and was able to choose one as a Table in Crystal reports but I could not get it to expand and list the fields. I waited for more than an hour after clicking on it in the Field Exporer. Have you encountered this? I was trying to open the class com_lawson_apps_hr_Employee_Manager which has around 5700 records.

 

Also, I am trying to make sure that I am correctly interpreting your comments about the Create Report feature in LTM. Are you are saying that Lawson security can be used to hide fields not entire records? The thought was to be able to hide records similar to what occurs when a report is created in Crystal Reports and burst in LBI. Is that possible within Lawson when the report is made 'public'?

 

Thanks

 

Regenia

Hi Regenia, 

"Thanks for the advice. Unfortunately, employees can enter different addresses in the system for MakeEmployeeMailAddress and MakeEmployeePayrollAddress and it does happen. I need to determine which of the fields is checked when this situation occurs and I can only see those fields using Lawson Query Builder."

I guess I'm not sure what the issue is here - Landmark transactions are date based- so you could probably pull the most recently created address for a termed employee. Also, from what I understand of the employee record, there are flags that are present in the application, which have an equivalent flag in the database (for reporting) - eg: IsMailingAddress, IsPayrollAddress, and Active flags -- All of these flags exist either through relations or derived fields in the database. 

 

Without looking at your Crystal setup, I can't tell you anything about why it takes forever... but from what I understand with Crystal, this is not a difficult thing to do (have crystal hang for hours) :( Sounds like it could be a local issue.

 

"Also, I am trying to make sure that I am correctly interpreting your comments about the Create Report feature in LTM. Are you are saying that Lawson security can be used to hide fields not entire records? The thought was to be able to hide records similar to what occurs when a report is created in Crystal Reports and burst in LBI. Is that possible within Lawson when the report is made 'public'?"

Even if you make a report public - you are able to restrict what FIELD DATA a user can see via Security Configurations. For example - if you make a report about Employee Telephone numbers public, but you restrict all of the user's roles so that they are NOT ALLOWED TO SEE Employee.TelephoneNumbers (I made up the relation&field) they will be able to open the report, but they will see nothing in the column where "Employee.TelephoneNumbers" is listed.

And yes- you can get so granular as to restrict a user's ability to view a specific FIELD.

Thanks,

Peter