@prompt with CASE statement

building semantic layer
1

I need to build CASE…WHEN logic into a prompt as follows:

Case when <= ‘07’ then else

@Select(CLASS\object)<=@Prompt('Select Current Month as YYYYMM: ',‘A’,'CLASS\object,mono,constrained) AND @Select(CLASS\object) > (100*(@Prompt('Select Current Month as YYYYMM: ',‘A’,'CLASS\object’,mono,constrained)/100))

end

Due to the prompt having 2 parts the code does not parse. All it needs to do is prompt when the datepart is between 08 and 31 otherwise default to previous month.

I’m sure it is only a syntax issue. Could anyone help? thanks

frank35 :uk: (BOB member since 2005-08-25)

2

You’re missing a single quote after CLASS\object in the first @prompt.

joepeters :us: (BOB member since 2002-08-29)

3

sorry that was just a typo. Doesn’t solve issue.

frank35 :uk: (BOB member since 2005-08-25)

4

Ok, next: the highlighted part of your formula is a condition, but no CASE WHEN or result. You’re just saying “a <= b and c > d”. That should be wrapped in a CASE WHEN.

joepeters :us: (BOB member since 2002-08-29)

5

I tried this but gettimg error:

Case when <= ‘07’ then else

when > ‘07’ then

@Select(CLASS\object)<=@Prompt('Select Current Month as YYYYMM: ',‘A’,'CLASS\object,mono,constrained) AND @Select(CLASS\object) > (100*(@Prompt('Select Current Month as YYYYMM: ',‘A’,'CLASS\object’,mono,constrained)/100))

end

frank35 :uk: (BOB member since 2005-08-25)

6

You still aren’t telling it what you want to do if the month if > 7, or when that condition is not met.

joepeters :us: (BOB member since 2002-08-29)

7

Could it just be your Syntax?
CASE WHEN …
WHEN …
ELSE …
END

charlie :us: (BOB member since 2002-08-20)

8

What I want it to do is, if day in month is > 07 then I want it to prompt for a month e.g. 201009 but it will return all months of that year back to 201001.

Therefore if the current date is between 01 and 07 then it defaults to a predefined obejct i.e. last month.

If the current day in month is after 07 then I want it to prompt for the month but it will return that month and all prior months for that year. That’s why there are 2 parts to the prompt.

frank35 :uk: (BOB member since 2005-08-25)

9

Are you putting this in a predefined condition, or the SELECT clause of an object?

joepeters :us: (BOB member since 2002-08-29)

10

in a predefined condition

frank35 :uk: (BOB member since 2005-08-25)

11

The you need something completely different:

((
    <day number of month> <= '07' 
    AND @Select(CLASS\object) = <previous month YYYYMM>
)
OR
(
    <day number of month> > '07'
    AND @Select(CLASS\object) BETWEEN substr(@Prompt('Select Current Month as YYYYMM: ','A','CLASS\object,mono,constrained),1,4) || '01' 
                                  AND @Prompt('Select Current Month as YYYYMM: ','A','CLASS\object,mono,constrained)
))

joepeters :us: (BOB member since 2002-08-29)

12

Joe - thanks for that, I am leaving office now and will pick up tomorrow. Will get back to you.

frank35 :uk: (BOB member since 2005-08-25)

13

Regarding JoePeters solution: The problem is, that the prompt will pop up no matter what, because at this time there is no conditional prompting in SAP BusinessObjects.

Andreas :de: (BOB member since 2002-06-20)

14

Yes, true – if the month is prior to July, the prompt response will simply be ignored.

joepeters :us: (BOB member since 2002-08-29)

15

Thanks. It doesn’t like

|| ‘01’

What is the SQL Server equivalent?

frank35 :uk: (BOB member since 2005-08-25)

16

uh, plus sign, maybe?

joepeters :us: (BOB member since 2002-08-29)