[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 537
  • Last Modified:

Crystal Reports automatically selecting previous business day for a report

I have a report that we are going to be putting on a server to automate it. Currently we have a prompt the user select the previous business day but on the server we need it to automatically select the previous business day. I don't know if this will help but below is the select expert formula I have now.

({FUND_MASTER_IDX.SNAPSHOT_ID}="DEFAULT" or Isnull({FUND_MASTER_IDX.SNAPSHOT_ID})) and
{ENTITY_GROUP.ENTITY_NAME} in ["MFunds_01_Domestic Eq", "MFunds_02_Global Intl Rgnl", "MFunds_03_Sector", "MFunds_04_Fixed Inc", "MFunds_05_FOF", "MFunds_06_VI", "MFunds_07_Closed-End"] and
({FUND_SUMMARY_IDX.DMART_CURRENCY}="BASE" or
Isnull({FUND_SUMMARY_IDX.DMART_CURRENCY})) and
({FUND_MASTER_IDX.EFFECTIVE_DATE} = {?As of Date} or
Isnull({FUND_MASTER_IDX.EFFECTIVE_DATE})) and
({FUND_SUMMARY.DMART_CURRENCY} = "BASE" or
Isnull({FUND_SUMMARY.DMART_CURRENCY})) and
{FUND_MASTER.EFFECTIVE_DATE} = {?As of Date} and
({FUND_SUMMARY_IDX.ENT_TYPE} in ["CIDX", "INDX"] or Isnull({FUND_SUMMARY_IDX.ENT_TYPE}))
0
wilpitz
Asked:
wilpitz
1 Solution
 
dsackerContract ERP Admin/ConsultantCommented:
If your business days do not include Saturday and Sunday, you can simply change your lins that have {FUND_MASTER_EFFECTIVE_DATE} = {?As of Date} to the following:
{FUND_MASTER_EFFECTIVE_DATE} = DATEADD(DAY,
                    CASE DATENAME(WEEKDAY, GETDATE()) 
                        WHEN 'Sunday' THEN -2 
                        WHEN 'Monday' THEN -3 
                        ELSE -1
                    END, DATEDIFF(DAY, 0, GETDATE()))

Open in new window

0
 
mlmccCommented:
That should work.

I would suggest that you reverse some of the selection criteria.

Crystal stops evaluating if you try to compare a NULL value to anything.

You should always test for NULL before comparing to a value

For instance
(
 Isnull({FUND_SUMMARY.DMART_CURRENCY})
 OR
 {FUND_SUMMARY.DMART_CURRENCY} = "BASE"
)

(
  Isnull({FUND_MASTER_IDX.SNAPSHOT_ID})
  OR
  {FUND_MASTER_IDX.SNAPSHOT_ID}="DEFAULT"
)

(
Isnull({FUND_SUMMARY_IDX.DMART_CURRENCY})
OR
{FUND_SUMMARY_IDX.DMART_CURRENCY}="BASE"
)

mlmcc
0
 
James0628Commented:
dsacker,

 Why do you have DATEDIFF(DAY, 0, GETDATE()) at the end instead of just GETDATE()?  Just curious.  To get rid of the time maybe?


 wilpitz,

 dsacker's basic idea seems sound, but he's given you MS SQL syntax, which won't work in a CR record selection formula.  In CR syntax, I think it would be something like:

{FUND_MASTER_EFFECTIVE_DATE} =
 DateAdd ("d",
  (Select Weekdayname (WeekDay (CurrentDate))
   Case 'Sunday' :
    -2
   Case 'Monday' :
    -3
   Default :
    -1), CurrentDate)
                                           
 

 Also, when you talk about business days, are holidays a factor?  If so, that's a whole other problem to address.

 James
0
 
wilpitzAuthor Commented:
That was it in crystal format! Thanks for the SQL help and null tips as well!
0
 
James0628Commented:
I'm glad I could help, but, personally, I think dsacker should get some points too.  His code wouldn't work in your formula because it was in the wrong language, but all I did was copy his code and convert it to CR syntax.

 You can ask to have the question re-opened and split the points between us.


 And please do follow mlmcc's suggestion about the IsNull tests.  They have to be first or there's no point in including them.

 James
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now