Need some help with a stacked bar chart

[BigTeeJay]

I havent been able to find very much in the way of examples for Reporting Services, so I am hoping someone might be able to help me out here.

I am trying to recreate a chart that I created in Excel. Its a stacked bar chart, with a trendline running along the bottom of the X's in the below graph. It also has two horizontal cut-off lines (to depict instances where the bars are above/below a certain threshold).

It looks something like the following...

10 |
   |              Y
8  |             XY
   |          X   
6  |        Y XY
   |    XY XY XY
4  | X  XY XY  Y
   | XY XY XY 
2  | XY
   | 
   -----------------
     A  B  C  D  E

In my Excel version, the X bar is always one color (black), the Y another (gray).

Any ideas on how I could accomplish this? Any resources that I could be pointed to?

 

[ookete]

Can you give an example of your tabular source data? Just wondering what kind of manipulation you require from grid to chart.

 

[Catadmin]

There is a Stock Chart buried in RS somewhere that sounds similar to this. Have you looked at all the Chart options in RS yet? There's a ton of them, and they have different visual effects to them.

Go to the Chart control's properties box, and you should see the drop down list of charts. Clicking on each one of them should show you their options.

Of course, you'll have to drag the chart to the layout first. @=)



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[BigTeeJay]

Here is an example of the underlying data as CSV (it wasnt easy to get it to work in Excel).

Widget,Series A Start,Series A End,Series A Bar Start,Series A Growth,Series A Growth (Negative),Series B Start,Series B End,Series B Bar Start,Series B Growth,Series B Growth (Negative),Trendline,Separator,Separator
,184.45860,186.18604,184.45860,1.72744,#N/A,,,,,,184.45860,,
A,,,,,,184.45860,189.31197,184.45860,4.85337,#N/A,184.45860,,
,,,,,,,,,,,185.49065,0,0
,186.52270,186.52275,186.52270,0.00005,#N/A,,,,,,186.52270,,
B,,,,,,186.52270,186.84784,186.52270,0.32514,#N/A,186.52270,,
,,,,,,,,,,,187.23680,0,0
,187.95090,189.56324,187.95090,1.61234,#N/A,,,,,,187.95090,,
C,,,,,,187.95090,190.86328,187.95090,2.91238,#N/A,187.95090,,
,,,,,,,,,,,189.72800,0,0
,191.50510,193.27811,191.50510,1.77301,#N/A,,,,,,191.50510,,
D,,,,,,191.50510,191.42401,191.42401,#N/A,0.08109,191.50510,,
,,,,,,,,,,,192.19375,0,0
,192.88240,190.42733,190.42733,#N/A,2.45507,,,,,,192.88240,,
E,,,,,,192.88240,194.76117,192.88240,1.87877,#N/A,192.88240,,
,,,,,,,,,,,192.88240,0,0

I could probably post the xls somewhere so people could see what I'm trying to recreate. Anyone know of a good place to do so?

 

[ookete]

I imported the CSV info to Excel, and I see what you are going for now. I agree that just looking at the basic chart offerings, your need is not covered.

You may want to just draw the chart yourself using GDI+. You could make a "chart region" class and a "bar element" class. The chart region is given corner coordinates so it can draw itself on the screen. The bar elements use the chart region's vertical range ratio (pixels/value) and draws itself relatively. Would be tricky but fun!

 

[BigTeeJay]

Ugh

Something else I was thinking... I heard that the charting stuff in here was done via licensced Dundas charting components. I wonder if I got some of the full-blown Dundas charting libraries if I could include them in a report via Reporting Services?

I remember seeing a how to that showed how to render a chart/report via ASP.

Think it would be worth looking into? I'm not very familiar with GDI+ (I know what it is, but havent ever had to code for it).

 

[BigTeeJay]

Or, another question (related to going the Dundas route)... might it possible to create this kind of graph programatically via .Net (and it just isnt possible to do through the visual design interface?)

 

[ookete]

Yes, you could look into Dundas. I went to their website the other day, and saw that they had additional offerings that looked pretty nice.

From what I've seen with Reporting Services, programmatic control is limited. It's mainly useful for making certain elements visible at certain times, or format conversions, etc. I don't think they expose any interface that allows you to alter how the chart itself works or displays. I could be wrong though.

 

[BigTeeJay]

MS licensed a subset of the Dundas charting package for their charts in Reporting Services, so I figured they would know.

I contacted them and asked them about it, below is their reply...

This can be done with our Charting package either on it's own or in Reporting Service Yukon with our upgraded charting package.

The upgraded package will be our Enterprise charting package, it will expose our full api except:

- no rending to formats other than image
- no rendering to smart client control
- no tooltip support (limitation of the RS for scripting security)
- no databinding support outside of the RS databinding however after the data bind data can be customized through code -- this includes removal and adding new points to a series

So it sounds like this chart cant be created with the current version of RS's native charting capacity.

However, I read somewhere about calling a webservice for the source for an image in Reporting Services. I wonder if I could create a webservice that would render my chart (by whatever means, GDI, Dundas, ChartFx, etc) and return it to Reporting Services as a graphic in the image.

I'll post any luck I have with this (if any).