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
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]
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.
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.