Blank Report in Crystal 8.5

[cis12232004]

I am using Crystal 8.5 to create a new report that can be accessed through Solomon 5.5 and I am using SQL database with an ODBC connection. The problem is the report prints sometimes and then other times a blank report prints. I copied out the SQL statement on a couple occassions and what I noticed is that the "WHERE" clause is being amended and the RptCompany.RI_ID is duplicating itself. This is an example of the SQL with the report printing fine:

SELECT
vr_10630."Descr", vr_10630."InvtID", vr_10630."StkItem", vr_10630."ValMthd", vr_10630."RI_ID", vr_10630."SiteName", vr_10630."BegBal", vr_10630."BegQty", vr_10630."FiscYr", vr_10630."SiteID", vr_10630."CnvFact", vr_10630."BatNbr", vr_10630."RefNbr", vr_10630."S4Future04", vr_10630."TranDesc", vr_10630."TranType", vr_10630."UnitPrice", vr_10630."Qty", vr_10630."ExtCost",
RptCompany."CpnyID", RptCompany."CpnyName", RptCompany."RI_ID"
FROM
{ oj "CableAppTest"."dbo"."vr_10630" vr_10630 INNER JOIN "CableAppTest"."dbo"."RptCompany" RptCompany ON
vr_10630."CpnyID" = RptCompany."CpnyID" AND
vr_10630."RI_ID" = RptCompany."RI_ID"}
WHERE
vr_10630."StkItem" = 1 AND (((vr_10630.siteid = 'WH3')) AND RptCompany.RI_ID = '2') AND (RptCompany.RI_ID = '2') AND (RptCompany.RI_ID = '2') AND (RptCompany.RI_ID = '2')
ORDER BY
RptCompany."CpnyID" ASC,
vr_10630."InvtID" ASC,
vr_10630."SiteID" ASC

and this is the SQL for the blank report:

SELECT
vr_10630."Descr", vr_10630."InvtID", vr_10630."StkItem", vr_10630."ValMthd", vr_10630."RI_ID", vr_10630."SiteName", vr_10630."BegBal", vr_10630."BegQty", vr_10630."FiscYr", vr_10630."SiteID", vr_10630."CnvFact", vr_10630."BatNbr", vr_10630."RefNbr", vr_10630."S4Future04", vr_10630."TranDesc", vr_10630."TranType", vr_10630."UnitPrice", vr_10630."Qty", vr_10630."ExtCost",
RptCompany."CpnyID", RptCompany."CpnyName", RptCompany."RI_ID"
FROM
{ oj "CableAppTest"."dbo"."vr_10630" vr_10630 INNER JOIN "CableAppTest"."dbo"."RptCompany" RptCompany ON
vr_10630."CpnyID" = RptCompany."CpnyID" AND
vr_10630."RI_ID" = RptCompany."RI_ID"}
WHERE
vr_10630."StkItem" = 1 AND (((vr_10630.siteid = 'WH3')) AND RptCompany.RI_ID = '2') AND (RptCompany.RI_ID = '2') AND (RptCompany.RI_ID = '2') AND (RptCompany.RI_ID = '3')
ORDER BY
RptCompany."CpnyID" ASC,
vr_10630."InvtID" ASC,
vr_10630."SiteID" ASC

Is it possible that items may be stuck in the rpt. runtime table and if so, how can I prevent it from recurring?

If not, any idea what may be causing this problem to occur randomly? There is no consistency as to when this problem will.

Thanks for your help.

 

[synapsevampire]

The SQL Statement is generated as a result of what's in the Report->Edit Selection Formula->Record and Group formulas.

I'd guess that this is where your problem lies, so please post it.

-k

 

[cis12232004]

This is what is located in the Record selection:

{vr_10630.RI_ID} = ToNumber ({@RI_ID}) and
{vr_10630.StkItem} = 1.00
and {RptCompany.RI_ID} = ToNumber(RIPARAM( "RI_ID" ))

The Group formula is blank.

This scenario is repeated for both the report that printed blank and the one that printed correctly.

 

[synapsevampire]

What is this RIPARAM? It's probably a UFL function, and may be your culprit.

Anyway, let's alter its format slightly and see if that helps:

(
{vr_10630.RI_ID} = val({@RI_ID})
)
and
(
{vr_10630.StkItem} = 1.00
)
and
(
{RptCompany.RI_ID} = val(RIPARAM("RI_ID"))
)

If you can discover what this function does, we may be able to rewrite it using Crystal syntax.

-k