Problem Statement

Pass Empty values to dashboard query prompts based on condition in BI 4.0

Replicating the Issue

Dashboard is built on BW queries via query browser and optional prompts are defined which are populated through excel formulae. There is a combo box which is used to filter the data based on Calendar Year/Month. In addition to entries available via BW query, there is a manual entry for “All Months” in the dropdown

If we define the formula as shown in cell F4, it shows up the ‘Request Processing Failed..’ error. On debugging using Fiddler/ HTTPWatch, we get the error details: “No member found for caption “” for Calendar Year/Month”

Objective is to pass the logic – If <combo box selection> = “All Months”, “”, <combo box selection>

Workaround

Pass a row of blank cells instead of “” in the filter. So cell F4 =IF(D4=”ALL Months”,G4:G7,D4)

As seen below in Fiddler logs, the filter value is ‘skipped’ when it comes across a row of blank cells as input.

Using this method, you can pass blank values for filters via formula. This problem however does not exist for BI 4.1