Solved

Function Needed to Show Discrete Months on Chart Labels

Posted on 2013-05-23
23
440 Views
Last Modified: 2013-06-03
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.
0
Comment
Question by:BWFC
  • 11
  • 8
  • 3
  • +1
23 Comments
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39190504
What happens if you set Interval to 1?
0
 

Author Comment

by:BWFC
ID: 39190511
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.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39190661
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)
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:BWFC
ID: 39191045
Tick mark settings
As requested
DiskUse.csv
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39192861
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.
0
 

Author Comment

by:BWFC
ID: 39193478
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.
DiskUse.xlsx
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39193604
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.

however:
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?
0
 

Author Comment

by:BWFC
ID: 39193632
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
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39193817
:) 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)
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39193819
will you hate me?....

have you considered presenting  % of space instead of absolute figures (no need to answer)
0
 

Author Comment

by:BWFC
ID: 39193837
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.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39193881
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

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

does any of this make progress?
Q-28136804-suggest.png
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 39197611
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.
ChartMonths.rdl
0
 

Author Comment

by:BWFC
ID: 39200897
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.

Thanks
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39200937
>>exactly the same as the one you made
it displaying different dates on each label? may I "see" a sample?
0
 
LVL 22

Assisted Solution

by:Nico Bontenbal
Nico Bontenbal earned 250 total points
ID: 39204782
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:
Data
I have as chart on my report like this:
Chart design
For the 'Horizontal Axis Properties' I have:
Axis properties
and:
NumberThis gives me:
Chart
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 39209868
Just wondered if there was anything here that would help out:
http://technet.microsoft.com/en-us/library/aa964128(v=sql.90).aspx

looks quite relevant
0
 

Author Comment

by:BWFC
ID: 39215479
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.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39215511
I am very pleased to hear progress is being made :)
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 39215520
<<Nicobar>>
:-)

<<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.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39215538
@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
0
 

Author Comment

by:BWFC
ID: 39215550
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.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39215565
thanks BWFC, really very happy you got (at least some of) what you were looking for
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question