fitler fields displayed

[robmason10]

I have a report where the detail shows 5 fileds which are basically linked by matterID. One of the fields (MFE) is linked via a 'type' table and can contain multiple values for the matterID but only 1 value where the type = 7 - how do I get the report to select and display all matterID but only the MFE field where is type=7 - i do not want entries for each matter for all the other 'types' but do want to ensure that the report selects the matterID even if there is no type=7 entry?

Tx in advance

 

[SmpTraining]

To show all MatterIDs whether or not they have MFE = 7 you first need to link the tables with a left outer join type (if they are access tables this will already be the case).

So now you will have a report with all matterIDs and all MFE values.

You haven't said what the range of MFE values are but I am assuming 7 isn't either the lowest or highest value - if it is, then a slightly simpler solution is possible (Let me know if thats the case)

Add a group based on the matterID field and chose to sort them in "specific order" with 7 being the first group and on the 'others' tab chose to leave them in their own groups.

Copy all the fields from the details section to the Group Header section.

Suppress the Details section

Suppress the Group Footer Section

Conditionally Suppress the Group Header Section with the formula: MFE <> 7

Let me know how it goes.




Steve Phillips, Crystal Consultant

 

[synapsevampire]

Left Outer the join and in the record selection formula (Report->Edit Selection Formula-Record) place something like:

isnull({typetable.field})
or
{typetable.field} = 7

-k

 

[lbass]

Another approach would be to use a left join from the matter table to the type table and then create a formula {@typeMFE7} to use instead of the {type.MFE} field:

if is null({type.MFE}) or {type.MFE} <> 7 then 0 else {type.MFE}

As Steve suggested, group on {matterID} and then insert a sum on {@typeMFE7} and drag the sum to the header. Conditionally suppress this field with: {@typeMFE7} = 0

-LB