Presentation Help

[wildrancher]

Hello all,
This is what I have:
Table T1 has the following fields:

Emplid
Period
Salary


The information is stored as:
Emplid Period Salary
100 DEC2002 123
101 DEC2002 456
100 JAN2003 456
101 JAN2003 789


Now this is what I want to do.

I want to create a report which will help me do the following.

I want to know what is the salary I paid to the guy in the current Period and prior prior period.

For Employee 100 the view should bring out the following result set

Empl CurrentPay Previous Pay
100 456 123


The period will be a Prompt that the users will provide at run time.

Now when the prompt is for JAN2003 then prior period should be DEC2002. How can I make it work.


Please Help.

Thanks
wildRancher

 

[Madawc]

For Crystal 8.5, I'd do it as follows:

a) Group by Emplid

b) Do a pair of Running Totals, one of which adds the salary for the previous period and one for the current period. Each should reset for change of group.

c) Show these on the Group Footer. Also put Employee on the Group Footer.

d) Suppress the Group Header and Detail using [Format Selection].

If your data was exactly as you showed it, the same output could also be produced using Next or Previous. But the method I've detailed should work regardless.

Madawc Williams
East Anglia
Great Britain

 

[wildrancher]

Hello Madawc Williams of GBR,

This Period should be a dynamic one...the data I have shown is a sample and the Year -1 logic for this report is not supported by previuos and next...

Please help.

Thanks

 

[Madawc]

Set them as parameters, which you'd enter for each run.

Alternatively, if you check out DateAdd, there are also ways to find the current and previous period. Do an experimental report and experiment with values till you get the output you want.

Madawc Williams
East Anglia
Great Britain

 

[Reebo99]

First you need to only bring back the information for this period and last period.

Assuming the period parameter is a string, I suggest converting it to a date then converting back to a string to match against the database. This will save lines of code for working out the last period.

Create a formula :
@LastPeriod
ucase(ToText(DateAdd("m",-1,Date("01 "&left("JAN2003",3)&
" "&right("JAN2003",4))),"MMMyyyy&quot)

This gives you DEC2002. Replace "JAN2003" with your parameter field.
Then put in the Record Selection Formula :

T1.Period = {?PeriodParameter} or T1.Period = @LastPeriod

Second, create a group on Emplid then create 2 formulas :

@CurrentPay
If T1.Period = {?PeriodParameter} then T1.Salary else 0

@PreviousPay
If T1.Period = @LastPeriod then T1.Salary else 0

place these fields in the detail, right click, insert summary and choose maximum within the group you created.

You can now suppress the detail section and voila! you have within the group footer the EmplId, the current pay and the previous pay.

Did this help?


Reebo
Scotland (Sunny with a Smile)

 

[Ngolem]

I might try it by using a subreport for the previous year value

In the main report you would get the current Pay information

the Main report would have a formula that calculates the previous period value

@Previousyear

WhilePrintingRecords;
StringVar PreviousYear := "";
StringVar TempMonth := Left({Table.CurrentPayPeriod},3);
StringVar TempYear := Right({Table.CurrentPayPeriod},4);

if TempMonth = "JAN" then
PreviousYear := "DEC" + totext(tonumber(TempYear) - 1,0)
else if TempMonth = "FEB" then
PreviousYear := "JAN" + TempYear
else if TempMonth = "MAR" then
PreviousYear := "FEB" + TempYear
else if TempMonth = "APR" then
PreviousYear := "MAR" + TempYear
else if TempMonth = "MAY" then
PreviousYear := "APR" + TempYear
else if TempMonth = "JUN" then
PreviousYear := "MAY" + TempYear
else if TempMonth = "JUL" then
PreviousYear := "JUN" + TempYear
else if TempMonth = "AUG" then
PreviousYear := "JUL" + TempYear
else if TempMonth = "SEP" then
PreviousYear := "AUG" + TempYear
else if TempMonth = "OCT" then
PreviousYear := "SEP" + TempYear
else if TempMonth = "NOV" then
PreviousYear := "OCT" + TempYear
else if TempMonth = "DEC" then
PreviousYear := "NOV" + TempYear;

PreviousYear;

Your subreport would link on this formula as well as EmployeeID.

The output of the subreport would simply be the Previous Pay....which would be a null if the employee didn't work the previous month.

That should work







Jim Broadbent

 

[wildrancher]

Reebo

When I try to put the following into the formula field it says bad date...UCASE(TOTEXT(DATEADD("m", -1, DATE("01" &LEFT({EMPLOYEE_VW.PERIOD_ID},3)&" "&RIGHT({EMPLOYEE_VW.PERIOD_ID},4))), "MMMyyyy&quot)



Please Help.

Thanks

 

[Reebo99]

Replace with :

UCASE(TOTEXT(DATEADD("m", -1, DATE("01 " &LEFT({EMPLOYEE_VW.PERIOD_ID},3)&" "&RIGHT({EMPLOYEE_VW.PERIOD_ID},4))), "MMMyyyy&quot)

note: the space in "01 " was missing.

Reebo
Scotland (Sunny with a Smile)

 

[wildrancher]

This works now for current month totals but not for prior month totals.

Help.

Thanks

Nalini

 

[Reebo99]

You have to create a string parameter field, call it Period, which the user keys in :
"The period will be a Prompt that the users will provide at run time."

Then change the formula from

UCASE(TOTEXT(DATEADD("m", -1, DATE("01 " &LEFT({EMPLOYEE_VW.PERIOD_ID},3)&" "&RIGHT({EMPLOYEE_VW.PERIOD_ID},4))), "MMMyyyy&quot)

To

UCASE(TOTEXT(DATEADD("m", -1, DATE("01 " &LEFT({?Period},3)&" "&RIGHT({?Period},4))), "MMMyyyy&quot)

You will now have 2 fields, {?Period} - the period the user selected as current and {@LastPeriod} - the last period, based on the period keyed in by the user e.g. the user keys in JAN2003, the value of {?Period} becomes JAN2003, the value of {@LastPeriod} becomes DEC2002.

When you create the following formulas, they will become the value of the salary for that period, try changing them to :

@CurrentPay
If UCase(T1.Period) = UCase({?Period}) then T1.Salary else 0

@PreviousPay
If UCase(T1.Period) = UCase(@LastPeriod) then T1.Salary else 0

Also, try removing the record selection formula.

If this doesn't work, let me know where it seems to be going wrong. My tests show it works.





Reebo
Scotland (Sunny with a Smile)