SSRS 2012 100% Stacked Column Chart, I need to also dynamically change column widths

Hello Experts,

I am trying to create a 100% Stacked Column Chart with the widths of the Columns also totaling 100%, so both the X and Y Axis will equal 100%. The stacked columns are easy and displaying as needed, but now I need to dynamically set the width of each column based upon the percentage of the total it represents on that axis. I did find an article where you go into the Custom Attributes and set the PointWidth value, the problem is this sets all columns to the same width, I need each column based on the percentage it represents.

My data looks like:
Data being return from SQL Query
Each column is represented by a Segment and the stack is the Quality, the value is the SegmentAGR as a percentage of the total segment. I would like the width of the bar to be represented by the PercentagePortfolio value which is tied to the Segment as well, I would need to use the FIRST() of each PercentagePortfolio for the segment to equal 100%.

I'm sure this can be done and I am not searching the appropriate term to get the results I am looking for, any help is appreciated.
carbonbasedAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nico BontenbalCommented:
I'm afraid it's not possible. Like you said the width is controlled by the PointWidth, but that's one value for the whole chart, not for each series.
It might be possible to get a similar effect by repeating the same data based on the percentage. So F you repeat 65 times and G you repeat 19 times. This would (with the proper configuration of the chart) result in 65 bars for F and 19 bars for G. When the space between the bars is 0, the bars have no border and you take complete control over the colors of the bars that might give you the desired result. Placing the labels will probably be the biggest challenge. Along with writing the SQL code that repeats the data.

Another solution might be to draw the 'chart' yourself entirely using a map. With a map you can just 'draw' the shapes you like. Again the labels and the SQL code will be challenging. See the attached sample where I use a map (but entered the data manually).
Map.rdl
carbonbasedAuthor Commented:
Nicobo,

I was getting an error when I try to open your sample so I was unable to look at that. My thoughts are leaning towards a time based x-axis but I just don't know how to set the properties to do what I am thinking. Since my x-axis adds up to 1.00 (100%) can I somehow set the x-axis to be time based on hours with a minimum of 0 (zero) and a maximum of 1 (one). In my data the largest value would be for the R segment at 0.6269 so it would represent 0.6269 hours (basically 62.7% of the x-axis).

I also thought maybe an area chart with the same thinking but the user wants nice linear columns of stacked data not the pointy mountains typical of an area chart. I'm sure there's a way to do this, I've seen it done in Excel but haven't found an example in my searching.
carbonbasedAuthor Commented:
OK Experts I have found some additional information. The chart I am trying to create is called a Marimekko or a Mekko chart. So far I've been unable to locate a vendor that makes a plug in for SSRS or an article on how to do it in SSRS. If I find the answer I will post it, in the meantime if any of you can assist, I would appreciate the help.

Here is a sample...
Marimekko Chart sample
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Nico BontenbalCommented:
I've updated my previous example so it uses your data to calculate the coordinates. This is the closest I can get to a Marimekko in SSRS. I suppose you also searched for 'SSRS Marimekko' and didn't come up with any useful results. So I'm afraid the only way to do this is the hard way. You need to learn about spacial data in SQL Server (https://msdn.microsoft.com/en-us/library/bb933790.aspx) and the map control in SSRS (https://msdn.microsoft.com/en-us/library/ee240845.aspx).

My sample has two shortcomings (as far a I can see). The first is that the labels are missing when the bar is too small. The second is that there are no labels on the x and y-axis. I don't know how to fix the first one. But the second one might be fixable by adding lines to the map.

To get this sample working with your data you need to change the 'insert into #Mekko' part of my SQL to use your data instead of the fixed data in my sample.
Map.rdl

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nico BontenbalCommented:
I've looked at a 'time based x-axes'. But I don't think you can get around the mountain peaks thing. Excel has a property for this, but SSRS doesn't seem to have one.

You said you got an error message with my previous sample. You probably get the same with this one. I suppose it has to do with the data source. It point to my local SQL Server. You need to change this to point to your SQL server. If you still have problems with the sample I would like to see the error message. Maybe I can find a solution for you.
carbonbasedAuthor Commented:
I've accepted this as the solution, it is not the perfect solution but gets me headed in the right direction. I was able to figure out the error I was getting (not related to DB connection) and look at what you did. I was hoping for something easier or a third party component I could purchase, but I haven't found one for SSRS. Thanks for the help, I may have to use this and figure out the labeling errors as some of the data areas are quite small.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.