Processing Time

[mnmdesigns]

Hello, I am new to Crystal Reports. I am using version 8.5 and a SQL server 2000. I have been working on one report which had around 10,000 reocrods. So far the report was running fine. I have added a new summary field and this is just freezing the Crystal Report. I am running the SQL server on my machine too. Can anyone tell me why this is happening. If I take out that summary field the report run perfectly fine.

Any ideas why this is happening.

All help is appreciated.

MD

 

[bdreed35]

Please provide more information regarding the summary. Are you summarizing a database field or a formula? Adding one summary to your report should not cause any any significant processing time but it all depends on what your doing.

~Brian

 

[mnmdesigns]

I am using various formulas. In total I have 11 summary fields based on the fromulas that work with the database field.

The report has data for almost 2 years. This is a commision summary report. Includes information about all the sales people in the company and in the summary fields I am getting commissions based on the types.

I let is run for 10 mins and it showed me the data. So it is running but taking a long time to run. What I do to avoid this?

When I take out the summary field, the report run perfectly fine.

MD

 

[bdreed35]

Can you post one of the formulas that you have based the sumnmary off of? I don't know what your doing within the formulas so I can't point you in any direction.

~Brian

 

[mnmdesigns]

Brain,

I appreciate your prompt reply.

Here is what my summary field looks like

Newspaper Radio TV Domestic Intl
Quarter 1 XXX xxx xxx xxx xxx
Quarter 2 XXX xxx xxx xxx xxx
Quarter 3 XXX xxx xxx xxx xxx
Quarter 4 XXX xxx xxx xxx xxx
-----------------------------------------------------
Total Year 1 XXX xxx xxx xxx xxx
-----------------------------------------------------
Total Year 1&2 XXX xxx xxx xxx xxx
----------------------------------------------------

For each field like newspaper for Quarter 1, I have created a formula like this:

if {table.trans_type} = "News" then 1 else 0

Similarly I have created 3 more formula for the rest of the 3 quarters. I have added a summary of each field to get the number of transactions in each quarter under each category.

After this I have created a new formula to get Year I total for newspaper. The information is coming from a second table.

In the total field I have done of sum of all these fields. Maybe I am using too many records and this is why it is taking a long time.

Please advice.

Let me know if you need more information.

Thanks a bunch

MD

 

[bdreed35]

What are your grouping levels?

Is it:
G1 - Year
G2 - Quarter
and Details suppressed?

Depending on how it is structured, you may not need as many formulas.

Perhaps using the layout above, you can identify which section that the Quarters and Year reside in.

~Brian

 

[mnmdesigns]

I have only one group in my report. This group is based on Sales Person Identity (SPID). Besides this I have a bunch of fields in the details section.

To get the year summary I have added a second details section. I have the details section supressed and just using the Year total summary field. This data is for the last two year.

I only need the Total value. Is there another shorter way to do it. This would make my life so easier if the report runs faster.

Appreciate your help

MD

 

[bdreed35]

Can you group your data by date? You could change it to group by quarter and make your life much easier.

~Brian

 

[mnmdesigns]

The header is supposed to look like this

TITLE: Commission Summary Report
SPID: 0000
NAME : First, Last
FOR : Month Name

I thought it was the best choice to group the information by the SPID. Do you think I can do it by using the month section, or maybe add a second group?

Please advice.

Thanks a bunch,

MD

 

[bdreed35]

Add the Second group using the date field.
When you insert the Group, choose your date field, and change the 3rd drop down list to each month.
Now you should only need 5 formulas for your totals.
Your formula that you posted earlier:
if {table.trans_type} = "News" then 1 else 0
is what you want.
Make sure that you have the appropriate one form each of the other 4 categories.
Now you can create summaries for each of these formulas, on any or all groups. This eliminates the need to create a formula for each count that you want. This starts to take advantage of Crystals sections.

Try that out and see how far you can get.

~Brian

 

[mnmdesigns]

I have tried to create a new group. The only problem is that I dont have a date field. I have a separate table which is referenced for the name and number of month. When I use this field with the name of the month, the report runs at the same speed.

Any other suggestions?

MD

 

[bdreed35]

Lets take a step back here. Can you describe the data that is being returned to your report? I think we need to get an idea of what th structure is and then we can look at the design of the report.

~Brian

 

[mnmdesigns]

The header looks like my previous post. Then I have a bunch of fields including in the details section
Proj#, Sub Proj#,Move in Date, Reservation Date,

Some of these fields are strings, date time and numbers.

After this I have a couple of tables in the group footer. This is where the summary table is placed.

Hope this gives you an idea of what my report structure looks like.

MD