Add command and report header problem

[antmoe3]

Crystal Reports Version 9.0

I have a report, which uses an Add Command to generate data for the report. This report run in about 30 seconds.

Now taking that same report, I added a second Add Command to generate the 1st day in the month based upon the current date. This date field is placed into a formula. This formula is then placed into the report header. Using the same parameters as the first report, this same report now takes 2 hours to generate the same data. The date field from the second Add Command must be placed in the report header.

Does anyone know how I can fix this performance issue?

 

[pelajhia]

It is not clear why do you not use CurrentDate, available in the formula editor under functions>date and time?

Or, if you are actually pulling a date field from a datasource, it is not clear why you don't just add this to the query in the first command (I think you are referring to a SQL command?)

Please provide more info.

 

[lbass]

You would be better off just writing a formula:

currentdate-day(currentdate)+1

...and placing this in the report header.

-LB

 

[antmoe3]

The date I need will not always be the first day of each month. The sql statement selected a certain date based upon the companies billing cycle. Therefore it has to be an sql statement because the begining bill cycle date will change each month. The data is based upon a field in a table based upon the current date.

select max(date_dim_id) from period
where amc_period_no -1
and amc_week = 4
and date_dim_ly = current_date -1

I am not sure if I can add this to the first 'Add Command' because it may change the data result set. Why does just adding a second Add Command slow down the report so much??

 

[lbass]

If you link two commands together, the links get processed locally. In your case, if you aren't linking at all, I think that processing also occurs locally--as long as a command field is used somewhere in the report (with the exception that if used for a list of values for a parameter, it should not affect processing).

I think you should try to build the above into your original command as a subselect in the Select clause.

-LB

 

[synapsevampire]

Did you join the second add command?

You shouldn't.

Anyway, if it does take a long time then Crystal is confused about what dat is to be used in the report.

Place a subreport in the report header with the second add command in it. You can even use a shared variable if need be to return it to the main report if you prefer.

If the add commands are unlinked, they should each only fire once, although I've had odd results at times too, hence the above solution using a subreport. Since the subreport will only fire once, it won't be too punishing to performance.

-k

 

[antmoe3]

Thanks a million!!! The subreport worked perfect.