to retrieve the oldest date in the report

[Former Member]

I have a requirement to retrive the oldest date in the report, I am currently working in BW 3.5

The scenario is as below

I have a char infoObject creation date which retrieves the date

I have a char infoobject status which retrieves open or close

For eg in my cube I have 5 records

3 records has the status open and 2 records has the status closed

When I adding the InfoObject "creation date" which has filtered for the open status , I am getting the result correctly with 3 records

Now my requirement is I need to have only one row which should get oldest "creation date" out of the 3 records as below

Rec1 10.10.2006

Rec2 25.11.2007

Rec3 20.08.2009

I need only one count in the creation date which should show me 10.10.2006

[Former Member]

Hi Pavan.

Can you model the creation date as a key figure as well as having it as a char? If so, you can create a condition on the key figure, to only give you the "Bottom 1" record.

I think in 3.5 you cannot use a formula variable to read the value of the char in the row, but if you can, you can do it like that with a calculated key figure where you use the formula var and then put a condition on this ckf.

regards

Jacob

[Former Member]

Try creating a formula, a replacement path variable with 'creation_date', Currency/Unit as Date,exception aggregation First value based on a suitable char. Else, need to write a routine at provider level to fetch only the oldest record.