MikeM670
asked on
Formula Returns blank lines along with correct data
Crystal Reports 2016
MS SQL
The problem I have is a formula that produces blank results along with the appropriate value. You can see the blank spaces as highlighted blue lines. See image 1
In this example I am looking at a specific record. In the finalized report I will be looking at records based on dates. But to simplify this example I am using a specific test record.
Record Selection Formula:
{CivilPMaster.CivilPYear} = 2018 AND {CivilPMaster.CivilPID} = 241
The formula I used to display the Plaintiff's Name.
Formula: Plaintiff
IF {CivilPPersons.Involvement
THEN {CivilPPersons.Name} & ', ' & {CivilPPersons.Given1} & ' ' & {CivilPPersons.Given2}
Table: CivilPPersons
CivilPYear int
CivilPID int
Sequence int ( Not Really Needed just using for reference.)
PersonID int ( Not Really Needed just using for reference.)
Business char ( Not Really Needed just using for reference.)
Name varchar
Given1 varchar
Given2 varchar
Given2 varchar
Involvement varchar
Here is a view of the sql results in manager.
The number of lines both with data and blank corresponds with the number of lines shown in the sql results. Is this coincidence?
MS SQL
The problem I have is a formula that produces blank results along with the appropriate value. You can see the blank spaces as highlighted blue lines. See image 1
In this example I am looking at a specific record. In the finalized report I will be looking at records based on dates. But to simplify this example I am using a specific test record.
Record Selection Formula:
{CivilPMaster.CivilPYear} = 2018 AND {CivilPMaster.CivilPID} = 241
The formula I used to display the Plaintiff's Name.
Formula: Plaintiff
IF {CivilPPersons.Involvement
THEN {CivilPPersons.Name} & ', ' & {CivilPPersons.Given1} & ' ' & {CivilPPersons.Given2}
Table: CivilPPersons
CivilPYear int
CivilPID int
Sequence int ( Not Really Needed just using for reference.)
PersonID int ( Not Really Needed just using for reference.)
Business char ( Not Really Needed just using for reference.)
Name varchar
Given1 varchar
Given2 varchar
Given2 varchar
Involvement varchar
Here is a view of the sql results in manager.
The number of lines both with data and blank corresponds with the number of lines shown in the sql results. Is this coincidence?
Agree.
Another way to do it is in the formula there should be a dropdown that says ERROR FOR NULL change to the other option DEFAULT VALUE FOR NULL
You probably only need it on the middle initial
mlmcc
Another way to do it is in the formula there should be a dropdown that says ERROR FOR NULL change to the other option DEFAULT VALUE FOR NULL
IF {CivilPPersons.Involvement} = 'PLAINTIFF'
THEN IsNull({CivilPPersons.Name},' ') & ', ' & IsNull({CivilPPersons.Given1}, ' ') & ' ' & IsNull({CivilPPersons.Given2}, ' ')You probably only need it on the middle initial
mlmcc
ASKER
I get an error for your suggestions. To many arguments given for this function.
IF {CivilPPersons.Involvement} = 'PLAINTIFF'
THEN IsNull({CivilPPersons.Name},' ') & ', ' & IsNull({CivilPPersons.Given1}, ' ') & ' ' & IsNull({CivilPPersons.Given2}, ' ')
IsNull() doesn't accept a second argument.
Just use IF IsNull({YourField}) Then ... ELSE ...
or Use IIF(IsNull({YourField}), result if true, result if false)
Just use IF IsNull({YourField}) Then ... ELSE ...
or Use IIF(IsNull({YourField}), result if true, result if false)
ASKER
Ok...not sure we are going down the right path here. I can check for Nulls even tho there are no actual nulls in the actual data. See original posting. The issue is there is only one person who is a plaintiff. But the formula returns lines for all five persons in the database where it should just return the one. The other four lines are blank.
Change your record selection formula from
{CivilPMaster.CivilPYear} = 2018 AND {CivilPMaster.CivilPID} = 241{CivilPMaster.CivilPYear} = 2018 AND {CivilPMaster.CivilPID} = 241 AND {CivilPPersons.Involvement} = 'PLAINTIFF'ASKER
That did work. However I also need to find the person who is a Defendant. I would use the same formula but replace 'Plaintiff' with 'Defendant'. So when I did
it now returns two lines. I Not sure I understand why this happens since I'm using a formula to only display the line that contains "Defendant"
Beginning to think I can't do it this way. Will I have to use a sub report for each of these?
For reference here is what I am trying to design. A daily register of transactions that occurred.
{CivilPMaster.CivilPYear} = 2018 AND {CivilPMaster.CivilPID} = 241 AND ({CivilPPersons.Involvement} = 'PLAINTIFF' or {CivilPPersons.Involvement} = 'DEFENDANT')it now returns two lines. I Not sure I understand why this happens since I'm using a formula to only display the line that contains "Defendant"
Beginning to think I can't do it this way. Will I have to use a sub report for each of these?
For reference here is what I am trying to design. A daily register of transactions that occurred.
If you don't want to see both records, Group by {CivilPMaster.CivilPID}, suppress the detail section, and show the information you want in the Group Footer.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Check for NULL values using IsNull().