Why is my SUM not working? 1

[jw970170]

Hello,

I am very new to crystal reports. I'm trying to make a formula that sums by month. This is what I have

sum({table.Value},{table.TimeStamp}, "monthly")

The problem is that it is not summing correctly. Here is the data I am using

5/15/2004 4:30:00 AM 44190
6/20/2004 4:30:00 AM 40100
5/15/2004 4:31:00 AM 50000
7/15/2004 4:31:00 AM 75000

The report is grouped by month. On the May page, it sums both may and june to give 188380

On the june month it gives 80200 (which doesn't even make sense...it seems to somehow be grabbing data from another table)

I should probably mention that I've tried to include two tables in my report but not have them linked. It gave me some warning when doing this but let me continue

 

[jw970170]

Ok, I have a field from two different tables just displayed on the main report. When I delete the field from the second table, it sum's the first tables contents correctly. When I add the field back, it includes it in the sum for some reason. Any ideas?

 

[jw970170]

I just checked the SQL statement for the report. It is linking the tables. Is it possible to undo this?

 

[jw970170]

Ok, here is the SQL. I can't tell what if it is combining the results into one table (which would explain the summation problem) or if it is in two separate tables

SELECT "Table1"."TimeStamp", "Table1"."Value"
FROM "ADMKRS"."dbo"."Table1" "Table1"


SELECT "Table2"."Value"
FROM "ADMKRS"."dbo"."Table2" "Table2

 

[dgillz]

Not sure what version of Crystal you are using but the following will work in 8.5 and I assume it works the same way or very similar in 9 and newer.

Delete your formula. You typically do not use formulas in Crystal to Sum() data. Also delete your group by month.

Right click the field you want to sum in the details section of the report and select insert, summary. Tell Crystal you want to summarize by {table.TimeStamp}. By virtue of timestamp being a date/time field, you will get prompted for how often it should summarize. Choose monthly.

If this is not what you want, post back with more details.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[jw970170]

Ok, well that was interesting. As I said, I'm very new to this. I tried that out as well as using a running total. Unfortunately, the summay block is in the report footer in the design view (it must somehow be moved into the details section and it won't allow me to move it) and it does not show up in the report preview.


Also, I can't really get rid of the grouping. My report repeats every month. So I want sums for each month.

 

[dgillz]

If you do what I say you will have every month. You will have the report you need.

What is it again that is in the report footer?

Is the {Table.Value} field not in the details section?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[jw970170]

ok, I think I didn't quite follow your example the first time.

This time, I deleted my group. I also deleted every field (just for testing) except table.value. When I right click on table.value and insert summary, I can select sum but it doesn't exactly allow me to summarize by timestamp. I have to click add new group (in the summary editor) and then I can select timestamp. It places the summary box in the group footer (where it appears to be stuck). For some reason, it does not summarize for the first month, until I look at the report for the second month...but it does appear to be working sort of.

 

[jw970170]

ok, I was able to move the summary to the group header rather then footer and it is working on the right page Yea!

Now, if I coudl somehow move it into the details section (I am using XI)

 

[dgillz]

Why do you want the summary field in the details section?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[jw970170]

well, it is because all I really want is a summary section. The database has several values each month. All I want is a summary for each column. ie

Shipping costs Summary1
Travel expenses Summary2
Something else Summary3

Eventually, I am going to figure out how to use the cross tab and have something like

Month 1 Month 2 ....
Shipping costs Summary1 Summary1
Travel expenses Summary2 Summary2
Something else Summary3 Summary3

 

[dgillz]

Then suppress the details section and just display the summary. If you need detail fields in there, just drag them to the summary section as well.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[jw970170]

ahhh....that is a good idea. Thanks