Print Page | Close Window

Too many formulas/RT's?

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=20967
Printed Date: 07 May 2024 at 2:44am


Topic: Too many formulas/RT's?
Posted By: SOSteppenwolf
Subject: Too many formulas/RT's?
Date Posted: 12 Aug 2014 at 7:02am
I have a report that is crawling (slow). Takes forever to open and refresh.

It is only 94KB in size, but contains 36 running totals and 164 formulas. None of the formulas are excessively complicated in scope, in my opinion; but I have many more to add in order to get what I want to deliver.

This is a forecasting report that builds off of a rolling 36 month history.

My IT lady thinks the problem is the number of formulas and running totals I'm using. She mentioned referencing "stored procedures" or SQL. However, she doesn't know how to do that offhand.

Can anybody offer any insight on how I can build this report more efficiently?

Thanks,


Replies:
Posted By: DBlank
Date Posted: 13 Aug 2014 at 3:49am
In general using stored procedures to select and manipulate tyour data tends to be more efficient as all of the processing is done on the server side rather than pulling all of the data into crystal and having the calculations done on the client side.
However, I would not neccessarily assume that the formula's are the issue.
How many records are we dealing with?
Are you only selecting the last 36 months of data to start with?
How many tables are you using?
Using the the raw data in another report and placing one field per table onto the report (making sure joins are enforced) what is the execute time without any formulas?
 

Posted By: SOSteppenwolf
Date Posted: 13 Aug 2014 at 6:42am
What is involved with creating a stored procedure, in general terms? Doesn't it involve the use of SQL and Visual Basic? That's my impression after searching for it.

As I'm building the report, I used the Select Expert to choose only one item. This amounts to 2,569 records over the 36 months. The "original" report, which isn't as formula intensive (approx 75) has 205,700 records and executes in a matter of seconds.

Yes, I am using the following formula in the Select Expert to avoid pulling any more data than necessary:

{salesTable.SalesDate} >= dateadd("m",-36,today()-day(today()-1))

5 tables

Thanks again

Posted By: DBlank
Date Posted: 13 Aug 2014 at 6:57am

You need the correct permissions in the SQL DB to write a stored procedure (and save it there) which can be used as a Report source.

IN a stored proc you can get very creative in creating and manipulating the original data set into something more useful as a report source. You will of course need to have the SQl skills to do that, but you can alwasy have a DB adminstrator crete the SP for you and then use it as the data source.
You can also write a Crystal Command as  a Data source which is like Stored Proc.
 
164 formula and 36 running totals seems like quite and I am going to guess that you are building formulas on top of formulas to get to an actual display value.
If that is the case perhaps envisioning this to accomplish the entire calculation in one formula or RT might be helpful.
Also you did not mention any sub reports but if you have them they can kill your performance.
Also in the "original" report that executes quickly, are you sure all 5 table joins are being enforced in the report?
If your new formulas are actually enforcing a previosuly not enforced join that may be where a large issue is happening.

Posted By: SOSteppenwolf
Date Posted: 13 Aug 2014 at 7:20am
I'll follow up with my people here on the SP/Crystal Command stuff. I'll also toy with consolidating the formulas.

Yep, quite a few compounding formulas, but no sub reports.

No, none of the table joins on the original report were enforced, nor on the new report (according to the Database Expert).

Thanks again for the help.

Posted By: DBlank
Date Posted: 13 Aug 2014 at 7:31am
The joins will become enforced when you use a field from both tables (or two tables ends if it is a daisy chain). This means that, although you may add a table to the report, unless you use a field from tha table it may actually be dormant. People often report the behavior as their report suddenly went from 100 records to thousands of records when they added a field to the report. What heppened was using the field enforced the join and chnaged the record set. Soemtimes this involves a cross join and will cause the report to crash.
In the original report, look at Field explorer and the DB field ande each table. See if their is a green check mark nexty to at least one field in each table. If so then all of the joins are enforced.
It is unlikely this is an issue, but best to rule it out. Otherwise you may be expecting the new report to run like the old one when in fact it is using a much smaller data set and running smoothly for that reason.


Print Page | Close Window