an alternate expression

building reports desktopintelligence
1

Would appreciate any ideas on this.
BO 4.1.5.

I have 2 columns in my report.
First column has this formula : ( revenue earned in 1998 for a region(s) ) =<Total Sales Revenue - YTD> Where (=“1998”)

2nd column has :
=<Total Sales Revenue - YTD> Where (=“1999”)

is a variable that was created to have : =FormatNumber(Year(),"####")

I want to remove the hard-coded values ‘1998’ and ‘1999’ and use a variable instead.

OR
Is there any other way I can rewrite the expression “=<Total Sales Revenue - YTD> Where (=“1998”)” and achieve the same results ( ofcourse without using that hard-coded year ) ?

BO syntax does not accept a variable in its WHERE clause after the “equal to”.

Thanks & Regards,

Unni

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

2

Hi Unni,

I’m not sure, but I think you need to build a cross table report, where your Year() variable will be your heading column.

Hamid

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

3

In a message dated 00-01-19 12:00:33 EST, you write:

Is there any other way I can rewrite the expression
“=<Total Sales Revenue - YTD> Where (=“1998”)” and achieve the same results ( ofcourse without using that hard-coded year ) ?

BO syntax does not accept a variable in its WHERE clause after the “equal to”.

Create variables as follows:

Variable: First Year
Formula: = UserResponse(,“Enter First Year”)

Variable: Second Year
Formula: = UserResponse(,“Enter Second Year”)

Variable: First Year Flag
Formula: =If = Then 1 Else 0

Variable: Second Year Flag
Formula: =If = Then 1 Else 0

Variable: First Year Sales
Formula: =Sum() Where ( = 1)

Variable: Second Year Sales
Formula: =Sum() Where ( = 1)

I am assuming you might want to prompt for the two years. If not, adjust accordingly.

By moving the check for “1998” (first year) or “1999” (second year) into another variable, you can test a constant value of 1 or 0 in the Sum()… Where() clause.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

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

4

You can build 2 queries, one for each year, using the following conditions :

Query 1:
TIME.YEAR = @variable(‘Year ?’)

Query 2:
TIME.YEAR = to_char(to_number(@variable(‘Year ?’)-1))

Refresh your report and fill ‘1999’ in the MessageBox, then display in your 2 columns ‘Revenue from Query 1’ (ie 1998 revenue) and ‘Revenue from Query 2’ (ie 1999 revenue).

Hope this helps,
Francois

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

5

Thankyou Dave, that was a good one.

Unni

In a message dated 00-01-19 12:00:33 EST, you write:

Is there any other way I can rewrite the expression
“=<Total Sales Revenue - YTD> Where (=“1998”)” and achieve the same results ( ofcourse without using
that hard-coded
year ) ?

BO syntax does not accept a variable in its WHERE clause after the “equal to”.

Create variables as follows:

Variable: First Year
Formula: = UserResponse(,“Enter First Year”)

Variable: Second Year
Formula: = UserResponse(,“Enter Second Year”)

Variable: First Year Flag
Formula: =If = Then 1 Else 0

Variable: Second Year Flag
Formula: =If = Then 1 Else 0

Variable: First Year Sales
Formula: =Sum() Where ( = 1)

Variable: Second Year Sales
Formula: =Sum() Where ( = 1)

I am assuming you might want to prompt for the two years. If not, adjust
accordingly.

By moving the check for “1998” (first year) or “1999” (second year) into
another variable, you can test a constant value of 1 or 0 in the Sum()…
Where() clause.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

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

6

Jan 19, 100 10:48 am

Unni,

You can have two data providers, first one pulling data for 1998 and next one pulling 1999 data. then link them on common dimensions.

hope it helps. let me know if u need further …

vasan

Would appreciate any ideas on this.
BO 4.1.5.

I have 2 columns in my report.
First column has this formula : ( revenue earned in 1998 for a region(s) ) =<Total Sales Revenue - YTD> Where (=“1998”)

2nd column has :
=<Total Sales Revenue - YTD> Where (=“1999”)

is a variable that was created to have : =FormatNumber(Year(),“####”)

I want to remove the hard-coded values ‘1998’ and ‘1999’ and use a variable instead.

OR
Is there any other way I can rewrite the expression “=<Total Sales Revenue - YTD> Where (=“1998”)” and achieve the same results ( ofcourse without using that hard-coded year ) ?

BO syntax does not accept a variable in its WHERE clause after the “equal to”.

Thanks & Regards,

Unni

OR search: Mail to listserv@listserv.aol.com, ‘search a_phrase in BUSOB-L’ Unsubscribe: Mail to listserv@listserv.aol.com, ‘unsubscribe BUSOB-L’

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

7

Thankyou Vasan.
Mr. Dave Rathbun’s solution exactly fits the bill ( as appended at the end ).

Regards,

Unni

Unni,

You can have two data providers, first one pulling data for 1998 and next one pulling 1999 data. then link them on common dimensions.

hope it helps. let me know if u need further …

vasan

Would appreciate any ideas on this.
BO 4.1.5.

I have 2 columns in my report.
First column has this formula : ( revenue earned in 1998
for a region(s) )
=<Total Sales Revenue - YTD> Where (=“1998”)

2nd column has :
=<Total Sales Revenue - YTD> Where (=“1999”)

is a variable that was created to have : =FormatNumber(Year(),“####”)

I want to remove the hard-coded values ‘1998’ and ‘1999’ and use a variable instead.

OR
Is there any other way I can rewrite the expression “=<Total Sales Revenue - YTD> Where (=“1998”)” and achieve the same results ( ofcourse without using that
hard-coded
year ) ?

BO syntax does not accept a variable in its WHERE clause after the “equal to”.

Thanks & Regards,

Unni

In a message dated 00-01-19 12:00:33 EST, you write:

Is there any other way I can rewrite the expression
“=<Total Sales Revenue - YTD> Where (=“1998”)” and achieve the same results ( ofcourse without using
that hard-coded
year ) ?

BO syntax does not accept a variable in its WHERE clause after the “equal to”.

Create variables as follows:

Variable: First Year
Formula: = UserResponse(,“Enter First Year”)

Variable: Second Year
Formula: = UserResponse(,“Enter Second Year”)

Variable: First Year Flag
Formula: =If = Then 1 Else 0

Variable: Second Year Flag
Formula: =If = Then 1 Else 0

Variable: First Year Sales
Formula: =Sum() Where ( = 1)

Variable: Second Year Sales
Formula: =Sum() Where ( = 1)

I am assuming you might want to prompt for the two years. If not, adjust
accordingly.

By moving the check for “1998” (first year) or “1999” (second year) into
another variable, you can test a constant value of 1 or 0 in the Sum()…
Where() clause.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

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