system
November 7, 2003, 2:51pm
#1
Ok,
I’ve been trying to do this for a long time and I wanted to know if this is in fact possible. I want to use the information I receive from two prompts in a where clause. The 2 prompts are ‘Year From’ and ‘Year To’.
A hardcoded version of the Measure I’m trying to make looks something like this:
=<Sum - Quantity> Where(=2003) - <Sum - Quantity> Where(=2002)
What I need to do is replace the hardcoded 2002 and 2003 with the information I receive from my prompts, i.e ‘Year From’ and ‘Year To’. I figured that this should look something like this:
=<Sum - Quantity> Where(=UserResponse(“Query” ,“Year From”) - <Sum - Quantity> Where(=UserResponse(“Query” ,“Year To”))
This however yields a Syntax Error. I get the same error when I make the UserResponse into a Detail and use that like this: =.
Is it possible to do this and if so then how should I do it?
Cheers,
binni (BOB member since 2003-11-07)
system
November 7, 2003, 3:04pm
#2
Please, take a look at the BO Reporter FAQ …
Remember: Search is your Friend
Andreas (BOB member since 2002-06-20)
system
November 7, 2003, 3:09pm
#3
Thanks,
Is the solution making a boolean returning variable and putting that into the Where clause?
I’ll try that out.
binni (BOB member since 2003-11-07)
system
November 7, 2003, 3:12pm
#4
I tried it out and this variable text yields a syntax error just like before:
=( = )
So does:
=( = UserResponse(“Query” ,“Year From”))
Note that is an integer variable in the warehouse.
binni (BOB member since 2003-11-07)
system
November 7, 2003, 3:20pm
#5
Try using the BO Reporter function ToNumber to convert the UserResponse string to a number…
Andreas (BOB member since 2002-06-20)
system
November 7, 2003, 3:24pm
#6
Tried it as a new variable and in the query itself and it doesn’t work…just yields a Syntax Error.
binni (BOB member since 2003-11-07)
system
November 7, 2003, 3:25pm
#7
Yes, it should work. What is the exact code that gives you the Syntax Error (error code)?
For you Boolean variable try something along the lines of:
= <Accounting Year> = ToNumber (UserResponse ("Query 1 with beach" , "1) Enter Year:"))Andreas (BOB member since 2002-06-20)
system
November 7, 2003, 3:28pm
#8
This is the code.
=<Sum - Quantity> Where(=ToNumber(UserResponse(“Query” ,“Year To”))) -
I also tried using a variable that included ‘=ToNumber(UserResponse(“Query” ,“Year To”))’ and then in the query using it like this: =
binni (BOB member since 2003-11-07)
system
November 7, 2003, 3:29pm
#9
You have to create a helper variable (of Data Type BOOLEAN) as outlined in the BO Reporter FAQ
Try something along the lines of:
=<Sum - Quantity> Where (<helper variable> = 1)Andreas (BOB member since 2002-06-20)
system
November 7, 2003, 3:33pm
#10
Andreas:
You have to create a helper variable (of Data Type BOOLEAN) as outlined in the BO Reporter FAQ
Try something along the lines of:
=<Sum - Quantity> Where (<helper variable> = 1)
Wish I could get that far.
When I make the helper variable the same syntax error occurs when I do:
= = ToNumber(UserResponse(“Query” ,“Year From”))
binni (BOB member since 2003-11-07)
system
November 7, 2003, 3:39pm
#11
Does
ToNumber(UserResponse("Query" ,"Year From"))work as a stand alone formula/variable?
Is the universe object of data type Number/Integer or Character in the underlying DB?
And how is this object defined in BO Designer? As Character/text or Number?
Andreas (BOB member since 2002-06-20)
system
November 7, 2003, 3:42pm
#12
Yes, I’ve tried every part stand alone and this works fine.
In the underlying Informix DB it’s Integer
It’s defined as Number in Designer.
by the way…thanks for trying to help.
binni (BOB member since 2003-11-07)
system
November 7, 2003, 3:59pm
#13
If you want to e-mail me the report.
Andreas (BOB member since 2002-06-20)
system
November 7, 2003, 4:03pm
#14
Sorry, no can do. I work for the government in my country and there’s a very strick privacy policy.
binni (BOB member since 2003-11-07)
system
November 7, 2003, 4:13pm
#15
I tried doing the helper variable version again and this time it worked!!
It seems that the first time I made it Reporter perceived the return value for the helper variable to be number, but for some reason this time it perceived it as a logical (boolean) variable, which effectively lead to no Syntax Error!
Thanks a lot Andreas for leading me on the right path
binni (BOB member since 2003-11-07)
(BOB member since 2002-06-20)
