Records not appearing unless an unwanted field is included!!!

[Dandas]

Hi.

I am trying to modify a report by displaying 3 calculated fileds on it. Not all calculated fields will return a value, and I only want the fileds that do have a value to be displayed. ie. I do not want any records on the whole report unless there is data in the calculated colums.

So I inserted the calculated fields and viewed the report and some had data like I expected. GOOD.

I then went back in to design and deleted the original fileds so that only the 3 calculated ones were left. I tried to view the report again but this time all of the data was missing.

I then closed it without saving and went back in and deleted the fields one by one to see which one was affecting the report. It turns out that if I delete a field which is a site code (just a code to represent which office the data is from) then all the data dissapears. If I include it there are heaps of records that only have the site code and every now and then a record that has both the site code and also data in the 3 calculated cells.

Anyone have any ideas how I can just get a record for the 3 Calculated cells only.

Thanks in advance.

 

[Ngolem]

You should outline the tables you are using and their linkages

You should also post your record selection formula so we can take a look at it.Keeping records only when calculated values are present is tough in Crystal...sometimes the calculations can be written as an SQl expression and passed to the server but usually more records are returned then needed and you have to sift through them.

Let us say that you have 3 calculations

@CalcA @CalcB @CalcC

You can put the following formula in The conditional suppress for the section where the results are shown

WhilePrintingRecords;
EvaluateAfter({@CalcA});
EvaluateAfter({@CalcB});
EvaluateAfter({@CalcC});

//choose this if all must be not-null
{@CalcA} <> &quot;&quot; and {@CalcB} <> &quot;&quot; and {@CalcC} <> &quot;&quot;;

//OR if you want records where at least one is not-null
{@CalcA} <> &quot;&quot; or {@CalcB} <> &quot;&quot; or {@CalcC} <> &quot;&quot;;






Jim Broadbent

 

[dgillz]

Place the field that you do not want back onto the report, then suppress it.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

dgilsdorf@trianglepartners.com

 

[Bennie47250]

I had a similar probloem last week and found it had to do with the way I was linking my tables. Are you linking any tables on this report? If you are, you may want to verify you are linking them the &quot;best&quot; way.

Bennie

 

[Dandas]

Dgills:

I actually tried that first and I got pages of blank rows, and data in the calculated field every now and then. It seems to still create a record for the cell I supress but it is invisible.

Bennie47250:

You could be right but I am not really experienced in linking tables. Sure I know how to make a link but I don't really know what is considered the &quot;best&quot; way. I am using/modifying standard templates which came with the software when we bought it and the template tables are already linked.

Ngolem:

These are my calculated fields;

@Activity Duration

if {VW_EVT_AGENT_ACTIVITY.AA_TYPE} in [3,4] and
Previous ({VW_EVT_AGENT_ACTIVITY.AA_TYPE}) IN [5] and
NEXT ({VW_EVT_AGENT_ACTIVITY.AA_TYPE}) IN [5]then

totext ({VW_EVT_AGENT_ACTIVITY.AA_ACTIVITY})+(&quot; &quot+RISecondsToHMS({VW_EVT_AGENT_ACTIVITY.AA_CALL_DURATION})
else
&quot;&quot;

@Call Work 2

if {VW_EVT_AGENT_ACTIVITY.AA_TYPE} in [3,4] and
Previous ({VW_EVT_AGENT_ACTIVITY.AA_TYPE}) IN [5] and
NEXT ({VW_EVT_AGENT_ACTIVITY.AA_TYPE}) IN [5]then

totext (Next({VW_EVT_AGENT_ACTIVITY.AA_ACTIVITY})+(&quot; &quot+RISecondsToHMS(Next({VW_EVT_AGENT_ACTIVITY.AA_CALL_DURATION})))
else
&quot;&quot;

@DurTime

WhilePrintingRecords;

if {VW_EVT_AGENT_ACTIVITY.AA_TYPE} in [3,4] and

Previous ({VW_EVT_AGENT_ACTIVITY.AA_TYPE}) IN [5] and
NEXT ({VW_EVT_AGENT_ACTIVITY.AA_TYPE}) IN [5]
Then
RISecondsToHMS (next({VW_EVT_AGENT_ACTIVITY.AA_CALL_DURATION})+previous({VW_EVT_AGENT_ACTIVITY.AA_CALL_DURATION}))
else
&quot;&quot;

This is the Static field that I have to include

VW_CFG_SYSTEM.SITE_CODE

And this is the selection formula

WHERE
VW_CFG_SYSTEM.&quot;SITE&quot; = VW_EVT_AGENT_ACTIVITY.&quot;AA_SITE&quot; AND
VW_EVT_AGENT_ACTIVITY.&quot;AA_SITE&quot; = VW_CFG_STAFF.&quot;SITE&quot; AND
VW_EVT_AGENT_ACTIVITY.&quot;AA_STAFF&quot; = VW_CFG_STAFF.&quot;STAFF&quot; AND
VW_EVT_AGENT_ACTIVITY.&quot;AA_STAFF_VERSION&quot; = VW_CFG_STAFF.&quot;STAFF_VERSION&quot; AND
VW_CFG_STAFF.&quot;SITE&quot; = VW_CFG_STAFF_SUPV.&quot;SITE&quot; AND
VW_CFG_STAFF.&quot;SUPERVISOR&quot; = VW_CFG_STAFF_SUPV.&quot;STAFF&quot; AND
VW_CFG_STAFF.&quot;SUPERVISOR_VERSION&quot; = VW_CFG_STAFF_SUPV.&quot;STAFF_VERSION&quot; AND
VW_EVT_AGENT_ACTIVITY.&quot;AA_STAFF&quot; >= 1 AND
VW_EVT_AGENT_ACTIVITY.&quot;AA_STAFF&quot; <= 999999999 AND
VW_CFG_STAFF.&quot;SUPERVISOR&quot; >= 1 AND
VW_CFG_STAFF.&quot;SUPERVISOR&quot; <= 999999999 AND
VW_EVT_AGENT_ACTIVITY.&quot;AA_TIME_STAMP&quot; >= ? AND
VW_EVT_AGENT_ACTIVITY.&quot;AA_TIME_STAMP&quot; <= ? AND
VW_CFG_SYSTEM.&quot;SITE_CODE&quot; = 'GEEA' AND
VW_CFG_STAFF_SUPV.&quot;STAFF_TYPE&quot; = 2

I'm not sure how you want me to display the linkages???

Thanks for your help guys, I hope this info makes it a little easier.

 

[Dandas]

Ngolem:

I forgot to mention I used your formula and it did not make a difference that I could see.

 

[Dandas]

Anyone have any new suggestions?

Thanks

 

[lbass]

I think you should try a combination of Dgillz' and Ngolem's suggestions:

Put the field back in the report and suppress--this will return the wanted records with many unwanted ones as well. Then go to format section->detail->suppress->E-2 and enter:

{@Activity Duration} = &quot;&quot; and
{@Call Work 2} = &quot;&quot; and
{@Dur Time} = &quot;&quot;

This will allow only those records to appear in which at least one of these formulas has a value.

-LB

 

[Dandas]

Guys......

I LOVE YOU !!!

It worked, It is totally perfect now.

You all totlly rock !!!

Thank God for TEK-TIPS