on 09-29-2015 1:33 AM
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 ID | Group Category | Account Area | Amount |
---|---|---|---|---|
1 | 10001 | General | 7010 | 2300 |
2 | 10002 | Costs | 8090 | 5000 |
3 | 10003 | General | 1100 | |
4 | 10004 | Costs | 7010 | 9000 |
5 | 10001 | Sales | 2300 | |
6 | 10005 | Sales | 8090 | 1050 |
7 | 10001 | Sales | 8090 | 540 |
8 | 10001 | General | 2300 |
*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 ID | Group Category | Account Area | Amount |
---|---|---|---|---|
1 | 10001 | General | 7010 | 2300 |
2 | 10002 | Costs | 8090 | 5000 |
3 | 10003 | General | 1100 | |
4 | 10004 | Costs | 7010 | 9000 |
5 | 10001 | Sales | 2300 | |
6 | 10005 | Sales | 8090 | 1050 |
7 | 10001 | Sales | 8090 | 540 |
8 | 10001 | General | 2300 |
So the result will be the following:
nbr(*) | Account ID | Group Category | Account Area | Amount |
---|---|---|---|---|
1 | 10001 | General | 7010 | 2300 |
2 | 10002 | Costs | 8090 | 5000 |
3 | 10003 | General | 1100 | |
4 | 10004 | Costs | 7010 | 9000 |
6 | 10005 | Sales | 8090 | 1050 |
7 | 10001 | Sales | 8090 | 540 |
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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
User | Count |
---|---|
87 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.