Why doesn't our report data update automatically? How can we make it update the data every time it is run?

Question

We have a really simple Crystal Report that is just pulling info about jobs from our Viewpoint database. The only parameters are Company, Department and Status, each of which have default values that do not change.

When the report is run, it should update with new jobs that have been entered, new amounts, etc. Instead, all data remains the same and jobs that have been entered since the last time the report was run does not show up.

Here is our code:

SELECT 
  "JCJMPM"."Job",
  "JCJMPM"."Description",
  "JCCM"."ContractAmt",
  "JCCM"."JCCo",
  "JCCM"."Department",
  "JCCM"."ContractStatus",
  "JCMP"."Name",
  "ARCM"."Name"
 FROM   
  (("Viewpoint"."dbo"."JCJMPM" "JCJMPM" LEFT OUTER JOIN "Viewpoint"."dbo"."JCCM" "JCCM" ON ("JCJMPM"."JCCo"="JCCM"."JCCo") AND ("JCJMPM"."Contract"="JCCM"."Contract")) 
  LEFT OUTER JOIN "Viewpoint"."dbo"."JCMP" "JCMP" ON ("JCJMPM"."JCCo"="JCMP"."JCCo") AND ("JCJMPM"."ProjectMgr"="JCMP"."ProjectMgr")) 
  LEFT OUTER JOIN "Viewpoint"."dbo"."ARCM" "ARCM" ON ("JCCM"."CustGroup"="ARCM"."CustGroup") AND ("JCCM"."Customer"="ARCM"."Customer")
WHERE  
  "JCCM"."JCCo"=1 
  AND "JCCM"."Department"='50' 
  AND "JCCM"."ContractStatus"=1
ORDER BY
  "JCCM"."JCCo", 
  "JCCM"."Department", 
  "JCCM"."ContractStatus"

Answer 1

You have a left join : LEFT OUTER JOIN "Viewpoint"."dbo"."JCCM" "JCCM" and Where clause : WHERE
"JCCM"."JCCo"=1 AND "JCCM"."Department"='50' AND "JCCM"."ContractStatus"=1

The where clause will kill the left join and will turn it to an inner join, Change the SQL to this one and try again:

SELECT 
  "JCJMPM"."Job",
  "JCJMPM"."Description",
  "JCCM"."ContractAmt",
  "JCCM"."JCCo",
  "JCCM"."Department",
  "JCCM"."ContractStatus",
  "JCMP"."Name",
  "ARCM"."Name"
 FROM   
  (("Viewpoint"."dbo"."JCJMPM" "JCJMPM" 
LEFT OUTER JOIN "Viewpoint"."dbo"."JCCM" "JCCM" ON ("JCJMPM"."JCCo"="JCCM"."JCCo") AND ("JCJMPM"."Contract"="JCCM"."Contract")
AND   "JCCM"."JCCo"=1 
  AND "JCCM"."Department"='50' 
  AND "JCCM"."ContractStatus"=1
) 
  LEFT OUTER JOIN "Viewpoint"."dbo"."JCMP" "JCMP" ON ("JCJMPM"."JCCo"="JCMP"."JCCo") AND ("JCJMPM"."ProjectMgr"="JCMP"."ProjectMgr")) 
  LEFT OUTER JOIN "Viewpoint"."dbo"."ARCM" "ARCM" ON ("JCCM"."CustGroup"="ARCM"."CustGroup") AND ("JCCM"."Customer"="ARCM"."Customer")
ORDER BY
  "JCCM"."JCCo", 
  "JCCM"."Department", 
  "JCCM"."ContractStatus"