How to display a progress bar with multiple colors in a table cell in SSRS report?

I would like to change the presentation style of the Usage Rate column from a simple percentage with single background color to a bar, or a progress bar, with multiple colors based on the percentage value in a SSRS report. For example, when it is 90%, the portion of 1% to 80% should be displayed in dark green color, and the portion of 81% to 90% should be displayed in light green color.

Transformation of Usage Rate Presentation Style

I have tried to use data bar but it can only display in 1 color.

Data Bar Presentation

May I know how to achieve this? Any help would be appreciated.

I'm not sure there is another way to do this but you can do this using subreports for your bars.

This answer is LONG but it's actually pretty simple to follow, it took me about 15 mins top put this test report together.

To start we need to create a subreport that accepts a single parameter. In this example I've called this parameter @p1. This will be the usage rate from your main query but we'll cover that bit later.

So, create a report, I called this _subMultiColourBar. Add a new dataset query with the following query

SELECT  'a' as SeriesCol,
      grp1 = CASE WHEN @p1 <= .8 THEN @p1 ELSE .8 END
    , grp2 = CASE WHEN @p1 <= .8 THEN 0 WHEN @p1> .9 THEN .1 ELSE @p1-.8 END
    , grp3 = CASE WHEN @p1 <= .9 THEN 0 WHEN @p1> .95 THEN .05 ELSE @p1-.9 END
    , grp4 = CASE WHEN @p1 <= .95 THEN 0 ELSE @p1-.95 END

This query will generate 4 numbers which will represent each of the 4 bands in your data. I used

• 0-80% (0-0.85)
• 80% <=90% (.8 - .9)
• 90% <=95%
• 95%

You can change these boundaries in the query above if they are not exactly what you need.

For example if I pass in 0.97 (97%) then we'll get 0.8, 0.1, 0.05 and 0.02 returned. The SeriesCol is just a dummy entry to make it easier to setup the chart.

Now add a stacked bar chart to you report, set the series group to SeriesCol and then drag the 4 grpN columns onto the chart. The chart data setup should look like this.

Next we need to edit the horizontal axis to have a Min value of 0 and a Max value of 1. This prevents the axis from automatically scaling.

Next we need to add a label. In this I added the label to only the first series (as it will be the largest part of the bar). I set the label value to =Parameters!p1.Value and set the format to p0 (percentage with 0 decimals). Finally I set the Color property to "White"

Next you need to edit the p1 parameter that was generated and set the data type to float

If we run the report, we can test using a few values, here for example are 80,90 and 97..

Now that we have the basic chart working, remove all the titles, axis, legends etc and resize it to close to what you want in the main report.

Save the sub report.....

Next create a new report (or edit your existing one)

For this report I used a simple dataset that contains an ID and some 'amounts' (Usage rate for you).

I added a table that shows the ID, Amount and then added an extra column. In the new third column, I right-clicked and chose "Insert --> Subreport".

Right-Click the sub-report placeholder and set the following properties.

General Tab "Use this report as a subreport" = _SubMultiColourBar (or whatever you called you sub report that we created above.

Parameters Tab Add parameter, chose (or type) p1 in the name field and choose [Amount] in the value field.

Final main report design looks like this

When we run the report we get this...

You can adjust the custom plot positions in the subreport to remove the excessive whitespace, I can't remember exactly what needs changing but have a play and see what works.