If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Chart Date differentiation between x-axis and on mouse over

Started by aydakut, 10 Feb 2011 01:56:40 AM

Previous topic - Next topic

aydakut

Hi,
I have a chart that has dates as mon. yy (converted from long date format to char) on the x-axis. What I need is to show the dates as dd.mon.yyyy without conversion when on mouse over on the graph.

Is this possible in the Report Studio?
Thanks in advance.

PRIT AMRIT

Is there any specific reason why u converted as 'mon.yy' format?

aydakut

 :) Yes there is. This is actually a daily report but the trend should be followed monthly. And I want to show the data points' real dates.
Let me give you detail:
The user chooses date from combo  (ex: 27.Dec.2010) the chart is drawn from 27.Dec.2010 to 27.Dec.2009 backwards shown Dec.10, Nov.10, Oct.10, and so on.

PRIT AMRIT

But if your trend is showing Monthly in X-axis, then which date you would consider from your real dates, e.g.

For Dec: Your real dates are
Dec-01-2009
Dec-02-2009
Dec-03-2009
.
.
Dec-31-2009

Now when you do cast in MM-YYYY format, it accumulates entire DEC month dates and brings u the value for DEC-2009.

In your case, DEC-2009 shows in X-axis. And the tool tip is showing the same.
Now how you consider the Real Date for DEC month? Because DEC consists of all the dates from 1st to 31st.



aydakut

Thanks PRIT, I' ve noticed sthg that I did wrong. I used add_months(?parameter?,-12) function so I always get the last day of the months. Since I used converted date I didn't realise the fact.

Therefore my question is now, how will I show the date chosen backwards.
For instance Dec.31.2010 is chosen as the initial date, the graph's date should be dec.31.2010,nov.30.2010,oct.31.2010,.....feb.28.2010,...dec.31.2009.

Choice of Date is random.
Add_months doesn' t work because it goes 30 days back.

I hope you help.

PRIT AMRIT

OK. So it will always show for 1 year?
Say
if Dec.31.2010 then it should display Dec.31.2010 till Dec.31.2009
if Nov.31.2010 then it should display Nov.31.2010 till Nov.31.2009

if Dec.31.2009 then it should display Dec.31.2010 till Dec.31.2008
if Nov.31.2009 then it should display Nov.31.2010 till Nov.31.2008

PRIT AMRIT

What does your date field returns? Does it contain the dates like below format

Dec.31.2010
Nov.30.2010
Oct.31.2010
Sept.30.2010
Aug.31.2010
:
:
:
Jan-31-2010

If Yes, then you can just add a filter like below and the sort Desc?

[Date] <= ?p_date? and
[Date] >=_add_years(?p_date?,-1)

Does it make a sense?

Thanks

aydakut

It makes sense a lot. Thank you.
I used 2 filters:
1) [Date] between to_date(TO_CHAR(?date?;'DD.MM')+'.'+
TO_CHAR(to_char(?date?;'YYYY')-1);'DD.MM.YYYY') and ?date?
2) to_char([Date];'dd') = (
if    ( to_char(?date?;'dd') <= 28 )
then   ( to_char(?date?;'dd') )
else if   ( to_char(?date?;'dd') = 31 )
then   ( to_char(_last_of_month([Date]);'dd') )
else if    ( (to_char(?date?;'dd') in (29;30)) and (to_char([Date] ;'mm') = 2) )
then   ( to_char(_last_of_month([Date]);'dd') )
else    ( to_char(?date?;'dd') )
           )