如何在SSRS中复刻Excel组合饼图并拆解Success v2分段数据

Hey Charlotte, I’ve run into this exact scenario before when trying to replicate nested pie/donut charts in SSRS—since there’s no built-in nested chart type, we have to simulate it by combining two charts and syncing their data. Here’s a step-by-step guide to get this working:

First, let’s make sure your dataset is set up correctly. You’ll need fields for:

• The main categories (Success v1, Success v2, Success v3, Failed)
• The time-taken breakdown categories specifically for Success v2 (e.g., <1min, 1-5min, 5+min)
• A measure (like count or percentage) for both the main categories and the time-taken segments

Step 1: Build the Main Donut Chart #

1. Drag a Donut Chart onto your report canvas.
2. In the Chart Data pane:
   • Add your measure (e.g., CountOfRecords) to the Values area.
   • Add your main category field (e.g., MainStatus) to the Category Groups area.
3. Adjust the inner radius to make room for the nested pie:
   • Right-click the donut series > Series Properties > Go to the Custom Attributes tab.
   • Set InnerRadius to 60-70% (play around with this until there’s enough space in the center).
4. Format the donut with colors and data labels to match your Excel chart.

Step 2: Create the Success v2 Breakdown Pie #

1. Drag a Pie Chart onto the report, and place it directly over the center of the donut chart.
2. Filter this pie to only show Success v2 data:
   • In the Chart Data pane, right-click the dataset > Add Filter.
   • Set the filter to MainStatus equals Success v2.
3. Configure the pie’s data:
   • Add your measure (e.g., CountOfRecords) to the Values area.
   • Add your time-taken category field (e.g., TimeTakenBucket) to the Category Groups area.
4. Resize and position the pie so it fits perfectly inside the donut’s inner hole. Use the alignment guides in SSRS to center it precisely.

Step 3: Sync and Polish #

• To keep the charts aligned, place both inside a Rectangle container. Just drag a rectangle onto the canvas, then move both charts into it—now you can move/resize the rectangle without messing up their positions.
• Adjust the z-order if the pie is hidden behind the donut: right-click the pie > Bring to Front.
• Add data labels to both charts: for the donut, show the main category and percentage; for the pie, show the time-taken segment and its percentage relative to Success v2.
• Optional: Add a small line or text box to explicitly link the Success v2 segment of the donut to the inner pie, so viewers understand the relationship.

Troubleshooting Common Issues #

• If the inner pie isn’t filtering correctly: Double-check the filter on the pie chart—make sure it’s targeting the right field and value.
• If the charts don’t align: Use the Properties pane to set exact X/Y coordinates and width/height for both charts, so they’re perfectly centered.
• If percentages look off: Ensure your measure is calculating percentages correctly (e.g., use CountOfRecords / Sum(CountOfRecords) Over () for the main donut, and CountOfRecords / Sum(CountOfRecords) Over (Partition By MainStatus) for the inner pie).

This method should replicate the combined chart you have in Excel, with the Success v2 segment broken down by time taken. Let me know if you hit any snags along the way!

内容的提问来源于stack exchange，提问作者charlotte.emily