Solved

Function Needed to Show Discrete Months on Chart Labels

Posted on 2013-05-23
23
428 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
Comment Utility
What happens if you set Interval to 1?
0
 

Author Comment

by:BWFC
Comment Utility
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
Comment Utility
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
 

Author Comment

by:BWFC
Comment Utility
Tick mark settings
As requested
DiskUse.csv
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
:) 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
Comment Utility
will you hate me?....

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

Author Comment

by:BWFC
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>>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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I am very pleased to hear progress is being made :)
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
Comment Utility
<<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
Comment Utility
@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
Comment Utility
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
Comment Utility
thanks BWFC, really very happy you got (at least some of) what you were looking for
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now