Login
Register
Search
Home
Forums
Jobs
LawsonGuru
LawsonGuru Letter
LawsonGuru Blog
Worthwhile Reading
Infor Lawson News Feed
Store
Store FAQs
About
Forums
Performance Management
Lawson Business Intelligence/Reporting/Crystal
DME query issues
Home
Forums
Jobs
LawsonGuru
LawsonGuru Letter
LawsonGuru Blog
Worthwhile Reading
Infor Lawson News Feed
Store
Store FAQs
About
Who's On?
Membership:
Latest:
Wayne Willis
Past 24 Hours:
0
Prev. 24 Hours:
0
Overall:
5190
People Online:
Visitors:
210
Members:
0
Total:
210
Online Now:
New Topics
Lawson Landmark
Executing IPA from PR160
5/3/2024 2:46 PM
We're facing a situation where we need to upda
IPA/ProcessFlow
Executing IPA from PR160
5/3/2024 2:46 PM
We're facing a situation where we need to upda
S3 Customization/Development
Cobol - Extract Current Time
4/24/2024 7:21 PM
How do you extract just the Current System Time in
Lawson Landmark
LPL INSTR Functions
4/5/2024 8:32 PM
I'm writing a simple report using the Create R
Infor SCM
Translating 856 to get the ~ REF^CN^ field
4/3/2024 8:24 PM
We are trying to get the tracking number which is
IPA/ProcessFlow
Sample XML file create Flow
4/3/2024 3:43 PM
Hello everyone, I am new to creating XML files
Lawson S3 HR/Payroll/Benefits
bn105 error message
3/26/2024 6:40 PM
I need to change some of the set ups in our Life I
IPA/ProcessFlow
IPA executing Job
3/13/2024 7:08 PM
New to the IPA world and was wondering, can an IPA
Lawson S3 HR/Payroll/Benefits
Life Age Reduction on benefits plans
3/12/2024 7:15 PM
For our optional life we have an age based coverag
Lawson S3 HR/Payroll/Benefits
BN53.1 Add-In
3/7/2024 3:31 PM
We are migrating to Solstice. They require a
Top Forum Posters
Name
Points
Greg Moeller
4184
David Williams
3349
JonA
3288
Kat V
2984
Woozy
1973
Jimmy Chiu
1883
Kwane McNeal
1437
Ragu Raghavan
1354
Roger French
1311
mark.cook
1244
Forums
Unanswered
Active Topics
Most Liked
Most Replies
Search Forums
Search
Advanced Search
Topics
Posts
Prev
Next
Forums
Lawson Business Intelligence/Reporting/Crystal
DME query issues
Sort:
Oldest First
Most Recent First
You are not authorized to post a reply.
Author
Messages
dipu
Basic Member
Posts: 9
4/29/2010 7:39 AM
how can I get the maximum check date while creating a query in Lawson Query Builder, where I have informations about employee, employee status, process level, company and paymastr only. Please help.
Shane Jones
Veteran Member
Posts: 460
5/2/2010 8:35 PM
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?
dipu
Basic Member
Posts: 9
5/3/2010 4:33 AM
Is there any other way to do this in crystal reports 9? Kindly help.
Shane Jones
Veteran Member
Posts: 460
5/4/2010 11:59 AM
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....
dipu
Basic Member
Posts: 9
5/4/2010 12:06 PM
Yes, I am using Lawson OLEDB and crystal reports 9, so where we need to give this query? in crystal formula?
Shane Jones
Veteran Member
Posts: 460
5/5/2010 11:36 PM
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.
dipu
Basic Member
Posts: 9
5/6/2010 11:21 AM
Thanks for the information, however the query we are using is not SQL type it is DME.
Shane Jones
Veteran Member
Posts: 460
5/11/2010 2:35 AM
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.)
You are not authorized to post a reply.