asked on

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}))

dsacker

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

Mike McCracken

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

ASKER CERTIFIED SOLUTION

James0628

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wilpitz

ASKER

That was it in crystal format! Thanks for the SQL help and null tips as well!

James0628

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