Wednesday, April 19, 2017

First Steps into Workplace by Facebook

"Imagine a world where people wake up every day inspired to go to work, feel safe while they are there, and return home at the end of the day feeling fulfilled by the work they do, feeling that they have contributed to something greater than themselves."  That is the goal behind Simon Sinek's program to help organizations discover their Why Story - their purpose, cause, belief, and very reason they exist.

Several months ago we began an organization-wide journey to define our why, to help clarify our direction and inspire our constituents. To involve our staff in this initiative we devised a #MyBestDay employee engagement campaign where staff would be asked to share videos telling the story of the work day that they remember most fondly.  

To pull off the engagement campaign we needed to find the right technology match.  After evaluating some public tools and content management systems we came to the conclusion that a private enterprise social networking platform (#ESN) would be the best fit for our needs.  To narrow things down a bit further, while there are lots of tools out there that will allow users to easily share photos and micro-blog (posts), its a lot harder to find a tool that provides functionality to create and upload videos (live and pre-recorded) which was central to our concept.  

The final requirements?  there was no budget, we needed a tool that could be launched within a few weeks, it needed to have a mobile app (not just responsive), and it had to be incredibly intuitive for non-techie users to be able to utilize. There are a few strong candidates that could have done the job for us, but after a day of testing Workplace (by Facebook) we knew we had a winner, and they were in our budget - free for non-profits! 

Pre Launch
Our project team had a few calls with a Workplace implementation team who answered all of our functional and technical questions.  In addition to providing instructions to configure the technical components such as user provisioning (we use SAML), security roles, and integration capabilities, they also provided communication templates to help with the launch.  Getting our environment up and running took a day at most and configuring the SAML was another day.

With an environment in place we held a five day pilot for around 30 early-adopters who had been identified based on engagement on specific content posted to our intranet.  These folks were provided some training by our Workplace partners, and tasked with pre-loading their #MyBestDay videos, and sharing their personal #MyWhy stories.

Launch 
Our launch date was selected to coincide with a monthly "Open Door Session", which is essentially a staff town hall style meeting.  At the end of the meeting our president announced the launch of our Workplace environment and asked staff to login for a special live video announcement.  The live video was a motivational kickoff by our Chief Transformation & Strategy Officer who introduced the #MyBestDay campaign and how it tied into the organization's why story journey and other transformational projects underway.

Adoption
The launch invites were sent to all 1,000 staff members on April 17th.
TermAccounts claimedGroupsPostsComments
Day 141%2727940
Week 170%91751516

We are coming into our annual March for Babies fundraising season in which we hold hundreds of events to support our mission.  During this period we will encourage staff to utilize the Workplace platform to share videos and photos of events and activities from their mobile devices - which will be both fun and inspiring for colleagues at the same time.

Coexistence with Intranet
Over the years I've seen several conference presentations by companies that utilize separate ESN and intranet platforms and I've always been struck by the disconnect between social and content.  Most of those presentations were about the struggle with having separate platforms and sounded more like warnings than encouragement.  I still hold those concerns with most ESN platforms where there is a constant struggle to grow adoption and engagement, but Workplace is different because of how easy it is to use and how comfortable most people are with the technology, the app, and the idea that everything "Facebook" is leading edge.

At this time we are keeping our intranet and plan to have it coexist with our Workplace environment.  When our employees open a web browser on our domain, or in our remote desktop environment, their default homepage is our intranet.  On our homepage we've added a shortcut to launch the Workplace site for those that have forgotten the URL.  Adding an embedded widget to our intranet homepage to display a Workplace feed is high on my list of things to develop.  Unfortunately Workplace doesn't offer this, and was slightly discouraging about the concept of building such an interface as they advocate using Workplace as the center of the ecosystem. For custom development like this there is an API available. Development vendors that specialize in the Facebook API provided me unusually high estimates for the development of such a feed.  With my background as a programmer I'm on the fence about whether to just write this myself or see if I can find an intern to help to spend a few days on it.

Conclusion
Workplace began their beta program over a year ago which we investigated but chose not to participate in.  The public launch happened in October of 2016. The Workplace user experience is nearly identical to the public experience with a few small twists.  A few things of note:
  • Like the public social network it has a core app and a secondary messenger app
  • There is no communication between public Facebook and Workplace with the exception of being able to tag locations and businesses.  There is no user to user connection.
  • Users can't post on each others walls, however they can post to their own wall
  • With the exception of posting on your own wall, in order to post you must first identify, or be on a particular "group"
  • Groups have 3 levels of permission: open, closed, secret.  
    • This has led to a bit of confusion among our staff
  • A multi-company group can be setup for circumstances when two or more Workplace customers want to collaborate together.  FWIW - I'm in a Workplace sponsored non-profit group that has 183 organizations participating
  • Analytics are on the lean side, with seemingly no way at this time to integrate Google Analytics, nor to do point in time reporting, and analytic data export is also fairly limited
  • At the April 18th F8 conference a slew of new capabilities were announced which included a few content integrations.  At this stage the content integration focus seems to be on "rich previews" which look interesting.  The interface seems to capture each page of a file (Word, PPT, PDF) as an image and give users the capability to review and comment just like any other image.
I'm a bug magnet and am happy to say that after a few weeks on the system I've only found 3 bugs which are all pretty trivial - so far so good:)

Opinions are my own and not the views of my employer.


Monday, August 8, 2016

Resizing and Positioning iframe Content

For years I mistakenly assumed that iframes were unsophisticated tags which didn't provide much room for customization.  Recently I learned about how to use CSS3 2D Transforms to scale and position content within an iframe.

For the sake of illustration, in the steps below I will scale and position the March of Dimes homepage to isolate the banner graphic showing #ZapZika into a 100x100 box.

Step 1: Nothing to see here, just the ordinary page

.wrap {
  width: 1280px; height: 1024px;
  padding: 0;
  overflow: hidden;
}
.frame {
  width: 1280px; height: 1024px;
  border: 0;
}

 

Step 2: Scale the target page to 25%

 .wrap {
  width: 1280px; height: 1024px;
  padding: 0;
  overflow: hidden;
}
.frame {
  width: 1280px; height: 1024px;
  border: 0;
 
  -ms-transform: scale(0.25);
  -moz-transform: scale(0.25);
  -o-transform: scale(0.25);
  -webkit-transform: scale(0.25);
  transform: scale(0.25);
}


 

Step 3: Crop the outer container to the final dimensions: 100x100

.wrap {
  width: 100px; height: 100px;
  padding: 0;
  overflow: hidden;
}
.frame {
  width: 1280px; height: 1024px;
  border: 0;
 
  -ms-transform: scale(0.25);
  -moz-transform: scale(0.25);
  -o-transform: scale(0.25);
  -webkit-transform: scale(0.25);
  transform: scale(0.25);
 
  -ms-transform-origin: 0 0;
  -moz-transform-origin: 0 0;
  -o-transform-origin: 0 0;
  -webkit-transform-origin: 0 0;
  transform-origin: 0 0;
}


Step 4: Position the target page

.wrap {
  width: 100px; height: 100px;
  padding: 0;
  overflow: hidden;
}
.frame {
  width: 1280px; height: 1024px;
  border: 0;
 
  -ms-transform: scale(0.25);
  -moz-transform: scale(0.25);
  -o-transform: scale(0.25);
  -webkit-transform: scale(0.25);
  transform: scale(0.25);
 
  -ms-transform-origin: 0 0;
  -moz-transform-origin: 0 0;
  -o-transform-origin: 0 0;
  -webkit-transform-origin: 0 0;
  transform-origin: -260px -90px;
}





Voila!


A great place to practice this transform capability is: http://jsfiddle.net/yy8yb/609/.
Below is an example of the full technique to implement the frame using CSS classes.
.wrap {
    width: 253px;  /* used to crop the outer container */
    height: 192px;
    padding: 0;  /* pad the outer container */
    overflow: hidden;
}

.frame {
    width: 1024px; /* resolution of iframe target page */
    height: 768px;
    border: 0;

    /* Use the scale elements to scale target page */
    -ms-transform: scale(0.25); /* IE */
    -moz-transform: scale(0.25); /* Firefox */
    -o-transform: scale(0.25); /* Opera */
    -webkit-transform: scale(0.25); /* Chrome, Safari, Opera */
    transform: scale(0.25);

    /* Use the origin elements to position the target page */
    -ms-transform-origin: 0 0;
    -moz-transform-origin: 0 0;
    -o-transform-origin: 0 0;
    -webkit-transform-origin: 0 0;
    transform-origin: 0 0;
}

<div class="wrap">
    <iframe class="frame" src=http://marchofdimes.org></iframe>
</div>

Thursday, November 5, 2015

Intranet Satisfaction Survey

In August of 2015 March of Dimes formed an intranet champions group to identify areas for improvement.  The group consisted of 3 staff members from our HQ and 8 from our "field" (staff NOT in the HQ).  After several brainstorming sessions it was decided that a user survey would be helpful in validating identified changes, and to bolster the business case for budget to design & implement those changes (apart from Interact support costs and staff salary, our standard operating budget for the intranet is $0). Our communication plan began in October with a blog post to staff which included:
  • a poll for them to help select a mission statement for the intranet
  • an introduction of the champion team
  • the announcement of a November satisfaction survey
In November we launched the satisfaction survey on our intranet, which included a homepage announcement and a banner (built in 5 minutes on canva.com!).  The banner will remain active on the site until Dec 1st, although the announcement would likely only stay on the homepage for 2-3 days before being pushed off by newer content.    Because our intranet is our primary vehicle for communication, no emails will be distributed to alert staff of the survey.

My goal is to update this post at the conclusion of the survey and share our results - good, bad and ugly!





Announcement

Title: dimension Needs Your Feedback

As part of our continued path of improvement, we invite you to participate in a Foundation-wide dimension intranet satisfaction survey.

All staff are invited to participate in our anonymous survey to let us know what you like, and where we can improve.  The survey will be available until December 1st, and will take approximately 5 minutes to complete.

After analyzing the results, we will communicate both the findings and our action plan.  It is only through transparent accountability that we will realize our organizational potential and build a great place for us all to work.

The goal of dimension is to effectively communicate and engage staff with current resources and information in a collaborative environment while supporting a foundation-wide culture change to bridge the distance between departments, offices and employees.



Survey Questions

  1. The homepage has relevant information to help me be more effective in my job 
  2. I can easily determine when new content is added or updated
  3. I am aware of what information and resources are available
  4. The intranet is well organized to provide quick answers to my questions
  5. When using the intranet, how easy is it to find the information you need?
  6. Navigating the intranet is simple and intuitive from an end user perspective
  7. I can easily find the information I am looking for using navigation at the top of the site, and the links on the left side of the page
  8. I can easily find the information I am looking for using the search feature
  9. I typically use the search function before I try to manually navigate to content
  10. Content is up-to-date and relevant for my needs
  11. When I have an idea for new content, it is clear who manages each section of dimension and who to submit the request to
  12. Help Desk tickets related to the intranet are responded to in a timely manner
  13. Questions posted on pages of the site, and forums, are answered in a timely manner
  14. Which of the following items describes your use of the discussion forums
    1. I feel comfortable posting a question
    2. I am confident my questions will be answered promptly
    3. I enjoy reading the forums
    4. I use the forums to search for answers to questions others have asked
    5. I am not comfortable posting a question
    6. I would rather email, call or Jabber someone
  15. If offered, I would attend training to better understand how to use functions of the site such as search, forums, blogs, page comments, etc.
  16. If offered, I would attend training on the organization of the site, and where to go to find the information I need.
  17. My preferred style of training would be
    1. Self-paced course
    2. Video tutorial
    3. Live webinar
    4. In-person
  18. How could we improve dimension
    1. Make it easier to find information by improving the site navigation
    2. Allow more personal information to be added
    3. Increase participation by senior management in social tools (forums, comments, Kudos, blogs, etc)
    4. Make it easier to upload documents
    5. Make it easier to share documents with others
    6. Develop more training
    7. Improve readability and overall quality of content
    8. Use more images and videos to make the site less text heavy
    9. Offer engaging activities
    10. Develop mobile access capabilities
    11. Provide an off-topic (water-cooler) discussion forum, for conversation that are less business focused
    12. Develop a subscription tool to send peronalized emails of what has changed on dimension
    13. Make the site more fun
  19. List the top 3 things you would like to see changed and how you would change them
  20. What type of information do you find the most valuable on the homepage?
    1. Announcements
    2. Application shortcuts
    3. Staff highlights (promotions, new hires, etc) 
    4. Forums
    5. Blogs
    6. Upcoming events
    7. Kudos (employee recognition tool)
    8. Timeline (aka Activity Feed)
    9. Recently added/updated content
    10. Quote of the day/week
    11. Poll of the day/week
  21. What is your overall level of satisfaction with the intranet
  22. How often do you typically visit the intranet?

Wednesday, September 23, 2015

How to Make a YouTube Video Responsive

This work comes from a great blog post by John of AvexOline who identified the solution.  The only reason I'm blogging about it is in fear that I might lose the link or something might happen to their post - I needed to document this for myself, which is the reason I blog in the first place!

FWIW - the video below is actually terrific and worth watching!

<style type="text/css">
.video-container{position: relative; padding-bottom: 56.25%; padding-top: 30px; height: 0; overflow: hidden;}
.video-container iframe, .video-container object, .video-container embed {position: absolute;top: 0;left: 0;width: 100%;height: 100%;}
</style>

<div class="video-container">
<iframe allowfullscreen="" frameborder="0" height="100%" src="https://www.youtube.com/embed/qgP7SGhiHCg?rel=0&amp;showinfo=0" width="100%"></iframe>
</div>


Responsive video in action:

Thursday, July 23, 2015

Lessons Learned from a Conference Scavenger Hunt

GooseChase is a mobile platform that makes running scavenger hunts incredibly easy and exciting. Participants complete missions, receive points, and track progress on real-time leaderboards and photo feeds. GooseChase apps are available for download on iPhone and Android, but not Windows phone.  A very similar competitor is The Go Game.

The organizers of the March of Dimes 2015 staff conference decided to use GooseChase for all  attendees as a fun way to help staff get to know each other, and to engage with the mission.  I volunteered to assist from the administration side of the game, and below are some of my observations.

Lessons Learned

Mission setup

GooseChase recommends 30+ missions for a successful game.  When creating a mission a title, description and identifying the number of award points are the only required fields, although you can also add a photo and link to website.

Regardless of the duration of your event, you can continue to add and edit missions.  Our game was setup for 3 days, and we added 7 events each day.  There is NOT a way to pre-program when new missions begin (nor am I suggesting there should be), so we added new missions nightly.

While adding new missions certainly keeps the game motivation/excitement alive for a multiple day game, I think that starting with 7 missions was limiting.  My recommendation would be to take the GooseChase advice and start with 30+ missions from the start, and if you want to add more later, go for it!

One bit of advice I picked up from a YouTube video was that when you have >30 missions it can become difficult to scroll through the list.  One tip they offered was that if you do offer a lot of missions, to consider printing out a list of them, and numbering them so teams can quickly find them in the app's mission list.

Teams versus individuals


The pro's of this approach are that it makes the app registration process less confusing, and allows the organizers to identify individual contributions.
The con's are that you lose a lot of the team building benefits that could otherwise be achieved, and more importantly you have to pay for significantly more licenses.

We opted for individual registration.

Who is winning?

The leaderboard section of both the app and the desktop site allow you to see who has earned the most points.  The catch is that the only thing that displays is the team/individual name.  As an admin it would be helpful if you could see a full list of user information such as their email address, but I couldn't find that capability.

While doing a corporate event we assumed folks would use their names, instead we ended up with completely random gibberish which prevented us from being able to identify who was playing.  In hindsight, it would be well advised that you ask your users to adhere to a certain naming convention so that you can easily identify who is who (this goes for both team and individual games!).

Examples usernames from our challenge that proved unidentifiable:
  • GFN1906
  • ChicagoBabe3
  • GloryDaze
  • TrackyRed

Inclusion of those not in attendance

As mentioned, our implementation of GooseChase was for an invite-only staff conference, which is typically attended by around 35% of our organization.  Since the conference is not streamed and doesn't offer any remote participation, there was a suggestion that the game be modified to provide opportunities to include staff that weren't invited to the conference.  Ultimately it was decided that the GooseChase be focused on on-site participants to keep it as interactive as possible, but it might be worth considering how to include off-site participants in the future. 

Screenshots

The GooseChase app only allows you to utilize the camera from your mobile device, NOT your photo gallery.

Communication, promotion, participation

The March of Dimes staff conference attendees received a binder of conference materials upon registering.  Since I didn't attend the event, and wasn't involved in the promotion of our GooseChase event, I can't say what they received that informed them about the game, instructions on how to participate, or how much it was promoted in other ways (general sessions, posters, management, etc).

Out of 350 conference attendees (and an enterprise/unlimited license), only 40 attendees download the app, and 18 of those completed at least one mission.

Missions

Below are the missions we used and the number of participants in each.  Why am I providing this? When constructing our GooseChase we struggled to find ideas for what would be fun - so consider this us paying it forward!

Mission Participants
Adaptable: Is your glass half full or half empty? Take a picture of both,8
Bold: Embracing change takes courage, get bold.  Visit the tech bar before 3pm and write on the courage wall.6
Circle of Champions: Download the Circle of Champions logo, print and wear it proudly.3
Ding ding ding! We have a winner!: Put a dollar in the March for Babies bucket.8
Enable breakouts: ABT getting you down? During your morning breakout session, share a picture of your success during your beach task.4
Entrepreneurial: Create a new breakfast food or beverage idea and encourage others to try it too.4
Externally aware: Did you embrace the 30 day challenge and meet someone new? Share!4
Feeling Floridian?: Ask the hotel waiter for the Tipsy Turtle Cocktail or key lime pie dessert at dinner tonight, share a picture of your treat!8
Find a friend: Make a new friend on your dine around tonight.10
Going, going, gone: Get ready to win big! Find out about Mobile bidding for auctions and download the link to our 2015, 24 hour silent auction at the tech bar.  Once you've received it send a picture of your screen.9
Home improvement: On your water taxi ride to dinner, take a picture of the house with the best story.6
I <3 technology: Technology getting you down? Take a picture of yourself having a drink and telling us about it the tech bar at 6pm.9
I like turtles: It is sea turtle nesting season here at the Marriott Harbor Beach.  Find the 2 different nesting areas on the beach and take a picture of yourself in awe.9
I spy with my little eye: Where's FRED? Find him and take a picture sharing a cup of coffee at the tech bar.11
Long term focused: reflect on your time at the conference4
Look at me now: Search yourself in CRM8
Macro planner: Visit the tech bar and search within FRED to find out about the macro planning program.8
Mobile giving: (no description was provided)4
Nimble: team pyramid? leap frog? take a picture being in the right position for change.3
Paparazzi: Find in your program and document the NICU Family Support photo of the year.9
Publix: Find someone wearing an I Love Publix button4
Research: Look in your programs and take a picture of the 2014 National Ambassador, Aidan Lamonth.12
Say hello: Introduce yourself to a new volunteer and show it by photographing your two badges (should definitely be from different states)4
Spread the word: 1 in 10 US babies is born prematurely each year, pass a note to a hotel guest or employee and take a picture.8
Surgeon general warning: Find a picture of a smoke free zone/no smoking and take a picture.7
Whip it good: from 4:30-5:30 get inspired for your next Signature Chef auction and see what the hotel is cooking in the lobby.  Take a picture of yourself and the chef.6
You gotta be in it to win it: Use the tech bar and donate $1 to marchforbabies.org.  Then take a picture of the screen.  Note: You will NOT be able to donate from your mobile device and submit a screenshot that way.9

Wednesday, July 22, 2015

How March of Dimes Conducted a 30 Day Challenge for Culture Change

For the past year March of Dimes has been undertaking a major strategic realignment to improve our operational efficiency.

Part of the communication plan around the realignment project involved using a 30 day challenge to engage employees in “micro-actions” each day for thirty days. The goal of the challenge was to focus on helping the Foundation establish a more open environment for communication and problem-solving.

Setup

To help drive traffic we posted a widget in the upper right corner for the duration of the challenge which linked to an area of the site with a deeper explanation.
Intranet homepage


30 Day Challenge area

We chose to use a discussion forum to post each challenge in order to keep responses organized and to make it easier to track engagement.


Results

Forum...MayJune (month of challenge)
Hits2992,848
Visits1991,857
# Threads1637
# Posts23779
# Likes51958



Lessons Learned

30 Days: Calendar or Business?

Our first struggle with the task was the literal interpretation of "30 day challenge".
  • 30 business days
    • then should it just be called a 6 week challenge? 
  • 30 calendar days, including weekends
    • would staff really participate on a weekend?

Having already decided to kickoff the project in June, and call it a 30 day challenge, we went right down the middle and posted challenges on the 22 business days in the month (4 1/2 weeks).  In hindsight, this is something you probably want to put quite a bit of thought into well before your launch date and be extremely clear in your communications, because it was a source of confusion for our staff.

Tagging & Email Notifications

Our intranet software includes tagging capabilities where you can tag individuals or groups of users, and they are then notified via an email and system message on our site.

Using the challenge to promote this feature was a rare opportunity to educate staff on what had been an under-utilized tool. By the end of the challenge nearly every forum post included a tag to a users, department, or physical office location.

Email notifications, generated from tagging, helped keep daily challenges on everyone's mind and fostered a herd mentality where those tagged quickly joined the thread.


Example of one of our forum threads with a few tags, click to expand

Email Overload

Our intranet allows users to subscribe to entire forums, or to individual threads, and a thread reply triggers an automatic thread subscription.  I love these subscription features, but in heavily utilized threads, the quantity of emails can cause some staff to get frustrated.

From the start it would be recommended to provide clear instructions about any email subscriptions your system has and ways to handle overload.  We took this as an opportunity to educate our staff on how to utilize Outlook email rules to maintain an organized inbox - an invaluable lesson that many staff were unaware of.  We also provided opt-out instructions.

Gamification: Leaderboards & Badges

Let's face it, competition is fun. So why not post a leaderboard of your most engaged employees, departments, offices - or award badges to users upon passing certain milestone!

We didn't implement leaderboards because of concern about quality of engagement over quantity, and to be sensitive to those users that were taking part off-line.  In hindsight, I feel that a leaderboard would have been helpful and that its value trumps other concerns.

Load Testing

We weren't sure how many users would participate in our challenge, but assumed it would be a manageable enough number that our intranet platform would be able to keep up.  We were wrong.

On the second day of the challenge we noticed that we had the same number of posts as the first day - which seemed very unusual and soon after were informed that some users were unable to post.  It turned out that our software had an IE specific bug which was preventing the display of >72 posts.

How to get around this? When a thread reached 50 replies, we edited the thread and marked it closed.  This locked the thread, preventing additional replies. Then, we created a new thread, using similar title, and the same thread copy to capture additional responses.  Problem solved!

Challenges

Below is a brief description of the challenges we posted, and the amount of engagement they triggered.  Why am I providing this? When constructing our 30 day challenge we struggled to find ideas for what would make a good challenge - so consider this us paying it forward!

Day Challenge Responses
1say hello to one person you do not normally interact with72
2share with a colleague what you value about them72
3volunteer to do something that is out of your comfort zone, or normal scope of work28
4encourage those around you to think of themselves as part of a team51
5post one word that describes the team that you work with72
8complete an employee satisfaction survey42
9seek out a perspective different than your own by inviting a colleague, volunteer or partner to participate in a conversation or meeting, and seek out this person’s input and ideas on the topic being discussed20
10congratulate a colleague, volunteer or partner on a success26
11keep an open mind all day long16
12post one word that you feel best describes <the company you work for>60
15ask a colleague, volunteer, or partner, for their input on a problem or challenge you are facing19
16consider your team members involvement before making a statement that begins with “I”13
17redirect any conversation that becomes exclusive or siloed13
18share the title of a song that you think of when you think of your work team and the work you do together55
19share the Olympic sport, or event, that you think of when you think about <the company you work for> 29
22contribute to a culture of “yes” by maintain a positive attitude in all conversations or meetings19
23take 5 minutes and offer to help a colleague with whatever they need20
24provide encouragement to someone around you21
25describe your favorite “team” moment20
26share the tool (from a toolbox), that you think of when you think about your team of colleagues, volunteers and partners at <the company you work for>29
29have lunch with someone who you don’t interact with on a daily basis19
30what are you coming away from after 30 Days focused on open environment? do you and your team have plans to continue on the path towards a more open environment for communication and problem solving? 22

Here are additional challenges which we considered:
  • Fill in the blank (Mad Libs style)
    • Example: The company I work for reminds me of  _____ and inspires me to ______.
  • Share something you are looking forward to happening this year
  • Share something about yourself that most people don’t know
  • Share something funny (joke, picture, etc)
  • Share your favorite inspirational quote
  • Repeat one of the challenges that you especially enjoyed
  • Share your thought on the 30 day challenge – what worked well, what could have been better?
  • Take your 15 minute breaks and unplug on at least one of them – walk, stretch, meditate

Thursday, February 5, 2015

Automating the scheduled extraction of db data and FTP of it using SQL Reporting Services 2012

Our intranet team manages our relationship with Next Jump, an e-commerce company that manages employee savings programs for companies worldwide including 70% of the Fortune 500.

To login to their site, employee data must first be sent to Next Jump.  We could log into our intranet db server, open SQL, run a script, copy the data to Excel, then go to the vendor site to upload it...but I get bored by repetitive things so we've automated the process.

The following steps were necessary:
  1. Start SQL Reporting Service 2012
  2. Create a report project
  3. Subscribe to the report
  4. Implement a scheduled SFTP task 
Start SQL Reporting Service 2012

From the server you have SQL installed on, open SQL 2012 Reporting Services Configuration Manager and essentially clicking "Next" through each step.  You'll want to take note of the web service virtual directory, and the report manager site identification.  The default for them is:
  • http://<your server name>/Reports
  • http://<your server name>/ReportServer
At this point both of those URLs should resolve and give an indication that your Report Service is up and running.  If you are running into difficulties, this is a great article which helped me get it up and running: How To Configure SQL Server Reporting Services In Order To Deploy Reporting Services Reports In GP

Creating a Report Project
Getting this setup was relatively easy as I had done it previously, albeit on an older version of SQL (Creating a report from Oracle WebCenter Analytics db using SQL Reporting Services).
  1. From your Start button, open SQL Server Data Tools.  If this is your first time going through it'll ask you to select a format - choose Business Intelligence if you are just making reports.
  2. Select File > New > Project > Report Server Project, be sure to name the project at the bottom of the page.
  3. In the Solution Explorer right click Reports then choose Add > New Item > Report, be sure to name the report at the bottom of the page.
  4. Specify a data connection from the Report Pane by clicking New > Data Source.  Name and configure your source.
  5. Define your data set from the Report Pane by clicking New > Data Set.  Name the data set and enter your query.
  6. Add a table to your report by going to the toolbox, selecting Table and dragging it onto the Design area of your report.
  7. Add your data to the report table by expanding your data set in the Report Data pane and dragging/dropping your data elements into the table column headers.
  8. Toggle the report preview button to ensure your data connection is working and the table is displaying nicely
  9. Deploy your report to the web server by selecting Build > Deploy
I've greatly simplified the steps above.  For a more comprehensive explanation with pictures please refer to Create a Basic Table Report (SSRS Tutorial) on the Microsoft Developer Network.

Subscribe to the Report
Assuming a successful deployment, verify it by going to the virtual directory at http://<your server name>/Reports.
To subscribe to the report:
  1. From the home page select the report folder, then select the down arrow next to the report and select Subscribe.  This dropdown didn't appear for me in Firefox so I had to use IE.
  2. Select the Windows File Share delivery method and configure the report as necessary
  3. From the My Subscriptions link (top right of page) you can verify the subscription, when it was last run, and it's status.
Yay! The easy part is done!

Implement a scheduled SFTP task
From the brief research I did I came to the conclusion that FileZilla wouldn't work and that I would need to install http://winscp.net which has a command line option that would allow for scheduled SFTP.

The install is straight forward and there is plenty of documentation on how to use the tool from a command line or through other forms of programming.  What I found most useful was their page Schedule file transfers (or synchronization) to FTP/SFTP server.
  1. Start by installing WinSCP
  2. Open the client application and connect to your SFTP site.  You might want to save the connection for later testing.
  3. I'm on Windows 7, so I went to Admin Tools > Schedule Tasks then created a Basic Task
  4. For the action choose start a program ("C:\Program Files (x86)\WinSCP\WinSCP.exe")
  5. You'll need to point to a script file (.txt) which will open the FTP application, connect to the site, and transfer the file.  For the arguments area you'll need to point at this script using a command like: /script="\\<your share file path>\WinSCP_script.txt".  For testing you might want to prefix that with /console which will open a window to show you that it is working as intended.
  6. Next go and create the script file in the location you specified.  This is what mine looks like:
    # Automatically abort script on errors
    option batch abort

    # Disable overwrite confirmations that conflict with the previous
    option confirm off

    # Connect to SFTP server using a password
    open sftp://username:password@connectionSite.com/

    # File to upload
    put \\HQ\GeoffGarcia\Project\NextJumpExport\NextJump.xls

    # Closes all sessions and terminates the program
    close
    Run it all a few times to make sure it works without a hitch...and you're done!!!
    This might seem a little tricky if you haven't tried to create a SQL Report, use an FTP client, or write a script, but I hope this mini-guide will help you accomplish your goal!


Troubleshooting:
The second time I tried to implement this I couldn't get the console window to open or any other programs that I tried it with.  I deleted the task I had created and then recreated it and everything worked.