BusinessObjects Board

Year and Quarter

building reports desktopintelligence
#1

I have a interesting question.
I have a dimension variables “Year” and “Quarter” and a measure variable “Count”.
Year has value say 2003, 2004, 2005, 2006, 2007…
Quarter has 4 values -> 1, 2 ,3 , 4

Here will be the table like:

Year     Quarter       Count
-----     ---------       -------

2004        1                30
2004        2                11
2004        3                 2
2004        4                  38

2005        1                   22
2005         2                 59
2005        3                  42
2005         4                 51

2006        1                   22
2006         2                 59
2006        3                  42
2006         4                 51 .......

Lets say the table has only these data uptil 2006.
So I want the total data and the data from the last quarter (which is Y2006 Q4). I want to do it dynamically by creating a variable or some other way.

I created a variable(measure variable):
“Current Year” = Max(). I get 2006 here.

Now I am trying to create variable “Current Quarter” which will be like:
=Max() Where ( = Current Year)

But I am getting the incorrect data type syntax error.
If I write this, it works out fine:

=Max() Where ( = 2006). I get 4 here. But this method is not dynamic since current year can change

I could not find the problem between the two.
Can you tell how can I implement the design?

Thanks.

[Moderator Edit: Added code formatting - Dave Rathbun]

ujain82 (BOB member since 2007-06-19)

#2

Hi, please see this FAQ entry. Thanks.

Dave Rathbun :us: (BOB member since 2002-06-06)

#3

Its not solving my problem . It is addressing different problem.

WHERE Clause in Business Objects Reporter only allows the “=” operator, and the right side of the equation has to be a constant as well (and not another report variable etc.).

But in my case right side needs to be a variable for my reports to work dynamic.
How can I do it? Can you read the problem description again.

Thanks.

ujain82 (BOB member since 2007-06-19)

#4

It cannot be dynamic, that is the way it works…

ahm100 (BOB member since 2005-05-25)

#5

But there should be a work around for the problem.

ujain82 (BOB member since 2007-06-19)

#6

Please reread the FAQ. It shows you how to solve the problem.

Dave Rathbun :us: (BOB member since 2002-06-06)

#7

I am not getting the solution from it.

It says: Sum() Where ( = 2000)
I need the same thing but instead of 2000 (constant) I want a variable which is not possible with Where clause.

This is my problem.
The FAQ is trying to solve Sum() Where ( > 2000) this problem which I don’t want.

Thanks.

ujain82 (BOB member since 2007-06-19)

#8

The FAQ shows you how to use a variable on the right side of the expression. The example is simply that, an example.

Create a variable for Current Year.
Create a variable Current Year Flag

=If (<Year> = <Current Year>) Then 1 else 0

The final expression:

=Sum(<Variable>) Where (<Current Year Flag> = 1)

It’s all there in the FAQ; you just have to be able to extend the example to fit your situation.

Dave Rathbun :us: (BOB member since 2002-06-06)

#9

I created a variable(measure):
“Current Year” = Max().

Now I could not create a Current Year Flag variable(measure).
If I do this:
= If(=) Then 1 Else 0 . It gives the computation error.

ujain82 (BOB member since 2007-06-19)

#10

You need some way to determine the year without using an aggregate function. The requirement is that the value used in the WHERE clause has to be a dimension. When you use an aggregate, the value becomes a measure, and therefore is not valid.

For example, you can use a prompt and let the user tell you what the last year is.

But you cannot use any aggregate functions in this process.

You can potentially create a second data provider that brings back the maximum year only, using database functions. Then you can link the data providers, and generate the flag that way.

Dave Rathbun :us: (BOB member since 2002-06-06)

#11

Hi Ujain82,

Were you able to fix this issue ?? I am having a similar issue here.

Thanks,
Neha

neha008 (BOB member since 2007-07-09)

#12

I was able to resolve it.

Thanks

neha008 (BOB member since 2007-07-09)