Crystal Reports - Pushing Down Query with Variables to Server

Question

I've been having trouble pushing down queries to a SQL server when using Crystal Reports (I'm currently working with Crystal Reports 2013).

If I enter the following as a record selection formula, Crystal uses a WHERE clause to select the records:

{LINKAGE.ENTITY_ID_1} = 1 and
{LINKAGE.ENTITY_TABLE_1} = "ENT" and
{LINKAGE.ENTITY_TABLE_2} in ["DOC","PHO"]

-Gives-

SELECT "LINKAGE"."ENTITY_ID_1", "LINKAGE"."ENTITY_TABLE_1", "LINKAGE"."ENTITY_TABLE_2"
FROM   "TESTING_DB"."dbo"."LINKAGE" "LINKAGE"
WHERE  "LINKAGE"."ENTITY_ID_1"=1 AND "LINKAGE"."ENTITY_TABLE_1"='ENT' AND ("LINKAGE"."ENTITY_TABLE_2"='DOC' OR "LINKAGE"."ENTITY_TABLE_2"='PHO')

If I change the 'hard coded' values to instead use variables, Crystal no longer includes a WHERE clause when selecting records:

numbervar valueNumber := 1;
stringvar valueString := "ENT";
stringvar array valueArray := ["DOC","PHO"];

{LINKAGE.ENTITY_ID_1} = valueNumber and
{LINKAGE.ENTITY_TABLE_1} = valueString and
{LINKAGE.ENTITY_TABLE_2} in valueArray

-Gives-

SELECT "LINKAGE"."ENTITY_ID_1", "LINKAGE"."ENTITY_TABLE_1", "LINKAGE"."ENTITY_TABLE_2"
FROM   "TESTING_DB"."dbo"."LINKAGE" "LINKAGE"

From reading the documentation it sounds as though there shouldn't be an issue pushing these down, so I'm not too sure what I might be missing. Any insight would be greatly appreciated!

Amran Amran · Accepted Answer · 2018-08-13T14:53:33

I seem to have found the answer in the Crystal Reports User Guide. There it's mentioned that only a constant or parameter value can be pushed down but not a variable:

https://help.sap.com/doc/dfc124becfa845ffa91b1e717b20e3ec/2016%20SP3/en-US/4770d0cd6e041014910aba7db0e91070.html#loio4770d0cd6e041014910aba7db0e91070

Crystal Reports - Pushing Down Query with Variables to Server

1 Answers