in

SQL Server Blogs

Voices from the UK SQL Server Community

Atul Thakor

April 2011 - Posts

  • 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!

    1. Running Massive Updates, Deletes, Inserts

      Firstly with large transactions comes large transaction logs, ensure you grow your log accordingly before running a huge transaction because autogrows can cause overhead during execution for example: why did the query run so quickly on test rather than live… the likelihood is you ran it repeatedly on your test box and it had grown already.

      If you plan on running a large insert you should grow the data file accordingly, not only will this improve speed but it will also ensure you have enough space for the query to run!

      Run in loops/batches

      Old way:

      Delete From
      MainTable
      where
      ID in(select ID from DeleteList)
       
      Loopy way:
      declare
      @min int,
      @max int,
      @increments int,
      @Counter int

      SET @min = (select MIN(ID) from DeleteList)
      SET @max = (select MAX(ID) from DeleteList)
      SET @increments = 1000
      SET @Counter = @min

      While(@Counter < @max)
      BEGIN
      declare @progress varchar(10)

      Delete From
      MainTable
      where
      ID between @Counter and @Counter + @increments
      and
      ID in(select ID from DeleteList)
      set @Counter = @Counter + @increments

      set @progress = CAST(@counter as varchar(10))
      RAISERROR ('PROGRESS %s', 0, 1, @progress)
      WITH NOWAIT
      END


      So why use the loop?

      Firstly you are running smaller batches so you’ll only be locking subsets, this is especially important when running on a 24/7 environment, secondly you can use the with (rowlock) option if you wish, you can play around with the batch size to find the optimal update size.

      You should also note the following statement:

      RAISERROR ('PROGRESS           %s', 0, 1, @progress) 
      WITH NOWAIT

      When you want to report progress the print statement can be a bad idea as print messages are buffered until the batch completes, the raiserror with nowait option will immediately send the message to you, when running your query if you notice a long delay in messages you are able to react immediately.

      I know this is a fairly back to basics post but this is something I’ve had to go back to lots of times so thought it would be worth sharing!

      I’ve attached the T-SQL.

    2. First time speaking at SQLBits and the laptop debacle

      So this weekend I was lucky enough to get the opportunity to speak at the largest SQL Server conference in the UK, I’d been attending the conference since it began back in 2007 on and off and have seen it grow from strength to strength attracting some of the best in the industry. I had wanted to speak at the conference for a while but had never managed to submit a session, Chris Testa-O'Neill gave me the push I needed and I submitted the session and was lucky enough to get a spot!

       

      So on the day of the talk I turned up bright and early mainly down to not being able to sleep due to a dodgy nightmare where I alt and tabbed during my demo and there was porn on my the projector, there was no chance of getting any sleep after this!

       

      Martin Bell had arranged for me to test out connectivity first thing to iron out any issues as I was using a new laptop which only had a HDMI connection, guess what? It didn't work!!!!!, there was a moment of complete and utter dread, Steve Bradbury relentlessly tried to fix the issue with no joy, at this point Tim Kent stepped in and offered his laptop.

       

      Working through with Tim and with a little help from Simon Sabin I was able to get my presentation ready for the big screen, various members of the SQLBits team were around and checking to make sure things were ok which was reassuring knowing help was there!

       

      I cant express my gratitude enough to the SQLBits team, with so much going on these guy were able to quickly get me a replacement machine, help me fix a couple of issues moving between machines and keep everything nice and calm as well as ensuring the other sessions were running smoothly.

       

      I must admit I was completely thrown after these issues but I have have to say I feel that I definitely came out stronger having been through this, hopefully next time things will go more smoothly.

       

      I also wanted to say a big thank you to the people who came to the presentation and hope they enjoyed it, the feedback will be much appreciated so hopefully I can take something away from this and improve for next time!

       

      A big thank you to the SQLBits organisers and fusionio (and anyone I may have missed!) for delivering an awesome conference and event!

      Looking forward to SQLBits 9,

      Atul

      Scary

      Picture taken from the bean bag diving championships by Carmel Gunn(Prodata)

    Powered by Community Server (Commercial Edition), by Telligent Systems