Passing Parameters

[jestrada101]

How can I have Crystal pass a parameter to a query?

I have a report that is taking over 1/2 hr to run, but I'm think it's doing this because it does not utilize my partitions.

Is it possible to pass a parameter to a query expecting one?

Thanks for any guidance.

 

[franklin1232]

Have you used the crystal parameter fields? The SQL query that crystal executes will have the values you enter into the parameter fields at run time. If you need to pass them via code that can be done as well. Let me know I can post the code.

 

[jestrada101]

Thanks.. I'm currently using this in the Crystal report, but I think that this is not utilizing my PARTITIONS and INDEXES, as it's taking over half an hour to retreive my data. I have a partitioned table by DATE range.



(Year({BT_MONTHLY_CUSTOMER.CALL_START_DATE}) = {?Year}) and
(Month({BT_MONTHLY_CUSTOMER.CALL_START_DATE}) = {?Month}) and
{BT_MONTHLY_CUSTOMER.CUSTOMER} = {?Customer}

 

[Turkbear]

Hi,
When you look at the Sql ( Show Sql under the database menu), what is actually being sent to the database?

 

[jestrada101]

This is what is being. As you can see, there is no "where" utilizing the DATE.
SELECT "BT_MONTHLY_CUSTOMER"."SERVICE_LOCATION", "BT_MONTHLY_CUSTOMER"."SR_OWNER", "BT_MONTHLY_CUSTOMER"."CALL_START_DATE", "BT_MONTHLY_CUSTOMER"."ORIG_CT", "BT_MONTHLY_CUSTOMER"."TERM_CT", "BT_MONTHLY_CUSTOMER"."D_SUM", "BT_MONTHLY_CUSTOMER"."THIS_COUNT", "BT_MONTHLY_CUSTOMER"."CUSTOMER", "BT_MONTHLY_CUSTOMER"."CP_ID"
FROM "BT"."BT_MONTHLY_CUSTOMER" "BT_MONTHLY_CUSTOMER"
WHERE "BT_MONTHLY_CUSTOMER"."CUSTOMER"='TEMP CUST NAME'
ORDER BY "BT_MONTHLY_CUSTOMER"."CALL_START_DATE

 

[jestrada101]

How can I modify this to utilize my CALL_START_DATE with a between?

 

[700503]

Hello jestrada101, sorry to jump in. Would you check the command 'select expert' under the report in the menu bar? If the selection criteria for date is not there, please setup it.

 

[jestrada101]

The "select expert" did not have a date criteria and I've added that now.

thanks!

 

[tektipdjango]

to use indexes avoid using them INSIDE formulas like you do in :
Year({BT_MONTHLY_CUSTOMER.CALL_START_DATE})

change your selection this way (I try in pseudo_code as I haven't CR at the moment so check syntax and function spelling)

({BT_MONTHLY_CUSTOMER.CALL_START_DATE} >= totext({?Month})& '/ 01 /' & totext({?Year}) and ({BT_MONTHLY_CUSTOMER.CALL_START_DATE} < dateadd(m,1,totext({?Month})& '/ 01 /' & totext({?Year})))

This way, the index will be checked against the min and max date.

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​

 

[lbass]

I think Django is on the right track, but I would try:

{BT_MONTHLY_CUSTOMER.CALL_START_DATE} >=
date({?Year},{?Month}, 01) and
{BT_MONTHLY_CUSTOMER.CALL_START_DATE} <=
date({?Year},{?Month}+1, 01)-1

This assumes that you have the year and month parameters set up as numbers.

-LB

 

[sommererdbeere]

yap, i agree w/lbass as i have something similar before which it helps me if i used >= and <= for the date range