Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Min, Mid and Max Post Reply Post New Topic
Author Message
thummel1
Senior Member
Senior Member
Avatar

Joined: 27 Apr 2012
Location: United States
Online Status: Offline
Posts: 140
Quote thummel1 Replybullet Topic: Min, Mid and Max
     Posted: 13 Mar 2015 at 4:48am
HI, I'm using Crystal Reports 2008.
 
My goal: To display in separate data fields, the minimim pay rate, mid pay rate, and max pay rate for each employee.
 
Table structure: Employee's are in a PAEMPPOS table with a "schedule" and "Pay Grade". Then there is another table (PRSAGDTL) that tracks the rates for each schedule and pay grade. Each schedule and pay grade has 3 pay rates. (minimum, middle, and max). The table also stores all the effective dates each time the rates change. So the PRSAGDTL table looks like this:
 
SCHEDULE EFFECT_DATE PAY_GRADE PAY_RATE
Non Contract   01-Jan-50 306 10
Non Contract 01-Jan-50 306 15
Non Contract 01-Jan-50 306 20
Non Contract 01-Mar-11 306 10.5
Non Contract 01-Mar-11 306 15.5
Non Contract 01-Mar-11 306 20.5
 
 
My goal: to display the minimum, mid, and pax amounts in separate data fields for the max effective date for each employee like this:
Employee Min Rate Mid Rate  Max Rate
12345 10.5 15.5 20.5
 
I have a formula now trying to pull the min pay rate for the max effective date, but it's pulling in the min pay rate for the min effective date:
minimum(({vw_PRSAGDTL.PAY_RATE}),({vw_PAEMPPOS.PAY_GRADE}))
 
How can I change this formula to pull the min pay rate for the max effective date per schedule/Pay grade? All suggestions welcome.


Edited by thummel1 - 13 Mar 2015 at 4:48am
"Press any key to continue. Where's the 'Any' Key?" ~Homer Simpson
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 13 Mar 2015 at 5:26am
can you write a stored procedure as your data source?
IP IP Logged
thummel1
Senior Member
Senior Member
Avatar

Joined: 27 Apr 2012
Location: United States
Online Status: Offline
Posts: 140
Quote thummel1 Replybullet Posted: 13 Mar 2015 at 5:36am

I have not worked with Stored Procedures before, and not knowing if internal setup would be an issue (do stored procedures need to be an approved data source for Lawson Business intelligence to work? Which at this time no additional data sources are being approved), I'm inclined to say no.  If Stored Procedures can be done without worrying about it being an approved data source, I'm willing to consider, but realize I have no experience with them. :)

My hope is that this is just a formula tweaking issue.


Edited by thummel1 - 13 Mar 2015 at 5:37am
"Press any key to continue. Where's the 'Any' Key?" ~Homer Simpson
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 13 Mar 2015 at 5:49am

Can't really answer those questions for you. It usually has more to do with data source type and user rights into that source.

I was headed towards limited the data scope to only joining in your max table values from the PRSAGDTL table. much 'simpler' to handle on that side of the process.
Is a Crystal Command an options for you?


Edited by DBlank - 13 Mar 2015 at 5:49am
IP IP Logged
thummel1
Senior Member
Senior Member
Avatar

Joined: 27 Apr 2012
Location: United States
Online Status: Offline
Posts: 140
Quote thummel1 Replybullet Posted: 13 Mar 2015 at 5:54am

Yes, there is an "Add Command" option. I never use it. But I know we have the option to use this, say, if we write queries in SQL Developer for Oracle. We can just paste them in there.
"Press any key to continue. Where's the 'Any' Key?" ~Homer Simpson
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 13 Mar 2015 at 6:03am
not an oracle user but the gist is you can write a sub query as your source for the PRSAGDTL content. In that sub query you can limit the data to only the max date rows per schedule and pay grade
 
example using SQL
select SCHEDULE, EFFECT_DATE, PAY_GRADE, PAY_RATE
FROM PRSAGDTL as A
JOIN
(select DISTINCT SCHEDULE, MAX(EFFECT_DATE) as Mdate, PAY_GRADE
FROM PRSAGDTL
GROUP BY SCHEDULE, PAY_GRADE) as B
ON A.SCHEDULE=B.SCHEDULE AND A.EFFECTDATE=B.Mdate AND A.PAY_GRADE = B.PAY_GRADE
 


Edited by DBlank - 13 Mar 2015 at 6:04am
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.