Exclude blank excel rows from report

[Former Member]

Hi,

I am using excel sheet as a datasource in webi. We have some rows whose all columns are blank but keyfigure columns have 0.0 value.

I want to webi report should exclude rows which are blank in all columns. Is there any way I can achieve it through settings in report filter.

Please guide.

Thanks,

Ankit

[tanveer1 · Accepted solution]

Hi Ankit,

Right Click table >> format table >> uncheck the setting Show rows for whihc sum of measures = 0

Thanks,

Tanveer.

[Former Member · Accepted solution]

Hi Tanveer,

I cannot use key figure = 0 filter since there are some where characteristic values are present but kf = 0 and we have to keep those rows in report.

Is there any setting that I can to with characteristic in report.

Thanks,

Ankit

[tanveer1 · Accepted solution]

Hi,

Create a varaible as

= isnull([Char1] and isnull([Char2] and isnull([Char3]  and......etc.,.

Use this in the table and apply filter = 0

Thanks,

Tanveer

[Former Member · Accepted solution]

Yes already implemented that formula, since columns are getting added in sheet on weekly basis. So cannot rely on the formula.

Thanks,

Ankit

[Former Member · Accepted solution]

When you say the sheet, do you mean the report block?

If so, then you'll simply have to maintain the formula to add extra null checks.

[Former Member · Accepted solution]

Hi Mark,

Sheet meant to excel sheet that am using as datasource.

Thanks,

Ankit

[Former Member · Accepted solution]

Are the extra columns just appearing in your BO report or are you having to add them?

[tanveer1 · Accepted solution]

Hi Ankit,

If you have excel as data source with columns keep on appending then you can create an excel based formula in the sheet itself as one column which checks for nulls and use it in the report as a filter

Thanks,

Tanveer.

[former_member207052]

Yeah cannot do that during data acquisition. But you can do it at the report level using the report(or block level) filter as shown below.

[Former Member]

Hi Nks,

I have 20 to 25 char columns in excel that's why I asked on setting in report filter.

If all columns are blank then exclude those values.

Thanks,

Ankit