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>
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