date prompt not working!?

building semantic layer
1

Okay, I know this was working but couple days later getting an error message… Incorrect syntax? .

CASE
WHEN @Prompt(‘Enter CutOff Date’,‘D’,mono,free) <= TransDate Then Balance
AgeCurr,
WHEN @Prompt(‘Enter CutOff Date’,‘D’,mono,free) > TransDate and DATEDIFF(day,
TransDate, @Prompt(‘Enter CutOff Date’,‘D’,mono,free) <= 30 Then Balance Age1To30,
WHEN
…
END

ystu15 (BOB member since 2011-02-10)

2

What’s the exact syntax, where are you creating it and what is the underlying database?

3

SQL Server

The total syntax is as it appears; aside from the missing lines but even if I just use the first part of the syntax… up to AgeCurr END
I get an invalid syntax message.

This worked all last week!? Nothing changes as I know it.? :?:

ystu15 (BOB member since 2011-02-10)

4

:reallymad: What error message? How do you think we can help you without the error?

Does Webi let you see the SQL after the error (once it has replaced @Prompt with something else)?

Looking at your code, I would think of a mess between the US an European dates formats (DD/MM/YYYY and MM/DD/YYYY), you can try raising the error with 16/03/2016, 03/16/2016 and 01/01/2016. Just an idea.

KFonMurphi :fr: (BOB member since 2007-10-16)

5

I’ve no idea how it worked before then!

CASE 
 WHEN @Prompt('Enter CutOff Date','D',,mono,free) <= TransDate Then Balance AgeCurr END

That will not work. What is Balance AgeCurr?

6

Parse failed: Exception:DBD…
Incorrect syntax near ‘AgeCurr’, State: 42000

Or maybe its that the Parsing will always fail. Trying it in the report…

ystu15 (BOB member since 2011-02-10)

7

I think you got it ystu…

I have worked with multiple DBs and the Universe. I can say that just about every time I used a date field in some type of calculated or prompted object I never got it to Parse.
That said however, the object worked fine in the report so I think your guess is correct.

toscajo (BOB member since 2002-09-04)

8

I’ll ask again:

What is Balance AgeCurr.