Presentation Variable for Prompt using "Between" operator

Dimitris Karydis

Is it possible to create a variable which gets the value of just the second part of a prompt using the "between" operator?

e.g. in the below screenshot, when the users selects a week ID range between 2018-02 and 2018-06, the variable should have the value '2018-06'

Robert Angel

Hi,

I am sure I have done similar, yes.

Just 'catch' the variable in a filter, against, a dimension text field to see what it passes (look in the log or examine the where clause in the SQL evaluated when it gives you no results), chances are you can use a simple piece of substr instr type nested logic to pull the part of the presentation variable out. Note you may have to cast that result into a numeric depending on your underlying data type on the field in question.

Gianni Ceresa

As Robert said, I guess the 2 values are just separated by a comma ',' when looking at the variable from a "text" point of view. Display the variable to validate the separator and then substring based on the position of the separator (or also the X characters from the right of the variable if your weeks IDs are always 7 characters long).

Martin van Donselaar

Late to the party because I was trying  what Robert and Gianni already said and it works as described.

Make sure tot put the reference between quotes for it to work, like '@{pVar}'.

EDIT: I realize now your prompt is on weeknrs not dates, so the quotes might not be needed here, test it for yourself and you will know :-)

Robert Angel

There is also a weird string generated for the 'All Values' value, so you can also emulate that logic with a simple case statement....

Dimitris Karydis

It looks like it passes no value to the presentation variable as it expects one value and the between operator has two

Robert Angel

Hi Dimitris,

what I am suggesting is that you use your {PresentationVariableName} in the filter itself, using some sql to parse out the values first.

You may have to use 'Is Protected' to prevent the default prompt behaviour from causing your error above.

Martin van Donselaar

Did you use the quotes as suggested? : like  '@{pWeekId}'

Dimitris Karydis

Interesting.

You are right, the presentation variable has two values separated by comma. I'll try to check what i can do with the sql and return to this thread if needed

Thanks

Dimitris Karydis

The quotes worked too. Below is a screenshot, the first column has the values getting back when adding the presentation variable in the filter (the sql has a [T95.columnname in ('2014-01', '2014-03')] statement) and the second one is the presentation variable after adding the quotes in answers (I suppose I had to use the quotes to make it a string??).

Thanks again, now I have two possible solutions to work with

Gianni Ceresa

Dimitris Karydis wrote:(I suppose I had to use the quotes to make it a string??).

To make it a string in the physical query. The value of a formula is sent as such to the DB, which allows you to query dynamic columns by storing their names in variables etc. So to not make your physical query invalid you need the quotes around it. (It doesn't cast the variable, it's really just about how the query is written)

So you can close the thread as you got your reply...