Crystal Report Formula Error

Question

So I have a field calculate field on my report. The formula should return a DateTime value based on some values.

Below is the formula

DateTime (DateValue(
if abs(DayOfWeek({vw_prfma_proforma.Dock_Date}) - {vw_prfma_proforma.p_dock_no}) < 4 then
(
if DayOfWeek({vw_prfma_proforma.Dock_Date}) > {vw_prfma_proforma.p_dock_no} then
 dateadd("d",-(DayOfWeek({vw_prfma_proforma.Dock_Date})-{vw_prfma_proforma.p_dock_no}),{vw_prfma_proforma.Dock_Date})
else if DayOfWeek({vw_prfma_proforma.Dock_Date}) < {vw_prfma_proforma.p_dock_no} then
dateadd("d",{vw_prfma_proforma.p_dock_no}-DayOfWeek({vw_prfma_proforma.Dock_Date}),{vw_prfma_proforma.Dock_Date})
else
dateadd("d",0,{vw_prfma_proforma.Dock_Date})
)
else
(
if DayOfWeek({vw_prfma_proforma.Dock_Date}) > {vw_prfma_proforma.p_dock_no} then
dateadd("d",7 , dateadd("d",-(DayOfWeek({vw_prfma_proforma.Dock_Date})-{vw_prfma_proforma.p_dock_no}),{vw_prfma_proforma.Dock_Date}))
else if DayOfWeek({vw_prfma_proforma.Dock_Date}) < {vw_prfma_proforma.p_dock_no} then
dateadd("d",-7,dateadd("d",{vw_prfma_proforma.p_dock_no}-DayOfWeek({vw_prfma_proforma.Dock_Date}),{vw_prfma_proforma.Dock_Date}))
else
dateadd("d",0,{vw_prfma_proforma.Dock_Date})
)

)
,Time(
cdbl(
left(
replace(Space( 5-Length (replace(cstr({vw_prfma_proforma.p_dtoleranc}),".00",""))) 
+ cstr(replace(cstr({vw_prfma_proforma.p_dtoleranc}),".00",""))," ","0")
,3))
,
cdbl(
right(
replace(Space( 5-Length (replace(cstr({vw_prfma_proforma.p_dtoleranc}),".00","")))
+ cstr(replace(cstr({vw_prfma_proforma.p_dtoleranc}),".00",""))," ","0") 
,2))
,0
))

The problem I am facing is, it loads in the Preview mode, giving desired data. However, when loaded up in the browser, it gives the following error

Error in formula . 'DateTime (DateValue( ' This field name is not known.

I am at a loss as to what to do. I verified DB on print and it gives me that. Without the verification command, all values come back as zero. Please help.

I do not have experience with Crystal Reports on the web, but I wonder if DateTime or DateValue are unavailable on the web to the formula. — Justin C
As far as i know, the DateTime and DateValue functions are standard. — trevoirwilliams
How would I know if I am connecting to the right database in the browser? — trevoirwilliams

trevoirwilliams trevoirwilliams · Accepted Answer · 2014-04-17T15:31:27

I found a fix. In the code where I load the tables and the connection information in the page's code behind, that is hosting the Crystal Viewer control, I added an additional field per table being referenced. See code below.

private void SetDBLogonToTables(CrystalDecisions.Shared.ConnectionInfo connectionInfo, CrystalDecisions.CrystalReports.Engine.ReportDocument reportDocument)
{
    CrystalDecisions.CrystalReports.Engine.Tables tables = reportDocument.Database.Tables;

    foreach (CrystalDecisions.CrystalReports.Engine.Table table in tables)
    {
        CrystalDecisions.Shared.TableLogOnInfo tableLogonInfo = table.LogOnInfo;

        tableLogonInfo.ConnectionInfo = connectionInfo;
        table.ApplyLogOnInfo(tableLogonInfo);

           //Following, did the trick! 
            try
            {
                string strLocation = connectionInfo.DatabaseName + ".dbo." + table.Location.Substring(table.Location.LastIndexOf(".") + 1);

                table.Location = strLocation;
            }
            catch (Exception ex)
            {//Catch 
            }
    }
}

Crystal Report Formula Error

1 Answers