Hi,
I am having 3 fields : customer and order-price and order date.
I want to generate a report which will ask me for 4 dates ex : 1/3/04 & 5/3/04 and 8/3/04 & 12/3/04.
I want to display a report having 3 columns:
Customer name
sum(order-price) for orders having order-date between 1/3/04 and
5/3/04
sum(order-price) for orders having order-date between 8/3/04 and
12/3/04
Since we cannot have the object order-price twice in the result-objects section of the query panel I created a user object called user-order-price.
Now I can display the 3 columns.
But how do I apply the condition for the individual columns.
i.e. - column2(order-price) should display sum(order-price) for orders
having order-date between 1/3/04 and 5/3/04
and
column3(user-order-price) should display sum(order-price) for
orders having order-date between 1/3/04 and 5/3/04
How do I apply these individual conditions?
Please reply soon.
Thanks in Advance.
I would not create a UDO.
Instead I would create my condition in the data provider as:
Date Between Prompt1 and Prompt2
OR
Date Betweeen Prompt3 and Prompt4
At the report level I would create local report variables using UserResponse to capture the prompt, convert them into data type Dates using ToDate, and then create new measures using the WHERE clause.
For example (pseudocode):
= <Price> WHERE (Date = Prompt1)
For more details on how to use WHERE clauses for measures within Business Objects Reporter (pitfalls and work arounds) see this entry in the Reporter FAQ.
Create two separate data providers for order price, one with the first prompt and the other with the second , and then link them thru’ customer name thru’ the data manager
Another approach with one DP is to create the condition as told by Andreas and now instead of a report level manipulation have two Measures defined at the Universe Level and use a CASE END to check for the Dates and Sum the Order Price… Drag those two Measure and the condition and you should get the required result.