Creating a heat map in Reporting Services

  • AndyOwl

    Mr or Mrs. 500

    Points: 578

    Comments posted to this topic are about the item Creating a heat map in Reporting Services

    Andy is a director of Wise Owl[/url], a UK company providing training courses (and occasional consultancy) in SQL, Reporting Services, Integration Services and Analysis Services, as well as in many other Microsoft software applications. You can see more about Wise Owl's SQL Server training courses here[/url].

  • PhilDaniels

    SSC Enthusiast

    Points: 151

    I do appreciate the author having presented this article in such a straightforward style..Clearly explained, great examples, and so on. Nicely done.

    I'll probably take some heat for this (pardon the pun) but, for the life of me, I cannot figure out why companies would continue to use SSRS. It takes so long to create simple things that, frankly, are much much easier to do with other tools. Given the amount of time and complexity needed to do basic BI things, it seems to negate the cost savings and delay realization of BI's value in the organization. I realize you can code alot of custom things in SSRS, but if you have to create a custom class and write code to support something as simple as this heatmap example, I believe you are using the wrong BI tools. This takes literally 2 minutes in any data discovery platform...no coding needed.

    Probably just stirred up trouble. I have always been a SQL Server guy from a DW-perspective and always wanted SSRS to be a true contender in the BI space. I just have never really seen it, nevermind what Gartner says.

  • jcrawf02

    SSC-Insane

    Points: 24198

    Phil, I think you have a point, this is slightly overkill. I tend to avoid custom coding where possible, you can do this in the text box properties directly (modified to show a black to red continuum for ease of use):

    ="#" & Right("0" & Hex(RGB((Fields!Num.Value/Max(Fields!Num.Value,"dsBounds"))*255,0,0)), 2) & "0000"

    I set my text to white so it would show up when the value was all black, I'm sure this could be further tweaked to make it prettier.

    **Edit - sample crappy report attached

    ---------------------------------------------------------
    How best to post your question[/url]
    How to post performance problems[/url]
    Tally Table:What it is and how it replaces a loop[/url]

    "stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

  • Barry ONeill

    SSC Rookie

    Points: 45

    I have used the code from this website effectively in the past, and felt it worth sharing as, to me, it was a much more convenient approach:

    http://blogs.msdn.com/b/bobmeyers/archive/2009/07/31/add-excel-like-color-scale-conditional-formatting-to-your-reports.aspx

    Also makes reference to this one.

  • rfr.ferrari

    SSCertifiable

    Points: 6879

    Thanks AndyOwl!!!! your article is easy and simple to understand and very useful too!!!


    [font="Times New Roman"]rfr.ferrari[/font]
    DBA - SQL Server 2008
    MCITP | MCTS

    remember is live or suffer twice!
    the period you fastest growing is the most difficult period of your life!
  • dj1202

    SSCommitted

    Points: 1829

    Way too complicated imo. I would simply create a view that joins on a range table with degree values like "high," "medium," "low" and then use boolean expressions to set the background color of the cells depending on these values.

  • nick.ryan

    SSC Rookie

    Points: 42

    Agree totally with your sentiments.

    I also think the author should not apologise for being from the UK. I think that gives him and advantage when it comes to the English language and the correct order of dates. 🙂

  • Robert vd Berg

    SSC Eights!

    Points: 850

    Even more simple: use the expression builder and nested IIf to set the colour directly by name, e.g.:

    =IIf(Fields!Num.Value/Max(Fields!Num.Value,"DSBounds") > 0.25,IIf(Fields!Num.Value/Max(Fields!Num.Value,"DSBounds") > 0.5,"HotPink","Pink"),"White")

    Robert van den Berg

    Freelance DBA
    Author of:

  • OldCursor

    SSC Eights!

    Points: 936

    I agree with SSCrazy. The class and other custom code is wholy unnecessary. It's a simple matter to either generate a hex colour code string in the SQL code, or directly as an expression in the report.

    I'm most in favour of the former - i.e. driving the report from a stored procedure that can dynamically get the upper and lower bounds and calculate a hex string appropriate to each value.

    It's good to know that it is possible (and not too complicated) to create fancy custom code for things that might need it - although I've been developing reports for some years now and haven't yet found a need to do so.

    SSRS is a very capable tool, intuitive and flexible and does not require a lot of learning. And SQL Server Central is just the dogs b**** for getting the support you need!

  • roworthm

    Old Hand

    Points: 313

    I'd agree. It's using a sledgehammer to crack a nut. You already have two technologies that can perform the colour calculation fairly simply: SQL or SSRS. Doesn't need another. On the other hand, it is a good example for integrating a C# assembly into SSRS where required.

    Just wondering, because I couldn't figure it out, does the additional assembly set on the reporting server, or does it need to be deployed to the client machines? Hopefully the former.

  • keith.fearnley

    Mr or Mrs. 500

    Points: 587

    Here's a solution using a background colour expression that relates directly to the example as provided, in case it helps anyone.

    My query for data set is as follows, rather than the assumed simple first query in the author's example:

    SELECT ActorGender, Decade, COUNT(ActorID) AS Num

    FROM tblActor

    GROUP BY ActorGender, Decade

    The table has Decade as the Row group, ActorGender as the Column group and MAX(Num) as the value (has to have an aggregate so almost any would do here).

    This means that the decades/genders with no actors have NULL value, so after creating the table, change the value in the cells from [Max(Num)] to an expression"=VAL(MAX(Num))" to display a zero instead of a blank.

    To colour the cells, use the following in the BackgroundColor property:

    ="#FF" &

    RIGHT("0" &

    HEX(255- 255*

    (IIF(Fields!Num.Value < MIN(Fields!Num.Value, "DataSet1"),

    MIN(Fields!Num.Value, "DataSet1"),

    Fields!Num.Value) - MIN(Fields!Num.Value, "DataSet1")

    ) /

    (MAX(Fields!Num.Value, "DataSet1") - MIN(Fields!Num.Value, "DataSet1"))

    )

    , 2)

    & "FF"

    The "IIF" is to cope with the zeroes, which are less than the MIN value (since MIN doesn't include NULLs). It uses the MIN value if the actual value is less than the min, i.e. if it is NULL. Without this the NULL/zero values would go negative and mess up the HEX output a little.

    The "RIGHT" is to cope with values less than 16 for which HEX returns a single digit, whereas the colour value needs a full set of digits to mean what we want it to mean.

    The "FF" values before and after are the Red and Blue colour values, since we only want to vary the Green value.

    Default names were used for dataset, etc.

    It can be simplified a bit if we make use of real-world data knowledge - i.e. there will never be fewer than zero actors in a given year/gender, but I've tried to match the author's original approach.

  • J-440512

    SSCertifiable

    Points: 6285

    I did learn something from this article - Step 5 - Building and Referencing the DLL.

    Since the example was not overly complex, it was easy to absorb the part I was not familiar with - dealing with DLLs.

    This simple example does have the merit of explaining the concept without having the reader stumbling at each step to figure out an extremely complicated case where using DLLs would be the only possible solution.

    Just like the notorious factorial example to illustrate recursivity - nice simple illustration of a concept BUT don't forget to warn newcomers that that in reality how and why this is the absolute worst approach as opposed to much more efficient methods for such a trivial case.

    In the case of this article, it would have been nice if the author had mentioned that there are simpler ways of handling such a trivial case and giving a hint as to when such an approach would be more suited if not absolutely necessary.

  • happycat59

    One Orange Chip

    Points: 29189

    roworthm (1/2/2015)


    I'd agree. It's using a sledgehammer to crack a nut. You already have two technologies that can perform the colour calculation fairly simply: SQL or SSRS. Doesn't need another. On the other hand, it is a good example for integrating a C# assembly into SSRS where required.

    Just wondering, because I couldn't figure it out, does the additional assembly set on the reporting server, or does it need to be deployed to the client machines? Hopefully the former.

    The assembly needs to be deployed to the SSRS server as well. The only client machine that needs the assembly is the workstation(s) that is being used to develop the reports. People who are simply viewing the report do not need the assembly on their workstation.

  • gordon.feeney

    SSC Enthusiast

    Points: 186

    I think only one person noted that this was a very useful article as it included creating and referencing an external DLL. I'm sure sure some of the other solutions are indeed straightforward but for me as well, that wasn't really the point. Very good article indeed.

    Just a minor point. If you are using a 64-bit o/s then you will need to copy the DLL to C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies.

    Regards,

    YaHozna.

  • RonKyle

    SSC-Dedicated

    Points: 31468

    I cannot figure out why companies would continue to use SSRS. It takes so long to create simple things that, frankly, are much much easier to do with other tools.

    I agree. And like you, I think it's too bad, as I would like to be able to use SSRS more. Having largely mastered (to the extent that's really possible) SSIS and SSAS, if SSRS were easier I'd have all the bases covered. But I have to stick with Excel.

    Good article about incorporating DLLs though.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply