Calling Commands in Formula Slows report dramatically

[pcollins1]

I have been tasked with using a 4-4-5 date scheme and have created a SQL command to query a table to calculate beginning and end date for periods. I have determined the command itself returns the result quickly, but if called from the select expert it is slow.

Here is the SQL command:

declare @now datetime, @fm int, @fy int, @param int;

set @now = getdate();
set @fm = (select FiscalMonth from FiscalCalendar445 where CalendarDate = DATEADD(d, datediff(d, 0, @now), 0)); 
set @fy = (select FiscalYear from FiscalCalendar445 where CalendarDate = DATEADD(d, datediff(d, 0, @now), 0));
set @param = {?FiscalPeriods};

select MIN(CalendarDateNum) as begindate, MAX(CalendarDateNum) as enddate
from FiscalCalendar445
where FiscalMonth = 
	case
		when @fm - @param > 0 then @fm - @param
		when @fm - @param + 12 > 0 then @fm - @param + 12
		when @fm - @param + 24 > 0 then @fm - @param + 24
		else @fm - @param + 36
	end
and FiscalYear = @fy - ( @param / 12 )

Here is the record select formula, using the command results:

{@QUANTITY} <> 0.00 and
{@Pounds} <> 0 and
{OEHDRHST.inv_dt} in {Command.begindate} to {Command.enddate} and
{IMITMIDX.item_weight} <> 1.00

If I use hard-coded dates instead of {Command.whatever}, it runs fast. And if I simply place the results in the report, it runs fast. But when I add the {Command} code to the record select formula... it bogs down.

 

[IanWaterman]

Is your main report based on a Command if so embed the logic of your date parameters into the main command SQL.

When the report is parses SQL to database the only filter going to DB is
{IMITMIDX.item_weight} <> 1.00

All the other filters are being executed locally on report PC, that is why slow. When you hard code dates they are parsed to DB too.

Best solution is to redesign report using a Command and use your auto dat parameters in that.
Ian

 

[hilfy]

For info about Best Practices with Commands see my blog post here:

https://blogs.sap.com/2015/04/01/best-practices-when-using-commands-with-crystal-reports/

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[kenhamady]

My guess is that the command is not being used anywhere else, so until you use it in this formula, Crystal is ignoring the command. You would probably get the same performance hit if you simply put the field onto a band in the report, so it is probably not something specific to that formula. In most cases, any time you mix tables with commands you will slow things down.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/