COGNOiSe.com - The IBM Cognos Community

How to get updated date & that row values to list
Hi,
am using cognos 8.4 & DB is SQL Server
In my report I have country,Budget(Aggregation-Total),Date in list
To get last updated Date am using Maximum(Date for Budget).

Then it showing last updated as
Country    date                            Budget
India        Jan 27,2012             133,450,588.24(Total India Budget upto till date)
Japan   Jan 26,2012       72,645,473.15

Here am getting last updated date as correct but budget values are showing Total Budget for each country
I want to show only updated budget values for updated Dates.I changed Budget Aggregation & roll up Aggregation properties. But I didn't get.
Please let me know how can I achieve this?

Create a data item that only returns buget when the date is equal to the max date for the country.

Case when date = max(date for country) then budget else 0 end

Make sure that data item is set to total aggregation.

Thanks for your Reply Johnson.
Its working fine. Now am getting data like
Country     date                            Budget
India         Jan 31,2012                20.79
Japan        Jan 30,2012                97.32
Other        May 15,2007                 0.00
In the same report my client needs previous 3 days date & budget for country like
Country     date                            Budget
India         Jan 30,2012                20.16
                 Jan 31,2012                20.79
Japan        Jan 28,2012                95.66
                 Jan 30,2012                97.32
Other        May 15,2007                 0.00
So,I have to get Today's,Yesterday's & Previous Date & budget
Could you please suggest me how can I achieve this?

Thanks in Advance,
Regards,
Swetha.

Use a different approach, take the case statement out and go back to your regular budget, take the aggregation off of the date field (set it to none).  This will have a report that lists all dates and the budget for that date.  Then limit the dates being returned in the report using a filter.

For the filter, try using: [Your Date Field] between _add_days (current_date,-2) and current_date

Hi Johnson,

I applied the filter but it showing last 3 days updated values. And also sometimes its not working exactly.
If Budget is updated on last month for a country,then it not showing that country row.
If budget is not updated from 3 days(or 3 months or Year) then it have to show last updated date,budget for that country.
How can I solve this Issue? please let me know.

Thanks....

In the filter, replace the current_date with max([Your Date Field] for [Country])

[Your Date Field] between _add_days (max([Your Date Field] for [Country]),-2) and current_date

That should filter to individual rows so that each one returns only if it is within 3 days of the maximum date for that country.

Hi Johnson,
your solution is best one to me. Its working exactly what I needed. Thank you
Now my report  in list is
DATAITEM1    -     COUNTRY
DATAITEM2    -     DATE
DATAITEM3    -     REVENUE(TOTAL-AGGREGATION)
FILTER           -     DATE BETWEEN _ADD_DAYS(MAXIMUM(DATE FOR COUNTRY),-2) AND CURRENT_DATE
MY REPORT OUTPUT
COUNTRY                               DATE                                 BUDGET
AAAAA                          8/2/12(RED COLOR)                       100
                                    7/2/12(WHITE COLOR)                    75
                                    6/2/12(GREEN COLOR)                    60
BBBBB                          7/2/12(WHITE COLOR)                    80
CCCCC                         26/3/07(GREEN COLOR)                  50
   
But am unable to set the conditional styles for the dates. May I know how to set the colors for different dates (Budget values also)?

Thanks in Advance,