Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Too many formulas/RT's? Post Reply Post New Topic
Author Message
SOSteppenwolf
Newbie
Newbie
Avatar

Joined: 12 Aug 2014
Online Status: Offline
Posts: 14
Quote SOSteppenwolf Replybullet Topic: Too many formulas/RT's?
    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,
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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?
 
IP IP Logged
SOSteppenwolf
Newbie
Newbie
Avatar

Joined: 12 Aug 2014
Online Status: Offline
Posts: 14
Quote SOSteppenwolf Replybullet 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
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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.
IP IP Logged
SOSteppenwolf
Newbie
Newbie
Avatar

Joined: 12 Aug 2014
Online Status: Offline
Posts: 14
Quote SOSteppenwolf Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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.


Edited by DBlank - 13 Aug 2014 at 7:34am
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.