Variable to list objects that have been filtered OUT

[former_member915918]

Standard procedure for the data analysts at our company is to include a page of report/query definitions, so the Business Users can see how the data pulled. For any filters in use (via Input Control), the definitions include a list of the data that has NOT been filtered out. In the case where there are MANY objects, the size of the list gets obnoxious and is not very helpful. Can anyone suggest a variable that will list the items that have been EXCLUDED, rather than listing the items that have been INCLUDED? I'm fairly new to BO, so any assistance would be really appreciated!

[ayman_salem]

In order to properly understand your requirement, please can include a screenshot of the sample data and the expected result.

[ayman_salem · Accepted solution]

(There's no out-of-box solution), but here's a trick on how to solve it:

Define the following variables:

v_ListAllCity: =If Pos(ReportFilterSummary(); "City") = 0 Then "All" Else NoFilter(ReportFilter([City]); All)

PosSelected: =Pos([v_ListAllCity]; [City])

v_ListUnselected: =Substr([v_ListAllCity]; 0; [PosSelected]-2) + Substr([v_ListAllCity]; [PosSelected]+Length([City]); Length([v_ListAllCity]))

Note: [City] in your case is [Subtype]

...

Hope this gives you some idea to meet your needs

[former_member915918]

Oh, ok. Well, thanks for taking the time to look into it. I appreciate it!

[ayman_salem]

If you found the answer OK, kindly accept it and close the question

[former_member915918]

This is sooooo close to what I was looking for (THANK YOU!). In my case, though, there will likely be several values selected for inclusion, not just a single value (as in "Boston" in your example above). And the resulting list of EXCLUDED items will only appear once in the entire document, on the Definitions & Filter report. After setting up the variables that you suggested above and pulling the object into a report, I got a MULTIVALUE error. See attached image for a sample of the Definitions & Filter report:

[ayman_salem]

As I said, there is no out-of-box solution, and my suggested trick works fine with input controls with only one item selected.

There is no solution for the multiple selected input control (since the split function is not available in BO).

Another solution is to use Javascript in your report to exclude the selected item (using the split function).

[former_member915918]

See screenshot with explanation