Solved

Help with Ageing formulae

Posted on 2013-07-01
9
195 Views
Last Modified: 2013-07-01
I have created a formulae which I know does not work

if isnull ({WO.SCHEDSTARTDATE}) then 'No Shed Date' else
if ({WO.SCHEDSTARTDATE}) < currentMonth then 'Future Months Requirement' else
if ({WO.SCHEDSTARTDATE}) in currentmonth then 'Due this Month' else
if ({WO.SCHEDSTARTDATE}) in LastFullMonth then 'Due Last Month' else

Can someone correct the above for me
Gordon
0
Comment
Question by:GiaHughes
  • 4
  • 3
  • 2
9 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 39290379
For a future requirement the Scheduled Start Date would be GREATER THAN today.

if isnull ({WO.SCHEDSTARTDATE}) then 'No Shed Date' else
if ({WO.SCHEDSTARTDATE}) in currentmonth then 'Due this Month' else
if ({WO.SCHEDSTARTDATE}) in LastFullMonth then 'Due Last Month' else
if ({WO.SCHEDSTARTDATE}) > currentMonth then 'Future Months Requirement' else

What isn't working?

mlmcc
0
 
LVL 77

Accepted Solution

by:
peter57r earned 250 total points
ID: 39290380
if isnull ({WO.SCHEDSTARTDATE}) then 'No Shed Date'
else
if {WO.SCHEDSTARTDATE} > dateserial(year(currentdate), month(currentdate)+1,0)then 'Future Months Requirement'
else
if {WO.SCHEDSTARTDATE} >= dateserial(year(currentdate), month(currentdate),1) then 'Due this Month'
 else
if {WO.SCHEDSTARTDATE} in LastFullMonth then 'Due Last Month'
else
"Before Last Month"
0
 

Author Comment

by:GiaHughes
ID: 39290452
Hi mimcc/peter67r

Good to hear from you
I think the solution peter has given me works
How can i make the report order the heading
No Shed Date
Future Months Requirement
Due This Month
Older than last month

Rather than in alpha order
Gordon
0
 
LVL 77

Expert Comment

by:peter57r
ID: 39290551
If you are grouping on this field then you can use the 'Specific Order' option in the Group dialog to set the group order.

Or you could just prefix the text with a letter to force a sequence..

A. No Shed Date
B. Future Months Requirement
C. Due This Month
D. Due Last Month
E. Older than last month
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:GiaHughes
ID: 39290563
Hi Peter57r
Where do I find the Group dialog so that i can specify the order?

Gordon
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39290613
Right click on the group header in the left margin
Click CHANGE GROUP
Click the dropdown on order
Choose SPECIFIED ORDER
A new tan will appear and you can specify the order

mlmcc
0
 

Author Comment

by:GiaHughes
ID: 39290651
Hi mimcc
The information is presented in a cross tab which is in a sub-report
so the cross tab is currently shown in the report header of the sub report
hope this makes sense, so I currently do have a Change group option
Any suggestions

Gordon
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
ID: 39290666
You can do the same thing in the cross tab

Edit the subreport
Right click the cross tab in the upper left corner
Click CROSS TAB Expert
Select the field in the row or column block
Click CHANGE GROUP
Set the order to SPECIFIED ORDER

mlmcc
0
 

Author Closing Comment

by:GiaHughes
ID: 39290698
Always excellent from mimcc and peter57r
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now