I’m new to BO/WebIntel. I’m trying to create a function where I retreive a value, say sales revenue, from a column based on a date input parameter. Here’s an example of what I’m trying to do:
in eFasion create report with Month and Sales Revenue fields. Have filters/conditions set on Month Greater then or equal to (‘BeginMonth’) and Month Less than or equal to (‘EndMonth’). If you run this report and specify month 1 as the begining month and month 6 as the endnig month you get 6 rows of two columns. I’d like to create a new cell the gets filled with just the sales revenue for the begining month, whatever the user may have chosen. This is where I get stuck. I’ve tried creating a BEGMONTH variable and having the formula for the standalone cell be something like:
= Where (=) but is doesn’t like this. I tried it with “” around the variable also. I also tried using the formula for the variable directly in the formula for the cell:
= Where (=UserResponse(DataProvider() ,“BeginMonth”))
but it doesn’t like this either. This is all in the full client. As you can see, I’m using the Where clause. How can I get this to work in WebIntelligence also which ( v 6.1a ) lacks the Where operator?
The “Where” operator does not allow you to use variables… the syntax must be
Sum(<X>) Where (<Obj> = Constant)
That being said, there is a standard work-around for full client reports that is documented in this Reporter FAQ Entry.
At this time there is no “Where” for Webi. You can, however, make objects in your universe that include prompts, and then using Decode() or Case or other database techniques accomplish the same thing.
I can help with the Reporter solution. The piece you are missing is that the Where clause ONLY accepts constants as the operand. You can’t use a formula of any kind. The workaround is to create a separate report variable (let’s call it ) with a formula like this: = If = Then “Y” Else “N”. Now the other formula would look like this: = Where ( = “Y”).
Dave, thanks for the reply. What about functions within functions such as:
=Sales Revenue Where ( = Min())
The idea I have is to get a value from 1 column that corresponds to a prompted value in a different column for the same row. Since the input param is a date, the min and max of the date values serve as the identifier for the row in question, make sense?
I saw the FAQ entry before and I don’t think that will work in my case as a user will pick a date from a list. Can you expand on the Decode and Case methods, I don’t have much documentation besides a really basic tutorial and online help isn’t that great.
Nope, can’t use functions either. As the FAQ (and Dwayne) note, you have to create a separate variable that uses any functions or other processing. That variable needs to generate a finite set of responses (yes or no, true or false, 1 or 0, and so on). Then you use that in your “where” flag.
Since you’re wanting to see a total revenue for the “first month” and you are prompting for the “first month” then you need something like this… create a variable called that has this formula:
UserResponse ALWAYS returns a as a string. Don’t let the fact that the @prompt syntax may specify number or date mislead you. That setting helps the SQL get written correctly, but UserResponse still returns the choice as a string. Try:
=If <Month> = (ToNumber(UserResponse(DataProvider(<Month>) ,"BeginMonth")) Then 1 Else 0
Oh, duh, I forgot your example was from eFashion. Sorry.
I built a query with Month and Sales revenue. There was a prompt for Month Between “Begin Month” and “End Month”. To obtain the user’s selection for the first month I used this:
I called that variable Selected Begin Month. Now, to show the revenue only for that month I created a second variable called Begin Month Flag as follows:
=if <Month> = ToNumber(<Selected Begin Month>) Then 1 Else 0
Finally, to get the total revenue for the “begin” month I created a variable with this formula:
=Sum(<Sales revenue>) Where (<Begin Month Flag>=1)
Thanks, that’s great. You had mentioned the decode and case methods, can you give me a alittle to go on with using them to work around the lasck of ‘Where’ in Webi? Thanks again for your help.
case when table.month = @prompt('Begin Month',,,,)
then table.revenue
else 0
end
Wrap a sum() around all of that and you have a Beginning Month revenue. That’s a bit simplistic, as you would want to check the year as well as the month.
Are you going to the conference this year? (See current banner as of Oct 18 for more details.) If so, I’m showing some other work-arounds for the lack of the Where in Webi in a presentation that I am doing this year. If you’re not going to the conference, you will be able to download all of the Integra presentations (4 this year) from our website after the conference is over.
Nope. Sorry for the confusion. This would have to be done by the universe designer, using the “Designer” application. The result would be an object that you select for your query.
(BOB member since 2002-06-06)