A note from Mark Miller: Oh, I just love it when things work the way they are supposed to. At the end of each EUSP Live Online Workshop, I ask the participants to keep in touch and let me know when they use one of the solutions in the workshop. Trudy Hutzler took the Dynamic Feedback through SharePoint and Sparkline Charts workshop and documented how she utilized a solution for adding images to specific events in a calendar. This is FANTASTIC stuff! Thanks Trudy.

Dear Mark & Christophe

OK, in the interest of full disclosure I am not a programmer/coder.  However, I am in IT and therefore know enough code to make programmers want to bang their head against their desk repeatedly while muttering “Why? Why? Why?” under their breath.

That being said I have something you might want to pass along to the other members of our workshops.  I will include all my code and the image I used, as well as all the steps I took to create the project in case anyone would like to try it themselves, or use other images. 

Since those absolutely wonderful workshops I have been working on a couple of different projects putting what we learned to good use.  However, when we were in the Sparklines workshop and looking at how to use the css-sprites to show the pie charts on a calendar view, someone mentioned in passing it might be a neat way to put pictures on a calendar for holidays.  I have long wanted to find a way to do just that so the idea just sort of stuck with me and wouldn’t leave me alone.  So when I found myself eating lunch at my desk yesterday I thought it might be a good way to entertain myself just to see what I could do. 

So first thing I had to do was find a holiday css-sprite image.  A quick look on Google didn’t come up with anything, but I did find a link to a css-sprite generator from Project Fondue so I decided to create my own sprite.  You can even download the generator and run a local copy if you like.

I picked out clip art pictures I liked for each of the holidays, copied them and put them all together in a folder.  I used Microsoft clip art pictures since they are available for personal and commercial use so I don’t think I am violating any copyrights. 

In order for the code generator work all the pictures you want in your sprite must be placed in a zip folder.  The size of the zip folder can’t exceed .5 MB.  So I edited the pictures in my folder to all be PNG files, set to 60 x 70 pixels while maintaining the image ratio, then compressed them to make them smaller in size.  Once the pictures were ready I put them all in a zip file, and was ready to make my sprite.

Generating the sprite took a little trial and error but all and all was pretty easy once I figured it all out.  I uploaded my zip folder, left the “Image Duplication”, and “Resize Source Images” sections set to defaults, and then changed the horizontal and vertical offsets to 10 pixels to give a 10 pixel space in between the images of the sprite.  You can also change the output to horizontal if you prefer but I stayed with vertical since that was what we had used in the workshop.  Everything else I left at the default values and clicked the “Generate Sprite Image & CSS” button at the bottom.

Not only does this generator take your pictures and create the sprite for you, it also gives you the CSS code to go with it including the position information for each image which I would need later to tell SharePoint where in the sprite to find the image I want displayed.  Also since I left the image ratio/aspect the same when I resized the pictures they were not all exactly 60 x 70 pixels so the position of the height and width of each image varies.  If I ever do it all over again I will try to make them all exactly the same size and my life a little easier on myself.  However having only one hour I was a little pressed for time.

I copied and pasted the code into Notepad and downloaded my new sprite image. 

Here is the code that was generated:

.sprite-holiday-0 { background-position: 0 0; width: 70px; height: 48px; }
.sprite-holiday-1 { background-position: 0 -58px; width: 70px; height: 41px; }
.sprite-holiday-10 { background-position: 0 -109px; width: 70px; height: 57px; }
.sprite-holiday-11 { background-position: 0 -176px; width: 70px; height: 44px; }
.sprite-holiday-12 { background-position: 0 -230px; width: 60px; height: 60px; }
.sprite-holiday-13 { background-position: 0 -300px; width: 60px; height: 70px; }
.sprite-holiday-14 { background-position: 0 -380px; width: 70px; height: 54px; }
.sprite-holiday-15 { background-position: 0 -444px; width: 68px; height: 60px; }
.sprite-holiday-16 { background-position: 0 -514px; width: 60px; height: 61px; }
.sprite-holiday-17 { background-position: 0 -585px; width: 70px; height: 54px; }
.sprite-holiday-2 { background-position: 0 -649px; width: 60px; height: 66px; }
.sprite-holiday-3 { background-position: 0 -725px; width: 70px; height: 11px; }
.sprite-holiday-4 { background-position: 0 -746px; width: 70px; height: 56px; }
.sprite-holiday-5 { background-position: 0 -812px; width: 70px; height: 18px; }
.sprite-holiday-6 { background-position: 0 -840px; width: 62px; height: 60px; }
.sprite-holiday-7 { background-position: 0 -910px; width: 60px; height: 61px; }
.sprite-holiday-8 { background-position: 0 -981px; width: 48px; height: 70px; }
.sprite-holiday-9 { background-position: 0 -1061px; width: 70px; height: 57px; }

Next I added a calendar to blank SharePoint site I have set up for testing and development.  Then I added a picture library to the site and uploaded my new css-sprite image to it.

I opened my new calendar and added a few events to it for testing later. 

Then while on the calendar page I went to “Settings” à”List Settings”

Once there I created a “Choice” column which I named “Holiday”.

I added the list of Holidays in the choices list box as follows:
(1) New Years Day
(2) MLK Day
(3) Valentine’s Day
(4) President’s Day
(5) Easter
(6) Mother’s Day
(7) Memorial Day
(8) Father’s Day
(9) July 4th
(10) Labor Day
(11) Columbus Day
(12) Halloween
(13) Election Day
(14) Veteran’s Day
(15) Thanksgiving Day
(16) Christmas Eve
(17) Christmas Day
(18) New Years Eve

Now I had my list of holidays, next I needed a way to tell SharePoint which image to display for each holiday.  After looking at the examples from the workshops I created another calculated column called “Icon”.  The formula in Christophe’s “Pie 50” calculated column is as follows:

="<span style='display:inline-block;height:30px;width:50px;background-image:url(""/test/sparkline/Images1/PieSprite50.png"");
background-position:0px -"&(10+60*INT([% Complete]*100/5))&"px;'>"&Title&"</span>"

The first line tells where to find the sprite image, and the second line tells what the position of the image to be displayed is in the sprite.  Since all the pie chart images were exactly the same size Christophe used a calculation to set the position.  Since my images weren’t all the same this was going to be a little tricky.  Hind sight is always 20/20.

So next I looked at the code from the generator:
.sprite-holiday-0 { background-position: 0 0; width: 70px; height: 48px; }

When I compared the two I saw that they both had “background-position:0px –“ in common, and in the css-sprite code the exact position for each image was given.  So I would need to replace the calculation that Christophe used with the hard coded position, but that would mean I would need essentially a line of code for each holiday.  I would need to be able to say if “Holiday” is set to “New Year Day” then the position is XYZ, if it is set to “Martin Luther King Day”, then the position in ABC, and so on.  So I wiped up a handy dandy IF statement that went something like this:

=IF([Holiday]=” (1) New Years Day”, “0; width: 70px; height: 48px; }”)

Which would leave me with the string “0; width: 70px; height: 48px; }” for New Years Day.  To see if this worked I added a new calculated column called “Pic Text”, and  I put my IF statement in the calculation box.   Next I added the column to the “All Events” view and then clicked OK.  This is what I got.

Now that I knew that my IF statement was working I added a few more IF statements so I could test how it would work with multiple/nested IF statements.  I went back to “List Settings”, clicked on the “Pic Text” column to edit it and added 5 more IF statements to the calculation.  The calculation for “Pic Text” now looked like this:

=IF(Holiday="(1) New Years Day","0; width: 70px; height: 48px; }",
IF(Holiday="(2) MLK Day","-58px; width: 70px; height: 41px; }",
IF(Holiday="(3) Valentine’s Day","-649px; width: 60px; height: 66px; }",
IF(Holiday="(4) President’s Day","-725px; width: 70px; height: 11px; }",
IF(Holiday="(5) Easter","-746px; width: 70px; height: 56px; }",
IF(Holiday="(6) Mother’s Day","-812px; width: 70px; height: 18px; }",""))))))

After clicking OK I went back to view the Calendar, changed the view to “All Events” and this is what I got:

Great all is working well! 

Now I needed to feed position text from the “Pic Text” column into the formula in the “Icon” column.  I went back to the “List Settings” page and clicked on the “Icon” column to edit it.  I took out everything between the two “&” and replaced it with [Pic Text].  So now my formula looked like this:

"<span style='display:inline-block;height:70px;width:70px;background-image:url(""/test/Slider%20Images/HolidaySprite.png"");
background-position:0 "&[pic text]&"px;'>"&Title&"</span>"

So now it was time for the BIG test. It was time to see if I could actually get a picture on my calendar.   I opened up my calendar in “Calendar” view.   This is what I saw:

No pictures just the html code I had added.  I still needed to convert the code into actual HTML. 

So I went to “Site Actions” à”Edit Page”, clicked on “Add A Web Part”, and added the HTML Calculated Column web art we got in the workshops. 

I click on “Exit Edit Mode” and . . .

SUCCESS!!!!!!!  Houston we have a picture!

It is now 10 minute till the end of my lunch hour and so I merrily go about adding the IF statements for the rest of the Holidays when. . . Houston we have a problem.   I have 18 holidays, and you can only use 7 nested if statements.  Now what?  I am running out of time fast.

OK you can only have 7 IF statements but you can have 29 choices so I quickly looked up the syntax for the choice statement but no matter what I tried I couldn’t get it quite work.  So now that I am 5 minutes over my lunch hour, but too close to total success to quit now I decided to do something that is a bit of a hack.  I added 2 more calculated column which I called “Pic Text 2” and “Pic Text 3”.  I divided the 18 holidays into three groups of 6 and created nested IF statements for each group, one for each “Pic Text” field.  Added the two new columns to my “All Events” and view, opened the calendar, switched to the “All Events” view and made sure everything was working.

This is what I got.

I now needed to be able to pull the position information from whichever of the three “Pic Text” columns had the information into the formula in the “Icon” column.  To make this quick and easy, since I was now 15 over my lunch break, I created a final calculated column which I named “Pic Position”.  I added this following formula to the calculation box:

=IF([pic text]<>"",([pic text]),IF([pic text 2]<>"",([pic text 2]),IF([pic text 3]<>"",([pic text 3]),"")))

I then changed the formula in the “Icon” column to pull the position information from “Pic Position” instead of “Pic Text”.  The new formula looked like this:

"<span style='display:inline-block;height:70px;width:70px;background-image:url(""/test/Slider%20Images/HolidaySprite.png"");
background-position:0 "&[pic position]&"px;'>"&Title&"</span>"

I saved everything, opened up the calendar added events each quarter of the year to test and . . .

We are in business once again!

And I was finally able to get to work.

Later I decided to add this functionality to our company calendar.  We already had all the holidays entered on the calendar so it only took a few quick copy and pastes, and adding the HTML Calc Column to the page and we were in business, but I did notice one last issue.

For events that are not holidays SharePoint didn’t know what to display, so it displayed all of the first image on the sprite, and part of the second.  Not to worry though a quick addition to the “Icon” formula solved the problem.

I added the following to the formula before everything else:

=IF([pic position]="","<span style='display:inline-block;height:20px;width:20px;"&[pic text]&"px;'>"&Title&"</span>",

So the final version of the “Icon” formula is:

=IF([pic position]="","<span style='display:inline-block;height:20px;width:20px;"&[pic text]&"px;'>"&Title&"</span>",
"<span style='display:inline-block;height:70px;width:70px;background-image:url(""/test/Slider%20Images/HolidaySprite.png"");background-position:0 "&[pic position]&"px;'>"&Title&"</span>")

You had asked to let you know how we use what we learned in the workshop, and although I have two more business like projects that are nearing completion, everyone seemed to like the idea of pictures on the calendar so I thought maybe you could pass this on to the others, or anyone else you think might be interested.  I will attach text files with my formulas, and a copy of the sprite to make it easier on everyone else.

If I get any more free lunches I would like to see if I could get the CHOOSE statement to work, and to either move the image to the right , or below the text so the text isn’t right over top of the picture.  Or if all else fails to change the color of the text, but there is only so much you can do in one lunch hour. 

If anyone comes up with a better way, or any of the above fixes, I would appreciate if you could pass them along to me.  Let me know if you have any question.

Trudy

Guest Author: Trudy Hutzler

Trudy Hutzler is an IT Professional/Systems Administrator based in Ohio.  Trudy has been administering and developing SharePoint since 2007 when her boss showed Trudy a Team site and told her to “Have fun.”  Originally in the IT department, in addition to SharePoint Trudy also administers, and helps maintain many of the systems on her company network.