Tuesday, April 27, 2010

SQLSaturday Richmond Wrap-up

What an amazing event. Over the past several years, I have been an attendee, volunteer, and speaker for a number of SQLSaturdays and code camps. Not until recently have I understood the amount of work and effort that an event of this type entails.

SQLSaturday events are community-driven events that provide free training for SQL Server DBAs, developers, and BI professionals. Started in 2007 as the brain child of Andy Warren, Steve Jones, and Brian Knight, the brand has recently been acquired by PASS, the Professional Association for SQL Server.

On April 10, 2010, Richmond, VA put on a SQLSaturday at the local ECPI College of Technology. Thank you so much to the speakers, sponsors, and volunteers who made this event possible. A huge thanks goes to the leadership team of Andy Leonard, Kevin Israel, Ron Deskins, and Steve Fibich who put in many hours of work!

As most of these wrap-ups go, I feel there were some things that we could have done better, and I wanted to share my thoughts to hopefully help future event leaders!

Volunteers

Volunteers are a godsend. Do not underestimate the need for multiple volunteers throughout the entire day. At one point when I was manning the registration table, I needed to grab an attendee to sit at the table while I ran away to take care of a room situation. (Thanks, Jimmy!) Speaking of room situations...

Technical Equipment

Check your projects and/or any other technical equipment needed the day of the event. We had a projector that decided to cut off of a fifth of the screen! We were lucky to be able to move to another room and readjust accordingly. Having a back up room and/or back up projector is a must.

Evaluations

We elected not to have evaluations for the event based on time and budget constraints. In hindsight, I wish we had deemed this more important. I really only have my own perspective and a few feedback emails to judge how the event went. It would be nice to know if we missed anything that was important!

Raffle

After raffling off the sponsors' raffle prizes, we had a raffle for everyone to win some books donated by Microsoft. Trying to be proactive, we had put everyone's name in the box who had signed up to attend. Unfortunately, with a 30% drop off rate, that means we pulled quite a few names of people who were not there. This actually could tie in very well with Evaluations, where we use the forms that people fill out to pick the winners of the final raffle.

Materials

Tell your sponsors they must send their materials to be including in attendee bags/raffle signs/raffle prizes to you ONE WEEK BEFORE THE EVENT. The items must arrive ONE WEEK BEFORE THE EVENT. Send the sponsors multiple emails to make sure the materials are there ONE WEEK BEFORE THE EVENT. Did I say that enough times? ;) We did not clarify a time for materials to arrive, and due to some last minute postal crises, had some extra details to work out the night before the event. Better to be safe than sorry, so make sure you receive your materials ONE WEEK BEFORE THE EVENT. ;)

Beginning of the day

Start your morning setup at least one hour before you tell attendees registration will open. We had some attendees who were so ready to learn some SQL Server, they arrived 25 minutes early! We had to ask them to wait as we finished setting up the registration table, breakfast, and last minute room adjustments.

I also wanted to highlight a few things that I think went pretty well!

Schedule

We received a few compliments on our massive color coded schedule. Each room had its schedule on the door in the appropriate color, that matched the wall schedule, and matched the map that each attendee received. We were easily able to switch rooms or make adjustments to both the wall-schedule and the door-schedules. I think it worked out well for both the attendees and leadership team!

Map&Schedule       Schedule 

Lunch

I cannot stress how great boxed lunches are. We ordered lunches from Firehouse Subs, which each came in nice, neat, easy boxes. Attendees grabbed a box, grabbed a drink, and were on their way. This resulted in no line during lunch, and very little mess to clean up! (Yes, that is my flow diagram for lunch - there is no such thing as too many signs or too many instructions ;) )

LunchInstructions

Sponsors

We set up our sponsors in the same room as breakfast and lunch. This allowed attendees to grab some food and browse the sponsor's wares at the same time. Based on the amount of raffle tickets in the sponsors’ raffle boxes, I think we got almost everyone to visit the sponsors!

LunchBoxes       Sponsors

Volunteer Instructions

This could be me going overboard, but we created instructions for the folks at the registration table. This included FAQs that attendees may ask, things to mention to the attendees when they checked them in, and the different steps they needed to do at the table. This worked out well because we could hand the volunteer the instructions and let them have at it, while we focused on other items of interest!

Here are a few posts from SQLSaturday Richmond speakers as well:
http://jasonhall.blogs.sqlsentry.net/2010/04/sql-saturday-30-richmond-virginia.html
http://nullgarity.wordpress.com/2010/04/14/integration-services-in-the-real-world-slide-deck
http://robertlambert.net/2010/04/sql-saturday-30-richmond-virginia-april-10-2010

Do you have feedback? Thoughts? Want to run your own SQLSaturday? Leave a comment below!

Monday, April 5, 2010

Different Items per SSRS Column Group

I had an interesting reporting scenario posed to me that I thought I would share!  (The actual business information has been changed to protect the innocent.)  In this scenario, we own multiple stores that sell a dozen or so products.  We would like a report that shows the type of products that each store has sold on a particular day.  Keep in mind that we could start stocking new products at any time, and we would still like our report to work.

Here is our existing data for the database:

3/4/2010 Sports-R-Us Virginia Softball Glove
12/15/2009 Sports-R-Us Virginia Tennis Racquet
12/15/2009 Sports-R-Us Virginia Dartboard
6/6/2009 Sports-R-Us Virginia Fishing Pole
6/6/2009 Games And More Virginia Canoe
2/21/2010 Games And More Maryland Pool Table
2/21/2010 Games And More Maryland Golf Bag
2/21/2010 Games And More Maryland Canoe
2/21/2010 Games And More Maryland Softball Glove
2/21/2010 Games And More Maryland Football

This scenario really comes into play when you have many different and unknown items in your column grouping, and you need to show them in a compact space.

We can use the following query to simulate this situation:

SELECT '03/04/2010' as TransactionDate, 'Sports-R-Us' as StoreName, 'Virginia' as StateProvince, 'Softball Glove' as Product
UNION ALL SELECT '12/15/2009', 'Sports-R-Us', 'Virginia', 'Tennis Racquet'
UNION ALL SELECT '12/15/2009', 'Sports-R-Us', 'Virginia', 'Dartboard'
UNION ALL SELECT '06/06/2009', 'Sports-R-Us', 'Virginia', 'Fishing Pole'
UNION ALL SELECT '06/06/2009', 'Games And More', 'Virginia', 'Canoe'
UNION ALL SELECT '02/21/2010', 'Games And More', 'Maryland', 'Pool Table'
UNION ALL SELECT '02/21/2010', 'Games And More', 'Maryland', 'Golf Bag'
UNION ALL SELECT '02/21/2010', 'Games And More', 'Maryland', 'Canoe'
UNION ALL SELECT '02/21/2010', 'Games And More', 'Maryland', 'Softball Glove'
UNION ALL SELECT '02/21/2010', 'Games And More', 'Maryland', 'Football'

At first glance, this seems like a simple matrix scenario.  If we create a dataset using our query, we can use the fields in a matrix that contains a detail grouping for our rows on TransactionDate, StoreName, and StateProvince with Product as a dynamic column grouping at the end.  Unfortunately, this will result in a lot of blank spaces for the Product columns, as the data per row will only display for the associated group.  If you see a matrix that looks like this, you may have a similar situation!

Original

To get the desired layout, we need to make three changes to our report.  First of all, modify the query to give a ranking per every group, which means we get to use one of my favorite clauses: OVER.  In our scenario, we will partition based on TransactionDate, StoreName, and StateProvince and order by Product.  Here is our new query:

SELECT TransactionDate, StoreName, StateProvince, Product
      , ROW_NUMBER() OVER ( PARTITION BY TransactionDate, StoreName, StateProvince ORDER BY Product ) AS ColumnGroupNumber
FROM
(
      SELECT '03/04/2010' AS TransactionDate, 'Sports-R-Us' AS StoreName, 'Virginia' AS StateProvince, 'Softball Glove' AS Product
      UNION ALL SELECT '12/15/2009', 'Sports-R-Us', 'Virginia', 'Tennis Racquet'
      UNION ALL SELECT '12/15/2009', 'Sports-R-Us', 'Virginia', 'Dartboard'
      UNION ALL SELECT '06/06/2009', 'Sports-R-Us', 'Virginia', 'Fishing Pole'
      UNION ALL SELECT '06/06/2009', 'Games And More', 'Virginia', 'Canoe'
      UNION ALL SELECT '02/21/2010', 'Games And More', 'Maryland', 'Pool Table'
      UNION ALL SELECT '02/21/2010', 'Games And More', 'Maryland', 'Golf Bag'
      UNION ALL SELECT '02/21/2010', 'Games And More', 'Maryland', 'Canoe'
      UNION ALL SELECT '02/21/2010', 'Games And More', 'Maryland', 'Softball Glove'
      UNION ALL SELECT '02/21/2010', 'Games And More', 'Maryland', 'Football'
) AS OriginalQuery

The second change that we need to make is in the matrix itself.  On the Product column grouping, we change the field so that it groups on our new "ColumnGroupNumber" field.  Don't change the field in the detail row, as we would still like to show each item.  Once we have made that change, every set of Products will start in the first column.

Our final change is to change the header expression so that it only shows the header on the first column and doesn't repeat fifty times.  We do this by modifying the expression of the textbox to this:

=IIF(Fields!ColumnGroupNumber.Value = 1, "Product", "")

When we put all of the pieces together, we end up with a report that looks pretty darn good!

Final