HSI_guelph
asked on
Need help with Data Bar chart
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 es.Value) + Sum(fields!Labour.Value)) > fields!Budget.Value, "Red",
(Sum(Fields!Expenses.Value ) + Sum(fields!Labour.Value)) > fields!Budget.Value*.85, "Orange",
(Sum(Fields!Expenses.Value ) + Sum(fields!Labour.Value)) < fields!Budget.Value*.85, "Green")
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?
ASKER
Also I am getting a warning
Warning 1 [rsRuntimeErrorInExpressio n] The Color expression for the chart ‘DataBar2’ contains an error: Input string was not in a correct format. C:\Users\hsiadmin.HUMANSYS TEMS\Docum ents\Visua l Studio 2008\Projects\Report Development\Report Development\BudgetExample. rdl 0 0
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
You're welcome. When I read your post of 2012-04-05 at 16:12:19 I was afraid we still had a long way to go. So my compliments to you for getting this working with only a few hints.
ASKER
Thank you :)
ASKER
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