Prompts & Variables question

system · January 7, 2000, 12:23pm

Hi all,

BO5 and Oracle 8

We have a report which returns Sales Value for a customer. The user is prompted to enter the “Customer” number and the “Year” and then sales values are returned for the customer for both the selected year and year-1 (i.e. previous year).

Hence we get columns
Customer, Sales Year, Sales Month, Sales Value showing records for both 1998 and 1999.

We now want top add additional columns, Sales Value for Year 1999 and Sales Value for Year 1998 for purposes of obtaining cumulative values and so on.

In the formula for the new column I’ve tried = where (=1999) and this works fine.

But ideally I don’t want to hard-code my year here, I want to use the value the user entered at the prompt. I tried
= where (=UserResponse("Query 1 with DATestUn,“Year”)
but got a syntax error, and also tried
= where (=ToNumber(UserResponse(“Query 1 with DATestUn”,“Year”)
and still got a syntax error.

I then tried defining a Variable in my report YearSelected with a formula =ToNumber(UserResponse(“Query 1 with DATestUn”,“Year”) and then tried to use this variable in the above column formula but that also gave a syntax error.

I’ve read posting about creating a User Object on the universe to hold the value but I can’t get this working either.

Any help would be greatly appreciated.

Thanks
Darren Anderson
Client Services Manager - QI/LSR-De

Listserv Archives (BOB member since 2002-06-25)

system · January 7, 2000, 2:40pm

Hi Darren,

You wrote:

We now want top add additional columns, Sales Value for Year 1999 and Sales Value for Year 1998 for purposes of obtaining cumulative values and so on.

As always there’s more ways to achieve this, even in BO 4.

A crosstab is not enough? you need a variance or another
calculation between the two years apart from simply summing them? If not specify year to go across and you’re there, the sum can be placed in the vertical footer.
Use a separate dataprovider for each year, reuse the
prompts and the user won’t know the difference. Now you can point to the right year by specifying the dataprovider.
Create separate objects for each year, reuse the prompts
again. Oracle syntax would be something like: sum(decode(year,@prompt(“which year”…),salesvalue,0)) for this year and
sum(decode(year,@prompt(“which year”…)-1,salesvalue,0)) for last year.

Advantage of the latter is the easy reuse in other reports and usability (for a less experienced user). Disadvantage might be if you have lots of measures and have to do this for all of them.

Hope this helps,
Marianne

Listserv Archives (BOB member since 2002-06-25)

system · January 8, 2000, 2:31am

In a message dated 00-01-07 07:47:42 EST, you write:

We have a report which returns Sales Value for a customer. The user is
prompted to enter the “Customer” number and the “Year” and then sales
values
are returned for the customer for both the selected year and year-1 (i.e. previous year).

Hence we get columns
Customer, Sales Year, Sales Month, Sales Value showing records for both
1998
and 1999.

We now want top add additional columns, Sales Value for Year 1999 and Sales Value for Year 1998 for purposes of obtaining cumulative values and so on.

In the formula for the new column I’ve tried = where (=1999) and this works fine.

But ideally I don’t want to hard-code my year here, I want to use the value the user entered at the prompt. I tried
= where (=UserResponse("Query 1 with DATestUn,“Year”)
but got a syntax error, and also tried

[[ snip ]]

I’ve answered this question before, but apparently the class wasn’t paying attention as nobody has volunteered my answer for you. So I’ll provide it once again.

The specifics are different, as I originally wrote this answer for another “lister”, but you should be able to get the basics of the solution.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

The problem is that you can’t use:

=

in a

Sum() Where…

expression. You have to use a constant value. So, what you have to do is create a constant value using another variable first.

Create a variable called (or whatever you would like to call it) with the following expression:

= If (= And = ) Then 1 Else 0

Next, create a variable using Sum() Where… like the following:

= Where ( = 1)

You can “fake” BusObj by creating an expression for Variable = Constant, it’s just that the Variable and the Constant are defined using another variable…

I tested this in version 4.1 and in 5.0, and it works in both places. Even if it didn’t, here’s another solution:

= *

Think about it… is either 1 (one) or 0 (zero). If you take and multiply by 1, you get the same value back. If you multiple by 0, you get 0 back. So, if the = 1 then you get a sum of Last Year’s Units. If = 0, then you get zero. Add the entire column and what do you get? The correct answer…

The only difference is that with the Sum(x) Where… result you will have an empty cell, rather than a zero. But either trick should get you the results that you wanted!

Listserv Archives (BOB member since 2002-06-25)