Question Re: Exclude blank excel rows from report in Technology Q&A

Exclude blank excel rows from report

Former Member — Fri, 29 May 2015 07:21:39 GMT

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

Re: Exclude blank excel rows from report

former_member207052 — Fri, 29 May 2015 07:31:59 GMT

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.

Re: Exclude blank excel rows from report

tanveer1 — Fri, 29 May 2015 07:34:01 GMT

Hi Ankit,

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

Thanks,

Tanveer.

Re: Exclude blank excel rows from report

Former Member — Fri, 29 May 2015 07:42:50 GMT

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

Re: Exclude blank excel rows from report

Former Member — Fri, 29 May 2015 07:47:54 GMT

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

Re: Exclude blank excel rows from report

tanveer1 — Fri, 29 May 2015 07:51:14 GMT

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

Re: Exclude blank excel rows from report

Former Member — Fri, 29 May 2015 08:05:26 GMT

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

Thanks,

Ankit

Re: Exclude blank excel rows from report

Former Member — Fri, 29 May 2015 08:59:15 GMT

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.

Re: Exclude blank excel rows from report

Former Member — Fri, 29 May 2015 10:35:19 GMT

Hi Mark,

Sheet meant to excel sheet that am using as datasource.

Thanks,

Ankit

Re: Exclude blank excel rows from report

Former Member — Fri, 29 May 2015 10:42:08 GMT

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

Re: Exclude blank excel rows from report

tanveer1 — Fri, 29 May 2015 10:44:21 GMT

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.