From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.
You need xMatters.
Course Of The Month
2 days, 1 hour left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Need help with Data Bar chart
Posted on 2012-04-04
I've created a report that lists projects, their budgets, amount already spent and amount remaining in the budget. What I'd like to do is have a small data bar chart in the last column that visually displays the amount of the budget spent for each client/project as a percentage.
I attached a picture of what I had in mind. At a glance my boss would be able to look down the page at a list of projects and see which ones were 50% spent, 90% spent or overspent. I'm just not sure how to customize a chart to do this. Plus a lot of my figures are calculated so perhaps I should create a seperate dataset for the chart? I thought of using a pie chart but I think the data bar chart is a better solution.
Any ideas or input please share! If anyone with report/chart experience has a suggestion of how to display the desired graphical data I'm all ears!
budget-data-bar.jpg
I attached a picture of what I had in mind. At a glance my boss would be able to look down the page at a list of projects and see which ones were 50% spent, 90% spent or overspent. I'm just not sure how to customize a chart to do this. Plus a lot of my figures are calculated so perhaps I should create a seperate dataset for the chart? I thought of using a pie chart but I think the data bar chart is a better solution.
Any ideas or input please share! If anyone with report/chart experience has a suggestion of how to display the desired graphical data I'm all ears!
budget-data-bar.jpg
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
5
3
8 Comments
See this article: http://technet.microsoft.com/en-us/library/dd239361.aspx
You'll need the "stacked bar". Not sure what you want to do with the overspent. I think you should have a green bar up to 100% and then a red bar attached to it with the overspent (10% for example). You might not need an separate dataset. You probably can do all the calculations in the report.
The data labels are probably the most tricky part because you want to display the total budget also. I need to experiment somewhat to give you the right answer. I can make an example for you but than I need to know what version of SQL server you are using. And could you upload the report (.rdl) here so I can use that as a starting point.
You'll need the "stacked bar". Not sure what you want to do with the overspent. I think you should have a green bar up to 100% and then a red bar attached to it with the overspent (10% for example). You might not need an separate dataset. You probably can do all the calculations in the report.
The data labels are probably the most tricky part because you want to display the total budget also. I need to experiment somewhat to give you the right answer. I can make an example for you but than I need to know what version of SQL server you are using. And could you upload the report (.rdl) here so I can use that as a starting point.
I'm using a virtual machine running MS SQL Server 2008 R2 to access a virtual MS SQL Server 2005 database (does that even matter? that its a different version then what I'm developing on?).
Here is a copy of the .rdl. I'm not sure it matters how much the project is over as much as what is left in the budget (if they are over they are over but if they are way under just before the end of contract they need to allocate the funds since many projects are smaller ones of a master project).
It would be cool if I could get the bar to be green when they are under 50%, yellow if they are 50-75%, orange 75%-90% and red when they are either 100% or 90%-100% (numbers are just examples). I read that there are stop lights I can implement into the report which would be ok to use too. I would like something that is clear and simple to draw the eye to key areas that might be of concern/interest. Any graphical tool would suffice but a bar would show how all projects are doing budgetwise. Also the number of clients on a page may vary and I don't want a big graph on the page but a small indicator per row.
BudgetExample.rdl
Here is a copy of the .rdl. I'm not sure it matters how much the project is over as much as what is left in the budget (if they are over they are over but if they are way under just before the end of contract they need to allocate the funds since many projects are smaller ones of a master project).
It would be cool if I could get the bar to be green when they are under 50%, yellow if they are 50-75%, orange 75%-90% and red when they are either 100% or 90%-100% (numbers are just examples). I read that there are stop lights I can implement into the report which would be ok to use too. I would like something that is clear and simple to draw the eye to key areas that might be of concern/interest. Any graphical tool would suffice but a bar would show how all projects are doing budgetwise. Also the number of clients on a page may vary and I don't want a big graph on the page but a small indicator per row.
BudgetExample.rdl
I got it working! I changed it to a stacked bar, like you suggested, and then went into the series properties fill tab and entered the expression for color:
=Switch((Sum(Fields!Expens
(Sum(Fields!Expenses.Value
(Sum(Fields!Expenses.Value
Its working great but I am running into some instances where I'm dividing by 0 and getting an #Error. If that happens I'd like the fill color to be the same as the background, white, but I am having no luck adding a test for error into my above expression.
Any ideas please?
=Switch((Sum(Fields!Expens
(Sum(Fields!Expenses.Value
(Sum(Fields!Expenses.Value
Its working great but I am running into some instances where I'm dividing by 0 and getting an #Error. If that happens I'd like the fill color to be the same as the background, white, but I am having no luck adding a test for error into my above expression.
Any ideas please?
Also I am getting a warning
Warning 1 [rsRuntimeErrorInExpressio
Do I need to correct this? Will it impact running the report?
Warning 1 [rsRuntimeErrorInExpressio
Do I need to correct this? Will it impact running the report?
Well done! I was just starting the example but it looks like you are ahead of me. You can get around the division by zero by using an if statement. But be careful, both parts of the iif are evaluated, so you might still get an error. So if something like:
You expression for the color returns null if none of the expressions are true. I think that is what causing the error. Try adding
=Sum(Fields!Labour.Value)/Fields!Budget.Value
=iif(Fields!Budget.Value = 0, 0, Sum(Fields!Labour.Value)/iif(Fields!Budget.Value=0,1,Fields!Budget.Value))
You expression for the color returns null if none of the expressions are true. I think that is what causing the error. Try adding
True, "White"
Thank you very much for the help! Sometimes its more helpful to get advice on how or what you want to display rather than the code since the code is pretty rigid, its the mind of the coder that creative and flexible.
Featured Post
Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.
Connect with xMatters.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Introduction:
This article is aimed at report developers who are used to developing reports using relational databases and have gotten a first-time assignment to develop reports on OLAP cubes.
It demonstrates how to build a report using SQL Ser…
Introduction
Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2. In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have.
In this…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Suggested Courses
Course of the Month2 days, 1 hour left to enroll
717 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.