August 30, 2012 at 12:00 am
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].
August 30, 2012 at 5:26 am
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.
August 30, 2012 at 5:33 am
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."
August 30, 2012 at 6:11 am
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:
Also makes reference to this one.
August 31, 2012 at 9:31 am
Thanks AndyOwl!!!! your article is easy and simple to understand and very useful too!!!
August 31, 2012 at 1:17 pm
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.
September 5, 2012 at 12:20 am
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. 🙂
September 12, 2012 at 8:58 am
October 24, 2012 at 3:01 am
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!
January 2, 2015 at 4:18 am
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.
January 2, 2015 at 6:56 am
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.
January 3, 2015 at 9:27 am
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.
January 4, 2015 at 7:32 pm
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.
January 5, 2015 at 9:09 am
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.
January 5, 2015 at 9:44 am
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 15 total)
You must be logged in to reply to this topic. Login to reply