June 27, 2018 at 9:59 am
I am a lone, and originally accidental, DBA, so would very much appreciate feedback on a solution I have come up with. I have no peers here to bounce ideas off. Among my many duties are creating SSRS reports, lots of them.
I was asked to create a report to replace a very inefficient management report, manually updated in Excel. At its heart it's a very simple one, jobs assigned to particular users with one month per sheet.
All the data was readily available from the database of one our core systems. Easy.
The one thing they were particularly attached to was the cell colour highlighting for users. I thought of using the usual switch statement in the highlight colour expression, but soon realised this would need constant maintenance as users came and went. I needed an elegant way to produce random colours for a variable number of users.
The user table has a userID INT column so, this was my approach. Thanks to Jeff Moden for the method of quickly populating the table which can also double as a tally table.
/*Create a simple 250000 row "tally table" extended to assign random hex colour to each value */
SELECT
TOP 250000
IDENTITY(INT,1,1) AS N
, '#' + CONVERT(VARCHAR(10), CRYPT_GEN_RANDOM(3), 2) AS Colour
INTO udTallyColour
FROM
master.dbo.syscolumns AS s
, master.sys.syscolumns AS s2
GO
ALTER TABLE dbo.udTallyColour
ALTER COLUMN Colour NVARCHAR(10)
GO
ALTER TABLE dbo.udTallyColour
ADD CONSTRAINT PK_udTallyColour_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
GO
Then in the query behind the report, I just join the tally table to the user table on userID = N and use the column Colour in the background Colour expression.
It works a treat, and seems to perform well, but is a relatively small set. Can anyone pick any large holes in this?
I did originally use a table variable, which also performed well but obviously the colours changed every report run. I thought this way would give a little more consistency.
Thanks for reading
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?
June 27, 2018 at 10:38 am
The main problem I can see is that there's no guarantee that you'll have humanly distinctive colors for each user.
If you get #EE1010 and #EE1012, it would be really hard to distinguish which is which. If you don't have a problem with that, it might be ok.
June 27, 2018 at 12:10 pm
find a max number of users maybe, then go to your color wheel and plot an array of however many you come up with (say 20, so 4 x 5, just click your mouse where you want it, write down the values). stick that in a table or CTE, pull it out as necessary, repeat if you get to the end
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
June 28, 2018 at 4:32 am
Luis Cazares - Wednesday, June 27, 2018 10:38 AMThe main problem I can see is that there's no guarantee that you'll have humanly distinctive colors for each user.
If you get #EE1010 and #EE1012, it would be really hard to distinguish which is which. If you don't have a problem with that, it might be ok.
Yes, thanks. I had considered that, I didn't want my post to be too long so left some thinking out. When using the table variable, obviously it's random every time, and sometimes hard to read/distinguish for the reason you mention. This way, if necessary, certain numbers can be set by just updating one record once.
Jonathan: thanks, we have 550 users, so didn't want anything manual. I also wanted to be able to potentially use this method in the future for other items with a much larger spread (clients for example) hence the 250,000 records. Populating randomly at the outset seemed the most time-effective compromise.
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?
June 28, 2018 at 7:29 am
This might give you an idea on how to control the values a little bit more.
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
E7(n) AS(
SELECT a.n FROM E4 a, E2 b, E2 c
),
cteTally(n) AS(
SELECT TOP(372827) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n * 45
FROM E7
)
SELECT '#' + CONVERT(VARCHAR(10), CAST(n AS BINARY(3)), 2) AS Colour
FROM cteTally
ORDER BY NEWID()
June 28, 2018 at 8:15 am
curious then how useful 550 different shades of colors are really going to be? Let alone 250000. And god forbid someone have any variation of color blindness, and not be able to tell the difference between half your colors.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
June 28, 2018 at 8:52 am
Thanks Luis, that looks elegant and gives me some additional options I think.
I will have a fiddle.
Jonathan, I would use this highlighting with caution and where appropriate: in this report there will only ever be a small subset (~15-20) - I am automating a manual report that they manually highlight now remember.The point of my approach is to prevent ever having to manually assign colours to new members of the team that use it etc by changing code. It just means that all users (or clients etc in other future reports) can have a colour assigned to them simply by joining to this table.
If there is ever a case where more than a sensible number of entities require highlighting I would advise the requestor against it.
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?
June 28, 2018 at 12:16 pm
if there will only ever be a small subset, then I guess that's what I'm suggesting. Instead of assigning a specific color and potentially having two users with very similar colors, why not just pick 20 colors to use, and rotate through them when you populate the report? Every user will be different, and the colors will be different enough (hopefully) to make it visible.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
July 1, 2018 at 5:27 am
Heh... Just a random thought... With no reflection on the OP, even as few as 20 colors is going to add no value to the report. 1 color for each of 550 users is certainly a waste of clock cycles and programming effort. I'm surprised that this is a requirement and one of the many reasons why I gave up on the front-end/display worlds 15 years ago.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 2, 2018 at 8:20 am
Jeff Moden - Sunday, July 1, 2018 5:27 AMHeh... Just a random thought... With no reflection on the OP, even as few as 20 colors is going to add no value to the report. 1 color for each of 550 users is certainly a waste of clock cycles and programming effort. I'm surprised that this is a requirement and one of the many reasons why I gave up on the front-end/display worlds 15 years ago.
No slight taken Jeff 🙂 - I had a one line "spec", the highlighting being the most important part to the requestor. I just wanted a way to make sure that any of the users (at present there are only between 5 and 8 distinct users on each page) would have a unique colour assigned to them, and that it would /always/ be the same colour for each person. Also trying to think ahead so that if(when) the next similar request came through the person would have the same colour assigned, for consistency.
I was trying to take a leaf out of your book and make a low cost, set based result, but which included a distinct colour, which is why I extended the tally table principle to save "creating" the colours each run. The team membership is small but fluid. I guess I posted here to get a sanity check on query performance more than anything else, but the other responses have all been helpful and appreciated.
if there will only ever be a small subset, then I guess that's what I'm suggesting. Instead of assigning a specific color and potentially having two users with very similar colors, why not just pick 20 colors to use, and rotate through them when you populate the report? Every user will be different, and the colors will be different enough (hopefully) to make it visible.
Jonathan, I can see where you are coming from, but I couldn't think of a way to ensure a particular user would always get the same colour, without a lot of code, especially if this gets extended to other reports.
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?
July 3, 2018 at 12:41 pm
Wow... I just wanted to post to say "I feel your pain" to the OP (david.edwards). Sometimes we just have these absolutely ridiculous requirements that we can do little/nothing about.
I do have to ask, along the lines of Jonathan's thinking -- a static set of colors -- If part of your "requirements" is that the same user gets the same color on all reports throughout the system (or at least a set of appropriate ones), why not store them in a permanent table, as an association of UserID to Color? You could have a triggered/scheduled process that generates a new random color each time a new User is added. And whatever other async maintenance you need it to do (delete records for deleted users, ensure distinct-ness of colors, whatnot). Just a thought. Good luck, and hang in there!
-Nate the DBA natethedba.com
July 4, 2018 at 2:44 am
Nate the DBA - Tuesday, July 3, 2018 12:41 PMWow... I just wanted to post to say "I feel your pain" to the OP (david.edwards). Sometimes we just have these absolutely ridiculous requirements that we can do little/nothing about.I do have to ask, along the lines of Jonathan's thinking -- a static set of colors -- If part of your "requirements" is that the same user gets the same color on all reports throughout the system (or at least a set of appropriate ones), why not store them in a permanent table, as an association of UserID to Color? You could have a triggered/scheduled process that generates a new random color each time a new User is added. And whatever other async maintenance you need it to do (delete records for deleted users, ensure distinct-ness of colors, whatnot). Just a thought. Good luck, and hang in there!
Thanks Nate. I did consider that first, we have a lot of additional user info already after all. My thinking was that I would try to future-proof it a little and have that permanent table big enough to be able to join to any of the important entities in this particular database, without having to re-invent the wheel when the next request came through. This (third-party-supplied) database uses int IDs for all tables regardless of whether there are natural keys, so joining to any of them would not be a problem. Although randomly generated, and large, this is now static after all.
I didn't want anything that would require maintenance, just wanted a store of random colours generated once that I could forget about, but utilise at any time, for any purpose.
(This is actually one of the most simple requests I have got recently 🙂 )
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply