SSRS 2008 R2 Bubble chart
We created bubble charts with ease using Microsoft Excel. Now we wanted to implement the same using SSRS 2008 R2. But, it seems to be too complicated. This is the actual requirement The X and Y axis are secondary and we are taking the Logarthmic scale for both the axis. The bubble size is dependent on a field. Tried all possible ways but not able to replicate as Excel Sample Data Count Data1 Data2 123 0.03 0.05 Like that. Data1 should be Y axis, Data 2 would be X axis and Count determines the bubble size. Its pretty straight forward in excel and we were not able to figure out what exactly is going wrong. The dataset is pulling the same values that are in Excel.
October 27th, 2010 5:47pm

Hi Lellah, Based on the Sample Data you posted, I have created a Chart report in Reporting Services 2008R2 as the following pictures shows: If it is want you want, please refer to the steps below to achieve this: 1. Add a Chart control to the report. 2. In the dialog Select Chart Type, select the Bubble chart in Scatter category. 3. Double click the chart control in the report to display the Chart Data pane. 4. Drag data field Data1 in Report Data window and drop it in ∑Values list in Chart Data pane. Then, we will see the Data1 item with 3 sub items added in the list. 5. For the second sub item labeled Size, click the arrow after it and select Count field in the popup menu. 6. For the last sub item labeled X Value, click the arrow after it and select Data2 field in the popup menu. After that, the chart will display different size squares in their corresponding positions. If you need to change the squares to circles, please refer to the steps below: 1. Right Data1 in ∑Values list in Chart Data pane, select Series Properties… 2. In the Series Properties window, select Markers in the left list. 3. Select Circle in the Marker type dropdown list. Then click OK. To add data labels for count, please follow the steps: 1. Right Data1 in ∑Values list in Chart Data, select Show Data Labels. 2. Right one data label in the chart, select Series Label Properties… 3. In the Series Label Properties window, type in the expression =Fields!Count.Value in the Label data dropdown list. Click Yes in the popup dialog and then click OK button. It will result in the example chart I posted. If you have any question, please feel free to ask. Thanks, Tony Chain. Please remember to mark the replies as answers if they help and unmark them if they provide no help
Free Windows Admin Tool Kit Click here and download it now
October 29th, 2010 4:10am

I tried your solution but its not working. The chart in SSRS doesn't match with Excel. Here is actaully what I am trying to do. We have 3 fields called impressions, CTR and pClick. CTR is on X axis, pClick is on Y Axis and the size of bubble depends on the Impressions. Also, I am using the logarithmic scale for both X and Y axis (I am using secondary Axis). Here is the sample data. I am trying to paste the Excel and SSRS charts that I am getting but, I am unable to do that. Impressions CTR pClick 7820995 0.000162894874629123 0.00161223412110607 2054359 7.73964044259061E-05 0.000871136106201496 942173 0.000644255354377593 0.00470359955125014 653449 0.000205065735811058 0.00269436666059631 599561 0.000348588383834172 0.00140498481388883 587817 6.63471794793278E-05 0.00130259111254013 506789 0.000416346842571563 0.00310209870379981 471576 0.000311720698254364 0.00243842116647158 302891 0.000165075885384511 0.0013853260743964 182928 0.000235065162249628 0.00120853013207382 135015 0.000377735807132541 0.00392892863755879 112197 5.34773657049654E-05 0.000769963546262378 111912 0.000160840660518979 0.00152219601115162 101862 0.000382870943040584 0.00523398225049577 101290 0.000987264290650607 0.00922958732352651 100818 4.95943184748755E-05 0.00121034934237934 92606 0.000183573418568991 0.00166024339675615 61494 3.25234982274693E-05 0.000625817152892965 49887 0.000200453023833865 0.00135624511395754 40708 2.45651960302643E-05 0.000456126559889948 37401 0.000267372530146253 0.00387493649902409 36017 0.000360940666907294 0.00336509981397673 30523 3.27621793401697E-05 0.00159642892245192 25012 0.000159923236846314 0.00353309211578442 20046 0.00234460740297316 0.0148284146463135 19504 0.000769073010664479 0.00991070036915504 19301 0.000466297083052692 0.00332094710118647 17635 0.000113410830734335 0.00135504961723845 Harsha
October 29th, 2010 5:12pm

Hi Harsha, Could you please send me the sample Excel and the report you created to the following E-mail address? It would help us to understand the expected result, and then try to achieve it. E-mail: sqltnsp AT (Please replace the AT with @, and remove additional spaces) Thanks, Tony ChainPlease remember to mark the replies as answers if they help and unmark them if they provide no help
Free Windows Admin Tool Kit Click here and download it now
October 31st, 2010 10:57pm

Hi Tony, I e-mailed the Excel doc with Bubble chart.Harsha
November 1st, 2010 3:36pm

Hi Harsha, Thank you for the example, and I have created a similar chart in Report Services based on the Excel. Please see the following picture. First, I need to clarify that by design Report Services only support logarithmic scale on the y-axis (please see: However, from my testing, we can achieve logarithmic scale on both axes by setting the Chart in a particular order. Please follow the steps below: 1. Add the 3-D Bubble Chart to the report. Bind data fields in dataset to corresponding Chart Data (Data Values, Size and X Value) and set the Marker type to Circle as we have achieved. 2. Before specify axis position to secondary, please try to set logarithmic scale on the primary horizontal axis. In my environment, if I first change the axis to secondary, I will not be allowed to set logarithmic on the secondary horizontal axis. So, please check Use logarithmic scale in the primary Horizontal Axis Properties dialog and click OK. 3. Specify both axes positions to Secondary. Then we can set Logarithmic scale on secondary horizontal axis. 4. In the Secondary Horizontal Axis Properties dialog, please check the Use logarithmic scale and keep the default value 10. Then specify the Minimum to 0.000001 and Maximum to 1. Also specify the Interval type to Number. 5. Click Number in the left list, select Number in the Category list and set the Decimal places to 5. Then click OK. 6. Repeat the similar steps to set logarithmic scale on the secondary vertical axis. However, there is no need to set the primary vertical axis at first. After these steps, you may configure the appearance of the chart. For example, we can enable Minor Gridlines and set the Grid interval to 1. And as the picture shows, even though the bubble size is corresponding to the impression field, it is not as big as it shows in Excel. So we can set the BubbleMaxSize and other properties in the CustomAttributes of the series in Chart. You can also refer to the sample report I created, it is available on: Thanks, Tony Chain Please remember to mark the replies as answers if they help and unmark them if they provide no help
Free Windows Admin Tool Kit Click here and download it now
November 2nd, 2010 2:22am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics