I am using a reporting object pointed at a FOCUS file as my ...

[Tim Easley]

I am using a reporting object pointed at a FOCUS file as my data source. I created WHERE clauses in the RO to use as filters in a Designer page. I would like to exclude null values from the listbox values for the filter. I havent yet found a way to do this but thought I would post it here.

Screenshot attached:

[Martin Yergeau]

Can you share your RO and WHERE clause

[Tim Easley]

WHERE PS_QUERY_DETAILS.SEG01.SOURCE_COLUMNS EQ &SOURCE_COLUMNS.(OR(FIND PS_QUERY_DETAILS.SEG01.SOURCE_COLUMNS IN FMSBI/PS_QUERY_DETAILS |FORMAT=A34,SORT=ASCENDING)).SOURCE_COLUMNS:.;

[Martin Yergeau]

Seems that your choices are coming from FMSBI/PS_QUERY_DETAILS

Can you change that source to exclude the Nulls values

Need to have SOURCE_COLUMNS not null

[Warren Hinchliffe]

We are having similar issues, and I think there is a fix coming up in 8207.28 (Hopefully)

[Tim Easley]

Thanks Martin but Im not sure how I would do that without excluding them from the data too.

[Tim Easley]

Thanks Waz, I will keep an eye out for it.

[Martin Yergeau]

tim.easley:

 

Thanks Martin but Im not sure how I would do that without excluding them from the data too.

 

 

Assuming that its a static table (meaning not live data that constantly changing during the day), what you can do is to create an overnight job using Report Caster that will create a hold file where you will only keep valid records (remove null values) and using that hold file, you will create you filter.

[Tim Easley]

Ive not had any luck editing a reporting object to use any table (for filter values) other than that chosen when creating the RO. I manually edited the text in the filter to use a different file but it reverts back to the original when saving. I can see how this method would work when manually coding reports but reporting objects using dynamic parameters dont seem to offer the choice of using a different data set.

Thanks

[Manoj Chaurasia]

You can edit the way a filter gets populated with the property Accept within the metadata.

You could just create a WF procedure that filters out the nulls, and link it there