When i make a cascading filter it sometimes happens that adding a filter to the list, filters items from the 1st filter.

 

 

When i make a filter City with 4 values: "Antwerp, Brussels, Milan, Paris" retrieved from my sql server 2005 database and i run the report it shows me those 4 values in a dropdown list. After that i add a new filter to that filter, with the values "John, Joe, Fred" also retrieved from my sql server 2005 database. Then the 1st filter only has 3 values: "Antwerp, Brussels, Milan".

 

I would really appriciate any help on this topic.

 

Thanks in advance

 

Ian De Herdt

Thank you for the fast reply, I am still trying to figure out where to apply the outer join.

 

The data that fills up the filters is a view from a SQL server 2005 database. And when i try to edit the filter i only see the following options:

(i'm on a dutch version so i'll try to translate/explain what i see)

- Filter text

- sort order

- only values with descriptions

- allow multiple values

- allow unique values

- allow a range of values

 

Also all the values do show up when i use the selection expert and assign a filter to a parameter, they show up in the drop down list below {?MyParameter?}

 

Kind regards,

 

Ian

You are using a view as your source. When you limit the scope of the view to 4 cities that changes your param to just show those. When you add in the extra limit of just a few people then your view is only returning rows that meet these conditions and so your params are only showing 3. basically your DB has no person that you are filtering on that is in Paris.

If you haev 2 tables in the view (one for city the other for person) you can write a more complicated view that left joins the two after the filters have been applied not before.

or use to views as your source 1 as a filterd view of the city table and the other as a filtered view of the people table and left join them in crystal.

 

However Lockwelle was correct from teh get go that if a user select Paris they will never get any name value for the person param because your data shows that does not exist. The report will be 0 rows for Paris so I am not sure why you want to do that.