INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Calling Commands in Formula Slows report dramatically

Calling Commands in Formula Slows report dramatically

(OP)
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:

CODE -->

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:

CODE -->

{@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.

RE: Calling Commands in Formula Slows report dramatically

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

RE: Calling Commands in Formula Slows report dramatically

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/

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close