Dunro.net

Welcome to Dunro.net, where the goal is to create efficiency by using technology effectively. This blog will be offering tips, tricks, and the occasional rant intended to help you become more efficient in a typical office environment. It will focus not only on the how, but the why. Click here to learn about Dunro.

[the-greatest-genderqueer]

"You’re the straightest gay guy I kn-"

Don’t.

[outoftime2025-deactivated202511]

"You’re not like those other gay people who go to those awful pride para-"

No.

[estrogene-deactivated20171022]

"You’re gay? Then why don’t you hit on me or other gu-"

No.

[dunro]

Pivot Charts and Color Schemes

Pivot Charts with more than 6 series will end up repeating colors (albeit in different shades) due to the way the Office 2007/2010 Themes work

If managers/clients do not like colors repeating, you have 4 options:

Do not use a PivotTable / PivotChart

Reduce # of series

Manually change colors (but moment pivot changes or refreshes, this gets overwritten)

Include a VBA Macro (Under Chart_Calculate) that resets the series colors  (ActiveChart. .SeriesCollection(<Index>).Interior.ColorIndex = <ColorIndex>)

Useful related links:

Set PivotChart Colors using VBA (     http://www.ozgrid.com/forum/showthread.php?t=61262)

Change Theme Colors (http://office.microsoft.com/en-ca/excel-help/apply-customize-and-save-a-document-theme-in-word-or-excel-HA010354866.aspx)

[dunro]

SQL Challenge: Avoiding Duplicates

For arguments sake, this is within a large-scale enterprise environment...and this is not proper SQL.  We need to run this query very rarely, so we're willing to accept that it's a bit slow.  We want the latest end date for a given jo... and assume that noone was gone out and back into a job... only extended.

Given tables:

Person (pkey EEID), PersonJobAssignment (pkey AssignmentID, EEID, StartDate, EndDate, JobID)

Query:

select Person.EEID, PJA2.StartDate as LastContractStart, JobSumm0.LastEnd as LastContractEnd from Person left join (Select max(EndDate) as LastEnd, max(assignmentID) as LatestAssignment, JobID, EEID from PersonJobAssignment as PJA0 group by EEID, JobID)  AS JobSumm0 ON JobSumm0.EEID = Person.EEID LEFT JOIN (select assignmentID, StartDate, EndDate, JobID from PersonJobAssignment) AS PJA2 ON PJA2.AssignmentID = JobSumm0.LatestAssignment

This gives us [Main Table]->[Helper Aggregate Query to find correct fkey based on highest fkey on latest date]->[Second Table]

[dancing--in--the--moonlight-dea]

[dunro]

Lance Armstrong’s thoughts on pain…

Great quote.  I think "pain" can also be expanded to include "pain" for the business.

[dunro]

Key principle #1: Data entry should occur once, and only as a last resort.

Data entry is one of the most tedious tasks out there, especially if you are not a fast typist.  Often organizations collect the same data from multiple places and enter this data into completely separate files.

There are many alternatives to data entry:

1. Electronic Data Submission*

Rather than have your clients submit a piece of paper, have them submit data online.  Even if it is not feasible for them to access your database directly, receiving data electronically provides options such as data import by file, use of macros, and the old stand-by cut and paste.

Similarly, consider requesting electronic copies of reports from your partners/clients.  Not only will you save time re-entering data, you will also save trees.

There may be legal implications to changing your process.  Do not let fear of the unknown scare you away.  Your organization likely uses electronic communication already and has policies in place which can be used to protect you.

*For the purposes of this blog entry, a FAX is not considered an electronic data submission as the data still needs to be converted from an image into text.

2. Optical Character Recognition

Many photocopiers offer the ability to scan documents to a file.  (Failing that, any old scanner will do.)  Once you scan the document, you can use software on the computer to convert the image into text.  Microsoft Office comes with this functionality already included.

3. Use a similar document that already exists

There may already be a document with the data available that is used for other purposes.  This document can then be copied and modified to meet your specific needs.  If the data is stored in a central repository, then corrections also only need to be made once.

Avoiding unnecessary data entry will free resources up to manipulate the data.  It will also reduce potential error due to typos, misread data, etc.  

What are your thoughts?  Are there any other ways to reduce the burden of data entry?  Do you disagree with any of the points presented?  Let me know in the comments!

[dunro]

Key principles of using technology effectively:

Data entry should occur once, and only as a last resort.

The software you already own can do more than you think.

The hardware you already own can do more than you think.

And the catch all, You don’t know what you don’t know. 

[dunro]

HR Post: Being a "People Person" vs. "Evil Corporate Sell-Out"

Despite being in HR, I don't consider myself a "people person." I did not go into HR to help people. I agree with the common saying that if you want to help people go into social science. That may sound cold and heartless, but please bear with me for a couple more paragraphs.

I think HR’s job is to help the business attract, develop, and keep the best talent. Results should be measured against business measures. Like it or not, we don’t pay people out of the goodness of our hearts, we pay them because we can’t replace them with something/someone cheaper.

Cheaper does not solely mean "dollars and cents" although most resources eventually fall into dollars and cents. How will keeping this resource affect our processes? How will losing this resource affect our processes? How will our impact on the community affect our long-term bottom line? None of these questions are meant to say that we can take advantage or over exert one resource. Look at our environment and how neglecting it for years has resulted in larger pain down the road.

Therefore, while I am not a people person, I do believe that helping people brings value to the organization. The difference is my job is to "help the business."  Often I will help people in the course of doing so, but that is not my ultimate goal.

I wrote this post in response to Chris Haskins’ post on The ‘People Person’ Problem. Please check out his blog and share your thoughts.

As an aside, the biggest reason I love HR is because we are able to help managers be better managers, so long as they allow us to do so :)

[dunro]

My computer no longer has hard drives dangling out the side!

Well, it took quite a bit of finagling(sp?), but I managed to get all 3 of my hard drives into my tower.  (This is fate mocking me for buying an HP...)

The challenges: 1 drive is IDE (PATA?), and needs to be on the same IDE cable as the DVD-RW.  Only 2 3.5" slots in the case.  Cables too short.

The solution: Bought 5.25" Hot Swap Drive bay when it was on sale at Dell.ca.  Overkill, I know... but I figure I can always use it in my next build.  Put my "slower" media drive in it, and locked it closed to prevent accidental swapping  Moved DVD-RW to reduce distance between it and IDE drive (but this may not have been necessary in the end).

I thought I was screwed as the SATA power feed (one feed with 2 connections) was too short to reach both the hot swap and my other drive.  I was willing to sacrifice my other drive (it's just my XP installation, and I'm more than happy with Win 7), but at the last minute I saw a 2nd SATA power feed taped (or simply caught on) to the far side of the case.

So I now have all my drives (including ahem my OS drive) inside my computer! *gasp*

Now I need to either get VLC to play these Australian DVDs I got for Christmas (for some reason it won't), or hookup the less reliable DVD player and change the region code on it. VLC seems to be co-operating... so now I can watch these DVDs!

[dunro]

Win 7 Migration Status: So far, so good.

For Christmas, I was rather surprised (delighted! :D) to find that my brother/sister-in-law/niece bought me a copy of Windows 7 Home Premium Upgrade.

This was good timing, as the illegal copy of Vista Professional 32-bit that came with my used computer had finally started to get a little noisy about the fact it wasn't genuine.  So I then decided to downgrade to XP in November as I own 3 genuine copies of XP Professional, none of them were in use, and I was at a point where I could deal with a reformat at the time.

"Vista sucks, go with Windows XP. " / "Why go to Windows 7?"

Now I know for some, going to XP from Vista is an upgrade.  I can see your point, as some items didn't work in Vista -- ie. my TV tuner from before the Year 2000, the USB drivers for my cell phone (supposedly - never worked with the drivers), etc.

But Vista came with my machine, and I quickly adapted to Vista and its quirks (good and bad).  Going down to XP was a bit of a shock from a visual standpoint.  I also was bothered because I hadn't wanted to change my OS until I had a 64-bit option.

(Granted, at this point, I'm not sure if 32- and 64-bit versions of software make a HUGE difference, but... I bought a 64-bit processor, so why am I not using a 64-bit OS?)

Microsoft has started to ship both 32-bit and 64-bit installation CDs together for Windows 7 (and will be doing so for Microsoft Office 2010) so that along with regaining the parts of Vista I liked was what put Windows 7 on my radar.

So far, Windows 7 64-bit and my computer get along well.  The visual asthetics are the smoothest I've had on any of my computers.

Dual-boot vs. Virtualization

As some of you may know, Windows 7 offers a "Windows XP Mode" to run "productivity applications." (Read: legacy software that businesses can't/don't want to upgrade.)

This XP Mode is only available with Windows 7 Professional, but also requires running Windows Virtual PC.  Aside from possible licensing advantages (depending on how XP Mode works), I didn't really see much advantage to this.  It likely would require more RAM, and not be a true emulation of XP.  Besides which, I'm very unlikely to need this as I was using Vista for a year prior to my brief period of returning to XP.  This is why Win 7 Home Premium was on my wishlist even though I would never dream of using XP Home.

However, as I have extra hard drives and extra licenses, I just have chosen to keep my XP installation intact.

"Musical Chairs" for Hard Drives

Although none of my hardware gave me issues, I did however have a bit of a unique challenge which I have not quite figured out yet.

My computer can only hold two 3.5" drives, preferably SATA drives (although I have an adapter from IDE to SATA).  I am currently using 3:

Windows XP is running on the 80gb SATA drive that came with the machine.

I have a 1.5 TB drive partitioned into 500gb partitions which I use for media storage.  This drive is slower than the other two however.

Windows 7 is running on a 300gb refurbished IDE drive (partitioned into 2 drives: one is 80gb, and the other is the balance).  This drive is currently dangling precariously out of the case because

As mentioned, no space for a 3rd drive

none of my IDE cables are long enough to reach both the hard drive and the CD drive

There is only one IDE connection on the motherboard

I can certainly (and should) use the SATA adapter I have -- plus the SATA cable/chasis space from the XP drive, however I have the sinking feeling this drive is liable to fail on me... it seems to make "waking up" noises at strange times. If it does indeed fail, my fall back is to install Win 7 on the former-XP drive and install Win XP on the slower storage drive, however this involves doing 2 clean installs... which would be 4 clean installs in a span of 2 months. Ick.

Dual booting is being managed by the BIOS.  I dislike messing with MBRs, so if I don't need a boot loader, why bother?

Things that make me shake my head

So far, the only software that doesn't seem to mesh well with the new Windows 7 interface is, amusingly, Windows Live Messenger (WLM).

Windows 7 is trying to get away from dependence on the right-hand side of the task bar.  All items on the task bar now are icons-only (unless you fiddle with settings).  This is kinda nice in theory, but WLM messes it up -- especially with Messenger Plus installed.  You still need to see the icon on the right-hand side to see your current status.

With enough fiddling I was able to eliminate the problem (at least getting it to behave the way it does in previous versions of Windows), but you would think Microsoft would be making its software in a way that it takes advantage of their own "features."

I'm also having some issues with watching Flash videos in Chrome and Firefox (but not IE), but those seem to be occurring in XP too.  Grrr @ Adobe.

Overall

I'm happy with Windows 7, although as you read I still need to resolve a few issues before I "move in" permanently.  Thank you to the various bloggers out there who along with Google helped me resolve some of my issues. :)

Next steps: install more software (MS Office 2007 or 2010, and the GIMP)

[dunro]

Don't you hate it when you want to tweet more than 140 characters?

I've decided to give tumblr a try.  I need another blog like a hole in the head, but on the other hand I've wanted to write some long-ish entries on twitter.  Sure, I could link to my journal... but I like having Twitter separated (reasonably) from the other facets of my online life.  This way I can continue to interact with strangers (professional contacts ;)).