Hide # to display in Webi report or Hide Null values in fields ( not complete rows/tuples)

How to hide ‘#’ values (Null values) from the Webi report or how to filter any unwanted records from the webi report ?

Sometimes it’s really annoying to see “#” values in the Webi report, while you are looking the KPI values of any business scenario. This is a most common scenario where end-users don’t want to see # values for blank records which are coming from the source system.

This fix is not to hide the complete row in the webi report, but to hide the # values ( null values) to display if any of the source fields which are blank at the source table.

I have created few Webi reports and found null values showing as “#” (null values which display as # hexa-decimal in the asci world) in the Webi reports. There could be a scenario where the source field is not getting populated or some user fields can be blank on certain conditions. Hiding the null records at a tuple is different than this.

In the below report, the activity type is the user entry field from the employee timesheet, to describe the activity type, where it can be blank if the employee has not filled it and populated if it has a value.

If its blank (not entered by the employee), it will show as # . We don’t want to show a # value for blank records and keep it blank instead, if any values is existing, it will show as-is.

This can be achieved with the below formula.

=If(IsNull([Variable]);0;[Variable])

if any value is blank, display a “0” else show the value as-is. We have fine tuned the above formula in the below screenshot as the source value is “#”.

To achieve the result in our situation, create a formula variable in webi with the following option. On the left-hand side of the webi report in the available objects section, go to the variables section, right click on the variables folder-> select New -> and create a new variable as “ActivityType_Null” given below where activity_type is the original field name in the webi report. Replace “activity_type” with your field in the report and put it in the formula section – 2nd red box below.

=If(([Activity_Type] = “#”);””;[Activity_Type])

Click on OK and drag and drop the newly created field on the webi report to replace the original Activity_type field.

That is ActivityType_null will be your new Activity_type now. If the value is populated at the source, it will show that respective value else it will be blank.

Hope this helps to achieve a good display of the Webi report to your end-users /Stakeholders. This is more of a cosmetic change to get a better viewability.