DME query issues

I was trying to do this once in a Process Flow. I contacted Lawson Support and was told that it is not possible to create a simple DME query to do this. It is possible with SQL, Crystal or creating programming in Process Flow.

Does it really need to be a DME?

Is there any other way to do this in crystal reports 9? Kindly help.

Are you able to connect directly to the database with and ODBC or OLE? If so you could create a simple command (SQL script) to pull what you need.

Something like: (structure might be a bit wrong writing from scratch)

SELECT EMPLOYEE, MAX(check_date)
FROM yourdatabase.table
WHERE what ever you need
GROUP BY EMPLOYEE

Then you could link this command to your other selections with a left outer join....

Yes, I am using Lawson OLEDB and crystal reports 9, so where we need to give this query? in crystal formula?

I use Crystal with an ODBC instead of OLE... However, it should be the same for the command. When you add tables to your crystal report you should be able to add a "Command". If you add a Command like this and then link it to your EMPLOYEE table you should be able to get what you need. (Emp Status is the only field you have listed that is not in PAYMASTR - it is in the EMPLOYEE table. The Status field in the PAYMASTR looks to be a different status field.)

This will group first by company, then employee, then process level. This means you will get more than one record for an associate if they are paid in two process levels.

SELECT "PAYMASTR"."EMPLOYEE", MAX("PAYMASTR"."CHECK_DATE") as MAX_PAYDATE, "PAYMASTR"."COMPANY", "PAYMASTR"."PROCESS_LEVEL"
FROM "prodlsn"."dbo"."PAYMASTR" "PAYMASTR"
GROUP BY "PAYMASTR"."COMPANY", "PAYMASTR"."EMPLOYEE", "PAYMASTR"."PROCESS_LEVEL"

This will group first by company, then employee. This example will give you one record per associate in each company.

SELECT "PAYMASTR"."EMPLOYEE", MAX("PAYMASTR"."CHECK_DATE") as MAX_PAYDATE, "PAYMASTR"."COMPANY"
FROM "prodlsn"."dbo"."PAYMASTR" "PAYMASTR"
GROUP BY "PAYMASTR"."COMPANY", "PAYMASTR"."EMPLOYEE"

You will need to change the FROM for your database... I hope this helps.

Thanks for the information, however the query we are using is not SQL type it is DME.

If you have OLE you should be able to create a different "type" and then just use a command to pull exactly what you need with the simple select statement. (contact your DBA for a connection to the database if you really need the report.)