Months in Y-axis chart

[tarikqazza]

Hello All,

My problem is concerning a chart in reporting services.

My dataset is looking as below:

MonthDue MonthDeleviry
------------|----------------
Jan-2011 Mars-2011

Feb-2012 Jun_2013

Aug-2016 Oct-014

I want to make a graphique
MonthDue(Y-axis) /MonthDeleviry(X-axis) the problem is that Y-axis doesen't show label of month and convert it to FirstDayofMonth/MM/YYYY. It seems that this axis dont accepte String value and allow just Numeric and date Format dd/mm/YYYY.

Excuse my bad english

Can you help please.

Thanks

 

[xlbo]

is there an issue with making your dates "real" dates?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[tarikqazza]

Thanks for reply.
I don't have date with format dd/mm/yyyy but months (NameMonth-Year) and my client want to see them like this.
do you mean by "Real" that months are like this : 012012 equivalent to jan_2012 ?

The problem of reporting services chart is that he didn't accept string format, just numéric or date (dd/mm/yyyy) i think may be that's limitation of the composant

 

[xlbo]

What I mean is are the dates stored as proper dates in the database? You can make them look however yo want with formatting but it is almost always best to return them in a query as real dates as then charts know how to interpret them

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[tarikqazza]

the problem in your idea is that the axis will show intermediate real values for example between 200612 and 200701,
[200613,200614....200699,200700], for example wich are not a months ( months 13,14,...99 that's not exist month must be between 1 to 12).
The question is why Y-axis don't allow to see qualitatives value?? (string values i mean) ??

I think it's limitation of product

 

[xlbo]

tbh i think it looks like an issue with your data - if you have 14, 14 & 99 in something that is meant to be a date field

Conversions to "1st of month" can be done in a query to remove "intermediate" values. Charts by their nature tend not to display string vs string information unless sing a scatter plot - it is much more common to use string/numeric or date

the question you need to ask is why is my data set up in such a way that it does nto translate easily into a chart?

Can you show me how your data is actually stored in the database? I can then sugget how to quiery it in such a way that the data will chart as you want it



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[tarikqazza]

Thanks for reply. The pic bellow explain what is my target-chart and my dataset

http://img15.hostingpics.net/pics/219062shemaGraphe.jpg

hope that is more clear now for you

 

[xlbo]

I can see that is your data set but how is it stored in your dataBASE?

are your "dates" really stored as "June-2014" etc?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[tarikqazza]

Ok no problem suppose that i have day granularity in my dataset(in europe format or us no matter) :
date_delevery| dateDue
01/11/1977 | 02/03/2015
21/03/2005 | 02/12/2013
01/11/1977 | 13/05/2015
........
....
...
..

for example (i can extract month name with function : DATENAME(mm,[mydate])+'-' + DATENAME(yyyy,[mydate]) to have months like Jan-2005.

can you show me the result with months in Y axis as i post-it before (in string format not in number format "numMonth-Year") on reporting services's graphe.

 

[xlbo]

all I am saying is that you should keep your dates as real dates rather than convert them in the SQL

If you want to see everything that happened in a month bundled together no matter if it happened on 1st or last of the month then convert all your dates using:

SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,dateDue))-1),DATEADD(mm,0,dateDue))

SELECT DATEADD(dd,-(DAY(DATEADD(mm,1,dateDelivery))-1),DATEADD(mm,0,dateDelivery))

you should then simply be able to put the 2 fields into your chart and format them to look however you want

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[tarikqazza]

Ok i try it and still have the same problem. Y-axis dont accept string format.
In your sollution the first of month still appear my client want just to see months in the Y-axis like Jan-2008 ( string format not real !!!)

I invite you to do the same with sample data. Can you make a pic-capture to show me how you did it plz?

click to this link to see my target :

http://img15.hostingpics.net/pics/219062shemaGraphe.jpg

 

[xlbo]

You are not getting my point. The date is returned as a true date so it can be plotted on the chart. You then FORMAT the date to appear on the axis as you (or your client) desires. I am afraid I am not going to do your job for you - I have explained the procedure. If you have a specific issue in trying to implement my suggestion, please post back with specifics - ie your query, the results and how you are trying to set the chart up

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[tarikqazza]

Xlbo thanks for reply.
First i don't want you to do the job for me. I explained that i tryed your solution and it dosen't work.Second i think that a picture worth more than 1000s word so i propose you to test yourself : Theory is not like practice.My example is very easy to implement and you can do it in 2 minutes juste table with 2 dates .No matter seems that you are very busy.

Well i make a second pic for you

http://img4.hostingpics.net/pics/335149DateImage.jpg

to see the result with your proposition. As you can see the graph can't show dates. how about months ??

Now when i use my formula in reporting service to convert date as follow :
=Monthname(month(Fields!dateLivraison.Value)) + "-" + Year(Fields!dateLivraison.Value)

http://img4.hostingpics.net/pics/126635DateImage.jpg

Thanks for you patience & help