cancel
Showing results for 
Search instead for 
Did you mean: 

Deleting a particular row based on conditional

former_member197479
Active Participant
0 Kudos

Hello Experts,

I'll like to take advantage of the known expertise that all of you have on this topic to ask a question regarding data-handling in Crystal Reports.

Currently, I have an Account table, but there is one condition that I need to validate in order to complete the report itself.

Please check the following table as an example.

nbr(*)Account IDGroup CategoryAccount AreaAmount
110001General70102300
210002Costs80905000
310003General1100
410004Costs70109000
510001Sales2300
610005Sales80901050
710001Sales8090540
810001General2300

*the nbr(*) is just as reference to identify every line.

Based on that table, check the Account ID 10001, the idea of the condition is that it validates per row if the Account Area is set, then take the corresponding Account ID and remove or hide the rows where the Account Area is blank (So they won't be valid in grouping or summarizes)

nbr(*)Account IDGroup CategoryAccount AreaAmount
110001General70102300
210002Costs80905000
310003General1100
410004Costs70109000
510001Sales2300
610005Sales80901050
710001Sales8090540
810001General2300

So the result will be the following:

nbr(*)Account IDGroup CategoryAccount AreaAmount
110001General70102300
210002Costs80905000
310003General1100
410004Costs70109000
610005Sales80901050
710001Sales8090540

Check that the rows 5 and 8 are no longer in the table, then with that table it's only matter for me to group by Category and make some other validations but that's the one that is getting me crazy.

I'll really appreciate if someone could take a look and give me some advices.

Best regards and thanks in advance,

Melvin

Accepted Solutions (0)

Answers (1)

Answers (1)

DellSC
Active Contributor
0 Kudos

Do you want to remove all rows where there is no account area, or, as in your example, the first one will appear?

If you want to remove all of them, then use the Select Expert and include something like this in the selection formula:

not IsNull({MyTable.AccountArea})  <--Use your actual table and field name here.

This will keep any records that don't have an account area from even being pulled from the database, so you won't have to do anything special with them in the report.

-Dell

former_member197479
Active Participant
0 Kudos

Dell Stinnett-Christy,

I really appreciate you taking a look at this.

If I use that formula, it'll delete every single record where the Account Area is blank, (Check the result table row 3.)

The condition is more like, When a row has an Account Area, take each Account ID and look for where the same Account ID has a blank Account Area and delete it.

Thanks and regards

Melvin

Former Member
0 Kudos

Melvin

In that case you will need to add an account group.    then you can have at least one record per account.   and you could suppress the rows you don't want to see.  

Then you issue you have is that suppressing only hides the data, it does not remove the item from summaries or counts..  it only means you cant see it.  You'll need to do running totals with a formula to get the counts you want.

As Dell indicated,  it is really a problem that more properly needs to be addressed before the Data gets to crystal.  Either in the select statement in the selection statement or  the SQL procedure    or whatever you are using for a data source.      Crystal is "one way only" in the sense that it can not  delete, change or modify  or add  Data back in the source.   It can only report what is given to it

Ted

abhilash_kumar
Active Contributor
0 Kudos

Hi Melvin,

If you wish to do this within CR, try this:

1) Insert a group on the Account ID field

2) Suppress the Group Header and Group Footer

3) Go to the Section Expert > Highlight the Details Section > Click the formula button beside Suppress and use this code:

isnull({AccountArea}) AND Count({AccountArea},{AcountID}) > 1

-Abhilash

DellSC
Active Contributor
0 Kudos

Another way to do this would be to use a Command, which is a SQL Select statement, instead of linking tables and having Crystal build the SQL.  The basic logic would be something like this:

Select <ALL of the fields required for the report>

from <ALL of the tables for the report>

where (table.AccountArea is not null or

  not exists (Select 1 from table as table2 where table2.AccountID = table.AccountID and table2.AccountArea is not null)

)

This will filter out all of the data that you don't want to have in your report, so you don't have to account for those rows in your summaries.  You can see my blog for more information about working with commands: 

-Dell