in

SQL Server Blogs

Voices from the UK SQL Server Community

Atul Thakor

Reporting Services 2008 R2 Gauges setting scale and colour based on value

So…. I started to play with reporting services this week and its proving to be pretty good fun so far… here is a very quick post covering the setup of a gauge, altering its scale and changing its colour based on input values.

  1. Firstly create a blank reporting services project
  2. Add a new Shared Data Source (for my example I’m using AdventureWorksLT2008) call this GaugeSource
  3. Add a new Shared Dataset using GaugeSource as your datasource, I’m using the following query which returns sales based on the ProductID supplied, name this GaugeData. You don't need to worry about populating the @ProductID variable yet, when you run the report you’ll be prompted for a ProductID.
  4. select
    ProductID,
    LineTotal
    from
    SalesLT.SalesOrderDetail
    where
    ProductID = @ProductID

  5. Add a new report (without the wizard)
  6. Using the toolbox add a Gauge to the report and choose the default dial
  7. When asked which dataset choose GaugeData
  8. To change the scale right click on the Gauge->Gauge Panel->Scale Properties
    image
  9. I set the maximum to 40,000 because I know that the maximum value is 37,000, but change this as per your data
  10. Now you need to specify the value you wish to indicate on the dial, on my query I care about the LineTotal, to do this click on the dial (you may need to click twice, once to focus on the report and then once again to click on the dial), You’ll notice that the sum function is automatically applied to the LineTotal, this is the normal behaviour when using numerical values in SSRS
    image
  11. If you click preview you should see the following, you need to enter a value before the report can run , in my example I enter 969 and hit view reportimage
  12. Depending on your scale/data you should see something similar to this:
    image
  13. Now lets set the back colour based on the value shown
  14. Right click on the dial and click “Gauge Properties”
    image
  15. On the following screen click Back Fill and then flick the Fx button:
    image
  16. Paste the following into the expression window:
    =IIF(sum(Fields!LineTotal.Value) >= 30000, "Green", 
    IIF(sum(Fields!LineTotal.Value) <= 500, "Red", "WhiteSmoke"))

    The query states that if the sum of LineTotal >= 30000 set the colour to Green, if the sum is <= 500 set the colour to Red, else WhiteSmoke.
  17. You can test various values and colours

Hope this helps!

    Comments

    No Comments

    About AtulThakor

    Twitter:@AtulThakor
    Powered by Community Server (Commercial Edition), by Telligent Systems