SSRS - How to align the vertical axis of 2 chart areas on the one chart

Question

I have basically loosely followed this link

http://www.angelsbiblog.com/2012/02/improve-data-visualization-in-your-ssrs.html

and made the below linked graph. Its one dataset, I have simply pulled in Gross Profit and Sales fields. Neither are calculated fields. I put them in 2 different chart areas, but then as per that link, made the chart areas the same size so they overlay.

*Apologies for a photobucket link instead of inserted image but I don't have 10 reputation points to be able to insert images.

http://i1375.photobucket.com/albums/ag447/AndrewJacksons/IncomeandProfit_zpse074ac02.jpg

what I want to do, is as illustrated by that inserted green arrow in the graph image, is raise up the Zero line for the Income bars (yellow) to the same level as the Profit/Loss(Blue-Red).

I also want the vertical axis to preferably have the same axis, so i dont have to have that secondary axis on the right.

However the main thing is the graphs sharing their zero line. I have made the Profit bars smaller I width than the yellow bars, so in a month of blue profit, it would simply sit neatly inside the yellow income bar.

I haven't added expenses because it should be obvious what they are by the height differential btw Income to Profit or to the Loss.

Any ideas much appreciated.

Answer 1

I have just experienced this problem, but this page did not solve it.

Dan's answer ("simply set the minimum and maximum values for the vertical axes on both areas manually") came close, but did not solve the problem for me because I needed the axis to be automatically calculated. If the maximum of the two datasets is something like 193,456 then you get that exact value as a label on the axis rather than the sensible value of 200,000.

The solution is to allow SSRS to calculate the axis labels automatically but to trick it by using both sets of data in each chart. Then you hide the data set that you don't want the user to see.

In each chart I made the data series of interest a column chart and the other data series a line chart (without markers), as all you need to do is set the fill color for the line series to None. If you try the same with columns for the other series the invisible columns affect the position of the visible columns even if they have been set to zero width.

Answer 2

Make sure both series in the chart use the Primary Vertical axis. Go into properties for the Income series > Go to "Axes and Chart Area", and make sure that the series uses the primary vertical axis: