Reporting from LTM

Sort:
You are not authorized to post a reply.
Author
Messages
Regenia
New Member
Posts: 3
New Member

    Hi,

    We want to use Crystal Reports and LBI dashboards to display data. In TM the derived fields and the encrypted fields do not display using a ODBC connection to the oracle database. I need fields for term reason, SSN, mailing address to name a few. I can parse term reason from the audit log using some oracle functions, and I could get mailing address and SSN from S3 if necessary in a query but I would like to know if there is an easier way to get the term reason than parsing the xml in the audit log and if there is some way to get the mailing address (as opposed to the payroll address) and SSN from LTM in a query. I have tried to use the Query Builder for Lawson OLEDB as a data source but it keeps giving me errors. I also tried to use the derflddef command in LID to get the logic behind the derived fields but those System Utility Tools are not on the Landmark server and it does not recognize the command.

     

    Also, does anyone use the LTM feature 'Create Report' for distributing reports to multiple employees? When the report is made public should the records viewed be controlled through Lawson security that has been setup? It seems very limited as a reporting tool for anything other than an adhoc query tool to be used by the employee at his or her desk.

     

    Thanks

     

    Regenia

    Peter O
    Veteran Member
    Posts: 69
    Veteran Member
      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


      Regenia
      New Member
      Posts: 3
      New Member

        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

        Peter O
        Veteran Member
        Posts: 69
        Veteran Member

          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


           

           

           

          You are not authorized to post a reply.