Hi,
I have a crosstab which displays dates instead of numbers using a solution I found here: http://www.ibm.com/developerworks/data/library/cognos/page482.html?ca=drs- (http://www.ibm.com/developerworks/data/library/cognos/page482.html?ca=drs-)
It works perfectly but a requirement from our Bourd is that future dates be formatted differently to those in the past. I am struggling here as i need to embed today's date in the crosstab fact cells somehow so that I can use it in a variable.
can anyone help?
Stuart
Test:
-------
Can you create a data item [Current Date]:
cast_integer(cast(extract(Year,current_date),char(4)))*10000+
cast_integer(cast(extract(Month,current_date),char(2)))*100+
cast_integer(cast(extract(Day,current_date),char(2)))
Boolean Variable:
[Date]>[Current Date]
If Yes, then Back Ground Color Yellow.
Just see if it works?
PRIT,
I tried this before using a different cast statement (but essentially doing the same thing and got the following error:
"An error ocurred at or near the position '27'. The variable named '[Query1].[Current Date]' is invalid."
I have also tried to use your cast statement and the same error appeared.
I wondered if I had to place the [Current Date] Data Item into the crosstab and hide it somehow but I couldn't get this to work either.
Any more clues?
Stu
I see. Usually when you use a new item in your existing report and not using in the report layout, cognos doesn't recognize the new item.
All you have to do is, Select the 'Page' .
In Properties Pane Select the Query where you have created the [Current Period] Data Item.
You can see another option called Properties. Click the 'elipse' and check [Current Period]
It should work now.
Thanks
Prit
you are a genius.
A long way to go though ;) THANKS :)
I have a new issue with the above report...
Formerly, we just wanted to conditionally format off Today's date. this works as shown above.
Now, they want an extra condition adding in that when a certain field > 0 for that record, the date is blue. as this is an outstanding payment.
So. We have the appointment date = 20110305 (this shows as "05/03/2011" in the crosstab)
We have today's date = 20110310 (using the Boolean variable, if this date is lower than the above date, the diplay is red)
We now have a numeric field ([paid]) which; when it is greater than 0 we want the Appointment Date to appear blue.
My method was to add the numeric value [Paid] to the [Appointment Date] this would mean that the [Paid]+[Date] value would be 20110306 (if [paid] = 1).
So in the above case we have:
[Date] = 20110305
[Today] = 20110310
[PD] = 20110306 ([Paid]+[Date])
I then used a String Variable:
Case when [Date]>[Today] then 'future' when [PD]>[Date] then 'Paid' else 'default' end
Then I set the variables to the colours I wanted:
future = red
paid = blue
Other = (Default)
I also added [paid] and [PD] to the Properties list.
now, it formats the future ones correctly, but the all other dates appear blue even though some have [paid] = 0 which should be black.
I tried changing the aggregation of the [paid] data item to None and None and then the future ones stayed correct and the past ones all went black.
I can't get all three colours to work and it's killing me!!
Please help.
Stuart
Quotenow, it formats the future ones correctly, but the all other dates appear blue even though some have [paid] = 0 which should be black.
Off course it would show 'Blue', because your condition is:
QuoteCase when [Date]>[Today] then 'future' when [PD]>[Date] then 'Paid' else 'default' end
And as per your logic, [PD]=
QuoteMy method was to add the numeric value [Paid] to the [Appointment Date] this would mean that the [Paid]+[Date] value would be 20110306 (if [paid] = 1)
Are you using the [PD] time in your report?
Hi PRIT,
No [PD] does not show in the report. I have added it to the proerties list for the page but I do not want it to display on the report.
Also, when I run tabular data on the query, I get 'future', 'Paid' and 'Default' results so I am quite confident that, in a list report, the case statement would be fine but I am missing something when I try and do it in this crosstab.
Agreed, for the situation I gave, I would want that date to be blue, which it is, but I can't get the unpaid ones to be black (Where [PD] <= [Date]). That is my problem.
Stuart
Since i am held up with some production issues, can't do a test now.
However, just a quick guess. Are you using [PAID] item in your report? If not,
For the [Paid] item, try changing the expression as
Total([Paid] for [Date]) and change the aggregate function to 'Automatic'
Because, seems the measure is not grouped by the DATE?
And see if it helps? If not, will do a small test once I get some free time.
Thanks
Prit
It's worked! I did think it was an issue with aggregation but I couldn't work out how to effectively work [Date] into the aggregation method - I should've tried that.
Thanks very much again. I hope I don't have any more brain death but if I do, I know where to come!
You're a star.
Cheers,
Stu
Good to hear that my guess was correct :D
Thanks Stu