combining certain lines into one line

[DJWheezyWeez]

I'm using CR XI.

Let's say I have something like this:

Letter   Number
A         0
B         20000
C         3000000
Ca        1000000
D         6000000
Da        30000

If I want to combine C, Ca, and D into D, I made this formula to do that:

if {table.Letter} = "C"
then "D"
else if {table.Letter} = "Ca"
then "D"
else if {table.Letter} = "D"
then "D"
else {table.Letter}

What I'm trying to do is sum Number for each letter using the new formula. The problem is that the Number column is actually a subreport. I'm trying to do a normal sum formula but when I use the shared numbervar, it says I need a field there and can't use the shared numbervar.

I realize I'm probably missing a lot of info for someone to figure out what's going on but anyone have any ideas around this? I can try to give more info if requested.

-DJWW

 

[lbass]

This is unclear. What is the result you would expect to see for the above sample?

Does each letter represent a group? Is the subreport linked on the letter field? If so, then assuming that your shared numbervar is returning the correct amount in the main report (let's say the sub is in GF_1a), insert a new section below (GF_1b), and create a formula like this and place it in GF_1b:

whileprintingrecords;
shared numbervar x;
numbervar sumx := sumx + x;

Then in the report footer use this formula:

whileprintingrecords;
numbervar sumx;

-LB

 

[lbass]

Actually if you only want to accumulate certain summaries, change the first formula to:

whileprintingrecords;
shared numbervar x;
numbervar sumx;
if {table.letter} in ["C","Ca","D"] then
sumx := sumx + x;

-LB

 

[DJWheezyWeez]

Sorry it was unclear but I was able to use the formulas you gave me in different sections. It works great, thanks! To answer your other questions, {table.letter} is actually in GH2b and the subreport is linked by a CustCode, not the Letter field but it still worked out.

Now since I have three lines with the same name of D, is there a way to suppress two of the lines so there's only one line for D and still keep the total of all three?

 

[lbass]

In what section is the subreport? What section do you mean when you say "line"?

-LB

 

[DJWheezyWeez]

The subreport is in GH2a and by line I mean all the data in GH2b. The Number field is actually a sum grabbing data from the subreport. In my first post for this thread, the Letter field is all in GH2b and with that formula changing the data "C", "Ca", and "D" all to "D" and the formula you gave me to sum the number values for "C", "Ca", and "D", I now have:

Letter   Number
A         0
B         20000
D         3000000
D         4000000
D         10000000
Da        30000

Is there a way to sum the number field so instead of three lines of data all reading "D" and three separate sums I would have one line of data and one sum reading 10000000?

I hope this is making more sense. What I want the end result to be is:

Letter   Number
A         0
B         20000
D         10000000
Da        30000

Just one line of data for "D".

 

[DJWheezyWeez]

I try using the following formula in the Section Expert under the Suppress by a formula button:
if {@Letter} = "D" then not onlastrecord
It suppresses those lines like I want but it leaves blank space as well as suppresses the data on those lines which I don't want. I tried {@Number} = maximum({@Number}, {@Letter}) but {@Number} is a cummulative sum so I get the error "This field cannot be summarized". Any way around this?

-DJWW

 

[lbass]

I think you should change your Group #2 field (remove it and add a new Group #2) to be based on a formula like this:

if {table.letter} in ["C","Ca","D"] then
"D" else
{table.letter}

Then link the subreport to the main report on this formula. Then you won't need to use a conditional clause in your variable formula.

-LB

 

[DJWheezyWeez]

Well I probably did something wrong since I'm being very general with the information but it didn't work. I was going to upload a screenshot of my problem but all the sites to upload files to are blocked so I will resort to attempting to type out my screen.

I'll put it in code so it all lines up:

RH  | [Title]
    | {@ResetSumAvantiVision} <-suppressed
PH  | [Customer]   [Last Year]
GH1 | {AnalysisCode.Desc}  {@ResetSumAvantiVisionREP} <-suppressed
GH2a| {{@ResetSumAvantiVision} <-suppressed   [Avanti 2008 (HIDDEN)] <-subreport
GH2b| {Customer.CustName}   {@SalesLastYear}
GH3 | {@letter}             {@lb LY}
Deta|         (suppressed)
ils |         (suppressed)
GF3 |         (suppressed)
GF2 |         (suppressed)
GF1 | {AnalysisCode.Desc}   {@SalesLastYearREP}
RF  |                       {@SalesLastYearGT}

Here are the contents of all the formulas in there:

@ResetSumAvantiVision
shared numbervar ytdLYAvanti;
shared numbervar ytdLY;
shared numbervar ytdLYbyZach;
ytdLYAvanti := 0;
ytdLY := 0;
ytdLYbyZach := 0

@ResetSumAvantiVisionREP
shared numbervar ytdLYAvantiREP;
shared numbervar ytdLYREP;
ytdLYAvantiREP := 0;
ytdLYREP := 0

@SalesLastYear
shared numbervar ytdLYAvanti;
shared numbervar ytdLY;
shared numbervar ytdLYbyZach;
ytdLYbyZach := ytdLYAvanti + ytdLY;
ytdLYbyZach

@letter
if {PV_Customer.CustName} = "Dr. Pepper Snapple Group"
then "Dr. Pepper Snapple Group"
else if {PV_Customer.CustName} = "CSAB/Snapple"
then "Dr. Pepper Snapple Group"
else if {PV_Customer.CustName} = "CSAB"
then "Dr. Pepper Snapple Group"
else {PV_Customer.CustName}

@lb LY
whileprintingrecords;
shared numbervar ytdLYbyZach;
shared numbervar sumx;
if {PV_Customer.CustName} in ["CSAB","CSAB/Snapple","Dr. Pepper Snapple Group"]
then sumx := sumx + ytdLYbyZach
else ytdLYbyZach

@SalesLastYearREP
shared numbervar ytdLYAvantiREP;
shared numbervar ytdLYREP;
ytdLYAvantiREP + ytdLYREP

@SalesLastYearGT
shared numbervar ytdLYAvantiGT;
shared numbervar ytdLYGT;
ytdLYAvantiGT + ytdLYGT

Here's the subreport:

RH  |                                    (suppressed)
GH1 | {Avanti.CustCode}                  (suppressed)
Deta| {@amount}   {@YTDLastYear}         (suppressed)
GF1 | {Sum of Avanti.Amount}
RF  |                                    (suppressed)

Subreport formulas:

@amount
shared numbervar amount;
amount := amount + {Avanti.Amount};
amount

@YTDLastYear
shared numbervar ytdLYAvanti;
shared numbervar ytdLYAvantiREP;
shared numbervar ytdLYAvantiGT;
ytdLYAvanti := ytdLYAvanti + {Avanti.Amount};
ytdLYAvantiREP := ytdLYAvantiREP + {Avanti.Amount};
ytdLYAvantiGT := ytdLYAvantiGT + {Avanti.Amount}

GH2a has Underlay Following Sections checked and the subreport is linked on CustCode.

Here's the report after it's run:

PH  | [Customer]                    [LastYear]
GH1 | Iovo
GH2b| Artisan Press                         0
GH3 | Artisan Press                         0
GH2b| Big Red, Inc.                     21561
GH3 | Big Red, Inc.                     21561
GH2b| CSAB                            3036446
GH3 | Dr. Pepper Snapple Group        3036446~
GH2b| CSAB/Snapple                    1011049
GH3 | Dr. Pepper Snapple Group        4047494~
GH2b| Dr. Pepper Snapple Group        6012515
GH3 | Dr. Pepper Snapple Group       10060010~
GH2b| Dr. Smoothie Enterprises          32621
GH3 | Dr. Smoothie Enterprises          32621
GH2b| Penta Water Company Inc.          40873
GH3 | Penta Water Company Inc.          40873
GH2b| San Manual Water                  31891
GH3 | San Manual Water                  31891
GH2b| Vista Del Mar Food & Bev.             0
GH3 | Vista Del Mar Food & Bev.             0
GF1 |                  Iovo          10186955

The last line is the total for this sales person. It's adding each GH2b line.

Hopefully this makes a little more sense. The report has twice the data since I'm keeping everything there until it works then I'll suppress some sections I don't need showing on the report. The lines with the tildes next to the numbers are summing up the lines of CSAB, CSAB/Snapple, and Dr. Pepper Snapple Group. What I eventually want is only the GH3 lines and only one GH3 line for Dr. Pepper Snapple Group with the last summed up number.

-DJWW

 

[lbass]

I can't tell what fields you are grouping on. Please identify them according to group #.

Also, what exactly did you try that didn't work? And what was the result you got and in what way was that inaccurate?

-LB

 

[DJWheezyWeez]

It's grouped by AnalysisCode.Desc, then Customer.CustName, then @letter. After your last suggestion, I ended up switching CustName and @letter so @letter was grouped 2nd and I just suppressed CustName in group 3. After that, all the data was 0s. I'm thinking there's a reset formula in a wierd spot or something that I'm not seeing. I'll play around with them.

 

[DJWheezyWeez]

Ok. So I played around with it and I think it ended up being that the @SalesLastYear formula was in GH3 instead of somewhere in GH2. I added a 3rd section in GH2. GH2a has the reset formula and the subreport, GH2b has the @SalesLastYear formula, and GH2c has @lb LY which is showing. Now instead of three lines on "Dr. Pepper Snapple Group", there's only one but it is no longer summing or it is and it is not summing any further than the first line. It has 3036446 as a value, not the 10060010 that I want there.