How to remove (Blank) value using URL Filtering

[MetrumOBE]

Hi the community,

I've published a report that need to be filtered via (url filtering) to remove (Blank) values.

By design, the report contains Report Level Filter doing this job by Advanced filter > is not blank.

Why using a report filter level, because I've several page in my report having to be filtered by this same value (is not blank).

My Issue :

When I tried to call the report via URL filtering, I don't get the expected result (Blank) are not filtered.

The URL used : ?filter=MARKET_MANAGER_D~2FMARKET_MANAGER_DESCR%20ne%20%27(Blank)%27

= ?filter=MARKET_MANAGER_D/MARKET_MANAGER_DESCR ne '(Blank)'

I've tried lots of combinasion without success 'blank', '', 'Blank', ...

The result :

Is there a specific way to fo this please ?

Thank you.

[v-frfei-msft · Accepted solution]

Hi @MetrumOBE,

Maggie Replied my that the writer of the online document.

It looks like it depends on the data type. Doing filter=Table/Column ne ‘’ will work if Column is a string type. But this doesn’t work for numbers or dates since it is trying to compare invalid data types to the empty string.

Our parser doesn’t have support for null as a right hand side expression, but we can add that functionality as part of the OData improvements after the accessibility work is finished. But for now this would only work for string values. There is a workaround by doing something like (filter=Table/Num gt L) where L is the lowest number in their dataset (or use “lt” and the value would be the largest number in that column). This would filter out empty values but you would need to know the high/low bounds of your dates and numbers.

Regards,

Frank

[MetrumOBE]

HI,

Thank you very much for your help.

The current field is in type String so it should work with ne ''.

But, when I tested it it didn't.

I would try maybe another approach on this.

I already though about your workaround proposal, I will test it.

It could be a good improvement for URL filtering for next release ;).

Many thank you.

Olivier

[v-frfei-msft]

Hi @MetrumOBE,

Based on my test, we cannot filter the blank value using this way directly. However we can create a calculated column in your data model switching the target column to numbers. Then we can use 'ge' to work around.

?filter = table/column ge number

For more details, please check the online document.

Regards,

Frank