Variable Prompt not filtering Analysis

Anna Aj

I am trying to create a prompt for an analysis in OTBI. This analysis was created using Logical SQL. When I created a column prompt, I got the error - "Failed to Load(Odbc driver returned an error (SQLExecDirectW)" and the choice list did not populate. When I created a variable prompt using a SQL query, the choice list populated but the selections will not filter the analysis. Please has anyone encountered this before?

Nathan CCC

Hi,

First question "column prompt, I got the error" Was the column in your column prompt the same column from the same subject area as that selected in the sql in the variable prompt? I assume not so the first column may be from a different subject area for which your user does not have a job role that inherit the duty role to be allowed to query?

Second question "When I created a variable prompt … but the selections will not filter the analysis". This is expected. A variable prompt will put the selected value in a presentation variable. It is not a column prompt so is not linked to any column. Therefore in your analysis the prompt value will not be used by any of your filters with columns set to operator "is prompted". You will have to edit the filter operator from "is prompted" to hard code if it is as "is equal to / is in" or "greater than" or "less than" etc. then select Add More Options then select Presentation Variable than add the name of your variable from the prompt and optionally a default value if it passed as null so you can test your analysis without the prompt. This will cover most use cases but you may need to "convert this filter to SQL" if you want to do anything a little bit more complicated.