Function Needed to Show Discrete Months on Chart Labels

I posted a question earlier about setting chart intervals and received some valuable help.  The principal outcome however was that I'd asked the wrong question for what I wanted to achieve.

The results I'm getting
The results I want
The data I'm using is below, the only difference being that I'm calculating DailyMax in Report Builder 3.0 rather than in SQL Server.

 My data
The report shows maximum daily disk usage from a date in April this year until the day before the report was run.  This maximum use is then plotted on the graphs next to the server and disk details so any sudden changes are easily visible.  This data could potentially cover a very long time period so I could end up with a lot of month labels along the X-axis.  
What I'd like is a function to set the X-Axis interval to show only the discrete month and year rather than what appears in the top diagram.  Alternatively if somebody could just tell me what number to set the interval to in order to achieve this if it's something that simple.

Current X-axis settings
I'm very new to Report Builder and my more experienced colleagues haven't come across this before either.
Who is Participating?

Improve company productivity with a Business Account.Sign Up

PaulConnect With a Mentor Commented:
Just wondered if there was anything here that would help out:

looks quite relevant
ValentinoVBI ConsultantCommented:
What happens if you set Interval to 1?
BWFCAuthor Commented:
It just shows May 2013.  I should have said that I've tried changing the interval to everything between 1 and 4 and also to 0.25, 0.375, 0.5 and up to 1.
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

could you also post the Major & Minor Tick mark screens?
these properties normally work together.

Is there any chance of you posting some data in text format? (csv xslx)
BWFCAuthor Commented:
Tick mark settings
As requested
as presented in that .csv there is a column of zeros (column C [Full Date]) which isn't what you displayed in the image of the data.
BWFCAuthor Commented:
Aargh, stupid formatting! When I saved the file I got a message warning that 'some formats may not be saved' or some such.  I didn't realise it was the date format, sorry about that...Try this one.
Sorry to put you to extra trouble on that file - all looks fine with it now. I thought that if I messed with it in Excel it might give me some clues. Feel a bit guilty now.

Perhaps this is a dumb question, but there are ~125 servers shown in that file and on average each has 3 drives. When you actually present this data in the chart are you filtering by server only? (if that's true then I think you might be getting "repeats" of the X-axis labels because of the drives.) If this suspicion is true, then you may not be able to suppress every second label as you indicate as the desired result, because it is really a change of drive that is causing a new label to be output.

On the Major Ticks Marks screen, lowest section "Set Custom Interval", have you tried
Interval = 4  &  Interval Type = Weeks ??

just in case:
what are the available Intervals? and Interval Types?
BWFCAuthor Commented:
This is how the data looks on the full report.  I've grouped it by server and then put in a child group to organise it by drive.

How it looks
:) looks better already, now we can see "the bigger picture" (&answers my dumb question)

so; you want less labels than that? (yet another dumb question I guess, sorry)
will you hate me?....

have you considered presenting  % of space instead of absolute figures (no need to answer)
BWFCAuthor Commented:
Technically I do want fewer labels than the one shown there.  When I ran the report this morning I'd forgotten to change the interval back to auto hence only showing the label for May!
As it stands it actually looks like this...
How it is
This is possibly my turn to ask a dumb question; how would showing percentages make a differance?  As far as I can see I'd still have a list of daily maxima to plot on the graph.  The other factor is the customer wants absolutes and what the customer wants, the customer gets.
sorry % display is a distraction - just a suggestion - I'll expand on this later perhaps
- it won't make the slightest difference to labels

OK, now I see the real thing it seems to me that "auto" is actually producing "weeks" not months. I want to make a few suggestions, and there may be some trial & error here
(forgive me here as I cannot mimic what you have)

in the dialog for Label could you change it so:
a. the year is NOT displayed (I think this would make sense anyway)
b. the DAY is displayed (this will help identify what the damn thing is really doing)
c. step the font down 2 or 3 points
d. change font color to a darkish gray (i.e. less than black)
- stepping down font and lowering the contrast will make the labels seem less intrusive

On the Major Ticks Marks screen, lowest section "Set Custom Interval", please try
Interval = 4  &  Interval Type = Weeks
Interval = 2  &  Interval Type = Weeks

does any of this make progress?
Nico BontenbalCommented:
I don't think what you want is possible. Only for a bar chart the labels will be in the middle of the data. The best I can come up with is a chart with a label per month, but at the start of the month not the middle. See the attached example.
BWFCAuthor Commented:
Hi Paul, I've tried changing the settings to what you suggested and it produces a chart that looks exactly the same as the one you made.

Nicobo, I'd be happy with the label per month being at the start of the month just so long as it was only the month.  Unfortunately I can't get the example to work so I can't see the one you made.

>>exactly the same as the one you made
it displaying different dates on each label? may I "see" a sample?
Nico BontenbalConnect With a Mentor Commented:
Maybe you can't open my sample because I use XML as the data source. This is not supported in SQL Express I believe. It might also be because I use 2008 R2. I've attached a new sample with SQL as the data source. Maybe that one works. If not, do you get any error messages that might give a clue about what's wrong?
You need to change the Data Source of my sample so it connects to your database.

If you can't get the sample to work, this is what I did:
I have a data source with the fields Date (4/1/2013-5/31/2013) and the field Max which is a random number. In a table this looks like this:
I have as chart on my report like this:
Chart design
For the 'Horizontal Axis Properties' I have:
Axis properties
NumberThis gives me:
BWFCAuthor Commented:
Nicobar, I've changed the settings to the ones you suggested and that seems to have solved it. One thing I did notice was that you were using the sum of the Max field where I was using the un-aggregated field.  Possibly dumb question time; will that make a difference to the charts?  In my inexperienced eyes the sum of the field and the raw field will be different values.

Paul, thanks for that link.  There's a lot on there that's worth reading.  As I said earlier, as a team we're rarely asked to produce charts and graphs so there are definite gaps in our collective knowledge and a link like that will be very useful.
I am very pleased to hear progress is being made :)
Nico BontenbalCommented:

<<the sum of the field and the raw field will be different values>>
Depends on your data. Suppose you have a query with columns 'date', 'customer', 'order amount'. When you want a chart that identifies your biggest customer you can use customer for your x-axis and sum(order amount) for your y-axis. This shows the total order amount per customer (so not the total of all the orders). When you have a query with columns 'customer', 'total order amount' (and only 1 row per customer), you'll get the same result for the raw field or for the sum.
When you just drag the fields into the chart like I did, SSRS assumes you want the sum, and I left it like that.
Since your sample data seems to have distinct dates in it, I would be very surprised if you would getting a different result from sum then from the raw field.
@Nicobo, pleased you replied I was going to make a similar remark but wasn't sure what you had done. I too would be surprised if sum() made a difference here as there most likely there is a single value per date (hence most likely sum() = raw value).

date          value
2013-01-01 100 -- << assuming there is no other value on this date

sum(value) group by date =

2013-01-01 100
BWFCAuthor Commented:
Nicobo, apologies for that, damn internal autocorrect!.

What you say about the values for distinct dates makes complete sense.  I didn't think it would make a difference but I'd been caught out by other things where the actual outcome was very different from what the function name/option chosen led me to expect I was going to get.  That is something that will come with experience.

Thank you both for your help.  I'm going to say that the problem is solved now.  I've learned a lot about producing charts in the last few days particularly from the link you posted Paul.  Some of the sections on there explain how some things are calculated or defined and that will make life easier in the future.  It'll be a lot easier to get the right results out when I can enter data in a better format.
thanks BWFC, really very happy you got (at least some of) what you were looking for
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.