Create a BOA Workbook variant to exclude a large amount of values

[Former Member]

Hi,

we are currently transferring our BEx Analyzer Workbooks to BO Analysis (Working with Version 1.4).

For a BEx WB our user created a variant which excludes about 800 specific articles from the selection. This was quite easy since those values were inserted by using a prepared  text file. We are aware that the text file functionality is not (yet) available in BOA. But even by using the clipboard insertion we have not been able to combine this with an exclude. Is there a way foreseen at all to copy values from clipboard and exclude them from selection?

Thanks in advance for any hint and regards,

Hans-Jörg

[Former Member · Accepted solution]

Hello Hans-Jörg,

just my 2 cents...

As you can see that the idea is already 2 years old, I would propose the following workaround:

This all works only if you don't get the "result set size limit exceeded" error... and you need to know how to work with VBA.

1) since you know the numbers/values which you want to exclude, you could store it in the same Excel as your Datasource is included, but on a second sheet ("bad list").

2) Now you select all the data without the exclusion restriction.

3) then you copy & paste the column with those values to the second sheet, too. Then remove all values which are available in the selected data but also in the "bad list".

4) Select the remaining numbers ("positive list") and fill them into the variable, which you wanted to use for exclusion:

Dim lResult As Long
Dim vntData As Variant

Set ObjRange = Range(Sheets("Table1").Cells(1, 1), Sheets("Table1").Cells(3, 3))
vntData = ObjRange.value

lResult=Application.Run("SAPSetVariable", "<techn-var-name>", vntData, "INPUT_STRING", "DS_3")

Then refresh the query again... voila, exclusion by inclusion 😉

Unfortunately If you want to re-do this, or save the Workbook, the Input varible will keep those values and you need to clear it by Setting one Default value, which you can remove manually in the variable Screen.

It needs a little bit experimenting, but it could work.

Best regards, Martin

[TammyPowlas]

Hello Hans-Jörg,

Would you please look at Ingo's suggestion as a possible option/work-around? Pasting a list of values to Exclude in Prompt | SCN

[Former Member]

Dear Tammy,

Thanks a lot for that hint. However, we tried the proposed workaround but found no way to define a Variable that allows to exclude values when it's not a single value entry variable.

The idea is (like it was possible in BEx Analyzer) just to upload the list of values from clipboard during report execution, set the operator to exclude and save as a variant afterwards. I suppose this remains a gap between BEx and BOA then?

Best regards,

Hans-Jörg

[TammyPowlas]

Hello Hans-Jörg,

There is an idea on SAP Idea place for this - if it matches your requirement I encourage you to vote for it:

Fiter out members (inverse selection / excluding) : View Idea

[Former Member]

Dear Tammy,

Thanks a lot again. I placed my vote - let's see...

However any working workarund idea for the time being would be appreciated as well since the request in the idea place is pending since 2013 already.

Best regards,

Hans-Jörg