Search code examples
reporting-servicesssrs-2014

SSRS-Best way to represent specific metrics

I'm self-training in SSRS 2014. I have a specific set of data I'd like to represent, to wit: we have a fund-raising goal, and have money coming in toward that goal. The money coming in falls into one of four different buckets - so I'm looking for something stacked-but-horizontal (one bar) that shows the bucketed totals, and then some "blank" space representing what we need to reach our goal. I've tried a linear gauge (couldn't figure out how to get the "bucketed" portions to show, even with the data group); a data bar (couldn't get the labels to display correctly because they must be displayed inside the bar); other representations cause multiple bars, which I don't want.

I've been researching and trying things for two days...I'm wondering if this thing that seems so simple is in fact not particularly do-able in SSRS, or if I've just chosen poor methods. What IS the best way to represent these data? Thank you!

Solution

  • I believe your best bet is a simple bar graph. In my opinion, the key here is to have the value of the bar be represented by the percent of the goal collected. However, you want the label for each bar to be the actual amount of money collected thus far. This way, your bar grows proportionally as it approaches the goal, but the label shows the amount collected.

    Alternatively, you could create a tablix containing the percent completed as a databar and show the amount collected thus far versus the goal.

    Included is a screenshot of the samples. Please note the following:

    1. I hard-coded the names of the colors in the query. This is a great way to control how your charts look and feel. Then you simply change the color to represent an expression equal to the color field's value. For varying light and dark colors, you can also set the font color, so if you have a light blue bar you can have a black text instead of white or vice versa.

    2. When using labels on bar graphs, you need to code in a bare-minimum value for the bar. This will ensure that when you have something like Bucket 5 where you're at 5% of the goal (a relatively small bar) but the amount is large ($124,243), that the bar will adjust to be big enough to contain the label. In this case, if the percent collected is less than 15%, then the bar size will be 15%.

      Examples

    If you have any questions, leave a comment and I'll do my best to help. The query I used to generate the dataset is below.

    With CTE as (
Select 'Bucket 1' as bucket, 'blue' as color, 'white' as text_color, 50 as percent_total, 15236 as amount Union all
Select 'Bucket 2' as bucket, 'red' as color, 'white' as text_color, 33 as percent_total, 24685 as amount Union all
Select 'Bucket 3' as bucket, 'green' as color, 'black' as text_color, 67 as percent_total, 41457 as amount Union all
Select 'Bucket 4' as bucket, 'purple' as color, 'white' as text_color, 95 as percent_total, 32493 as amount Union all
Select 'Bucket 5' as bucket, 'black' as color, 'white' as text_color, 5 as percent_total, 124243 as amount
)

Select CTE.*
    , CAST(amount as float) / (CAST(percent_total as float) / 100.00) as Goal

From CTE

Order by Bucket desc

    Edited based on Comments

    If you want all buckets with the remainder on a single bar, then you can accomplish that with the following (although I'm not sure how you would handle labeling the amounts to the individual buckets).

    The End Result

    enter image description here

    The Query

    Declare @Goal int = 500000;

With CTE as (
Select 'Bucket 5' as bucket, 'blue' as color, 'white' as text_color,  15236 as amount Union all
Select 'Bucket 4' as bucket, 'red' as color, 'white' as text_color, 24685 as amount Union all
Select 'Bucket 2' as bucket, 'green' as color, 'black' as text_color,  41457 as amount Union all
Select 'Bucket 3' as bucket, 'purple' as color, 'white' as text_color,  32493 as amount Union all
Select 'Bucket 1' as bucket, 'black' as color, 'white' as text_color, 124243 as amount
)

Select CTE.bucket
    , CTE.color
    , CTE.text_color
    , CTE.amount
    , CAST(CTE.amount as float) / CAST(@Goal as float) as percent_total
    , 'white' as border_color

From CTE

Union All

Select 'Remaining' as bucket
    , 'Transparent' as color
    , 'Transparent' as text_color
    , @Goal - SUM(CTE.amount) as amount
    , CAST(@Goal - SUM(CTE.amount) as float) / CAST(@Goal as float) as percent_total
    , 'black' as border_color

From CTE

Order by Bucket

    The Setup in Report Builder

    Note that I've dynamically changed the border color to be:

    =Fields!border_color.value

    and the color of the bar to be:

    =Fields!color.value

    enter image description here