Running Totals

[brxmas]

ODBC SQL 2000
Crystal 11.0

I have a 'Crystal' report as follows:

Grouped By - Insertion Date

Here's what prints on the report

ad # acct # date # of ads Lineage
12786 3157262 5/8/2005 1 4
12786 3157262 5/8/2005 1 4
12786 3157262 5/8/2005 0 4
34122 6381903 5/8/2005 1 8
34122 6381903 5/8/2005 1 8
34122 6381903 5/8/2005 0 8
34122 6381903 5/8/2005 1 8
--- ----
2 12

42901 3016200 5/11/2005 1 2
15238 5174103 5/11/2005 1 4
15238 5174103 5/11/2005 0 4
4335 10311100 5/11/2005 1 8
--- ----
3 14

1) running total for ad #: I used distinct count (correct)
In the example above for ad # 12786 I only want to
count that ad # one time even though there are three ads
for the same ad # for ad # 34122 there are four ads, I only want counted one time.

2) running total for lineage:
In the example above I want a running total lineage which would sum 8 & 4 (12). If there were four ads for the same date I only want to count Lineage one time, etc.

Hopefully, I'm explaining myself clearly so you can understand. Thanks in advance for your help.

 

[lbass]

For the # of ads, you don't need a running total. Just right click on {table.ad#} in the detail section and insert a summary (distinctcount).

For the running total of lineage, use the running total expert and select {table.lineage}, sum, evaluate on change of field {table.ad#}, reset on change of group ({table.date}). This assumes that the report is sorted on acct# and then on ad#.

-LB

 

[brxmas]

LB

I appreicate your help. Thanks in advance.

1) On the total for ad# I inserted a summary with distinctcount and it give me 82 which is incorrect. It should be 13 for the total report:

2
1
2
2
2
1
1
2
--
13 SUM

If you have any other ideas, please let me know.

In order for my total lineage I had to do the following:
running total:
Field to Summarize:
effective_lines

On Change of Group:
ad_num

reset:
Never

 

[lbass]

The inserted distinctcount should work--unless you have some rows suppressed or you are using group selection. Are either of these the case? If so, you would need to use a running total. In the case of group selection, you would use a running total which does a distinctcount of {table.ad#}, evaluate for each record, reset never. In the case of suppression, you would do the same except for the in the evaluation section you would need to choose "evaluate based on a formula" and then enter the opposite of your suppression criteria into the formula area.

-LB

 

[brxmas]

LB,

In this 'Crystal' report I need to group it by ad# with all it's totals and then by Insertion Date.

36688 10047300 5/23/2004 141.80 40.0
4335 13742180 5/02/2004 26.35

In the example above there were 5 ads for 36688, the first insertion date is 05/02/2004 through 05/23/2004. I only want to see the first day 05/02/2004 with the sum for that ad #. But, when I report prints is prints the last date 05/23/2004.

 

[lbass]

This is a new problem? You could use group selection. Go to report->edit selection formula->GROUP and enter:

{table.insertiondate} = minimum({table.insertiondate},{table.ad#})

-LB

 

[brxmas]

LB,

I get an error: This function cannot be used because it must be evaluated later.

 

[brxmas]

LB,

That worked, sorry.

Groups: ad #
insertion date

I want it to be ascending order by date 05/02/2005

So, in my report I would see these ads with sum data
36688 05/02/2005
43355 05/02/2005

57235 05/02/2005
17312 05/02/2005

etc.

Thanks for your help....

 

[lbass]

Go into your detail section and right click on the the date field and insert a summary (minimum) at the ad# group level. Then go to report->topN/group summary and choose "minimum of {table.insertiondate}" as your sort field ascending.

-LB