How to supress the extra rows

[satyakumar]

Hi Folks,

I have a weired scenario and the data is coming like this in Crystal....

Did Group by --- code 1234
Service Name code amount
OUT PAUL 1234 -390
IN PAUL 1234 390
-------------------------------------
Group by code 1212

Service Name code amount
OUT RAM 1212 -290
IN RAM 1212 290
IN RAM 1212 290
---------------------------------------
Group by code 2346

Service Name code amount
IN KYLE 2346 500

Basically in Crystal the users are asking me to supress the rows, which a customer has the IN and OUT on the code.

the first code 1234 should not show because it has IN and OUT..
Coming to second code 1212 which has IN and OUT and again it has IN, So we should show the one with IN record.
Coming to the third code 2346 which has only one row with IN so we have to disply the IN

so the Data finally should look like this in Crystal...

Group by code 1212

Service Name code amount
IN RAM 1212 290
---------------------------------------
Group by code 2346

Service Name code amount
IN KYLE 2346 500

Please help me guys in solving this.

Note: For understanding purpose i added the column 1 which is Service(IN and OUT), But from the Data base side we are not getting in the report.


Thanks.....

 

[Turkbear]

Hi,
What determines which IN record should show if more that one exists for a group?

For Instance for your Group 1212 how did you decide which IN to show?






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

How do YOU know that something is IN vs. OUT? Is it just based on the positive or negative value? If so, does it matter which "In" row shows for RAM, i.e., are there other fields you are not displaying? Could there ever be a situation like this:


Name Code Amount
sam 4567 -234
sam 4567 -145
sam 4567 234
sam 4567 145

?

If so, how do you know which rows "go together". Some other unique ID field?

-LB

 

[satyakumar]

The only way to know which is IN and which is OUT is by -ve & +ve signs. All the OUT has -ve signs and all the IN have +ve sign.
OUT is nothing but VOID of IN.

In RAM Scenario (code 1212), we can display any IN. It doesn't matter. I already discussed with the Business users they are Good with that.

Also above situation does'nt come. I checked all the data and i did not find any scenario.

Thanks......

 

[Turkbear]

Hi,
If those are actual numeric fields then to get only INs you could have a selection formula like:

{Table.Amount} > 0

If,for some reason, thay are character strings then use:

Left({Table.Amount},1) = "+"





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

First sort by the amount field ascending. Then in the section expert->details->suppress, try a formula like this:

(
(
not onlastrecord and
{table.code}=next({table.code}) and
{table.amt}+next({table.amt})=0
) or
(
not onfirstrecord and
{table.code}=previous({table.code}) and
{table.amt}+previous({table.amt})=0
)
)

-LB

 

[satyakumar]

You are the King... lbass.... It worked Perfectly.....

 

[satyakumar]

Hi lbass....

The formulae that you gave me is working perfectly, But in one scenario its failing.

The scenario is....

Name Code Amount
Ken 1234 -77.57
Ken 1234 77.57
Ken 1234 77.57
Ken 1234 -77.57
Ken 1234 77.57

The correct Output we should get is the 3rd row which is
Ken 1234 77.57

But its supressing the 3rd row aswell. Can you please let me know what i have to do.

 

[lbass]

Is there a reason you can't just group on name and code (you can suppress unwanted group header or footers, and then drag the name and code into the inner group footer or header. Then you can just right click on the amount field and insert a sum at the group level. Then suppress the group section using a formula like this:

sum({table.amount},{table.group2field})=0

-LB