To be perfectly honest, this Reportapalooza challenge scared me the most. While I have more Crystal Reports solutions under my belt than I care to count, I had never actually used SAP Crystal Presentation Design (formerly Xcelsius). In fact, other than some hands-on tutorials over the last few weeks, this would be my first ever dashboard. It turns out that while development time was limited, the process was surprisingly easy. Of course, having a background in Excel was pretty critical to the successful completion of the project.
Before I go any further, if you want to see the dashboard, please visit the Reportapalooza Dashboards by Request page: http://bit.ly/bpv4iZ
The original concept called for a dashboard that celebrated Bobby Cox’s career managing the Atlanta Braves. Bobby has managed the Braves for 25 seasons, from the 1978-1981 seasons to the 1990-present seasons. He is retiring at the end of this season, in just a few short months, and this dashboard is a way to pay tribute to his years in Atlanta. But first, I needed a public source for statistical baseball data. I found it in website called Retrosheet (http://retrosheet.org) which not only had the game by game box scores but packaged them in easily downloaded and imported CSV files.
It turns out that at 162 games per season, 24 seasons (the site did not have 2010 data yet) runs about 3888 rows of data (one row for each game), so my next task was to massage the data, removing columns of statistics that were not relevant to the dashboard, such as the names of the starting players from both teams, and to prep the data for use in the dashboard. By default, SAP Crystal Presentation Design tends to compile and initialize slowly with large spreadsheets, so I had decided cut back to just 5 seasons, from 2005-2009, so I could get the dashboard completed on deadline.
Now I had an idea of what I wanted the dashboard to do. Interactivity is a key component of SAP Crystal Presentation Design dashboards, so I wanted to create kind of a statistical playground. I would have a Winning Percentage gauge where you could filter the data by various criteria, such as opposing teams, day/night games, season, month of season, home/away games, American League (interleague since I did not include post-season data), National League, or just within the NL East division. Depending on the combination of the filters, the gauge would instantly calculate and display the percentage of wins over games played.
In order to make this work, I had to work out a way to get from the raw data to an intermediate stage from which I could easily calculate my key metric, the winning percentage. This meant I needed to dynamically calculate the number of wins and the number of games played based on the filter settings. To do this, I created a series of formulas for each filter that evaluated to 1 if an individual game that passed the filter, and 0 if it did not. I multiplied each of the filter values against each other to get a final filter value (1 if the game passed all filters, 0 if it failed any one of them). I then summed up all the games played that passed the filter and all the games won of those games played and I had the numbers I needed to calculate the winning percentage. As the filters changed, the Boolean filters changed and the winning percentage changed.
So far, you might be saying to yourself, this is all Excel and has nothing to do with SAP Crystal Presentation Design but you would be wrong. Properly designing your Excel data source is essential to constructing a strong and useful dashboard. Now that I had my data source, I could construct my filters by building the lists of opposing teams for the List Builder object, the drop-down menu options for the Seasons and Months Combination Box objects, the men options for the League, Day/Night and Home/Away for the Radio Button objects. I configured each object to write selected filter data into target destination cells that are referenced by the filter formulas. Finally, I added the Gauge Object and configured it to pull its value from the cell containing the winning percentage number.
The actual construction of the first dashboard page was relatively easy once the data was in place, just tying all the objects together through the intermediary filter formulas and then providing formatting. I pulled the colors from a web page I Googled that provided me with the RGB values for the Atlanta Braves team colors, used the SAP Crystal Presentation Design Formatting tools to help with sizing, alignment and spacing, and the Appearance properties to give the dashboard a polished look that pops. Finally, I set alerts to show that a winning percentage below 45% would fall into the red, and above 55% into the green.
With the bulk of the design work going into the initial tab, I turned my attention next to the second tab, a geographic representation of Bobby Cox’s winning record state by state (and in Canadian provinces). While there are many third party add-ons to SAP Crystal Presentation Design and other versions of the Crystal dashboards products, I chose to stick with the dashboard components that came with the product. One of those is a map of Continental USA. Crystal automatically lets you easily reference each state either as a selection tool or as a presentation tool. I chose the latter.
I again created a list of each major league team, identified by home field state. I then created a second set of filter formulas that would identify whether a game was played in each state or province on the map. Counting the number of games won and the number of games played in those states was easy, and linking the Map object to those calculations even easier. I sized the maps to make sure both the USA and Canada maps would fit and formatted the Appearance text and color properties. Easy peasy.
For the Game by Game Stats tab (By the way, the Tab object itself is a Crystal dashboard tool that make it simple to provide views of your data in different contexts), I returned to strong-arming the Excel data to achieve results that are presented in a clear and readable manner. The Calendar object is a tool that lets you pick a date and writes that date into a cell. I modified the Behavior tab to limit the date range from the start of the 2005 season to the end of the 2009 season (Spring Training and Post Season data not included). Unfortunately, Crystal doesn’t give me the ability to exclude the off season or travel and off days when games were not played. This results in #N/A is displayed in the Box Score object when those dates are selected, but I am getting ahead of myself.
Using the selected date, I modeled the Box Score table in Excel and used VLookup functions to access the desired stat for that date’s game in each box. A little creative formula writing to enable the spreadsheet to place the winning and losing pitchers in the correct row and this tab was done. Honestly, the most complicated portion of this tab was sizing the width of the boxes, which was accomplished through the Appearance properties, where I also modified the text and color settings to fit within the dashboard’s theme.
The final tab was of course the easiest to construct – I used a couple text boxes to give credit to Retrosheet (http://retrosheet.org), add a few acknowledgements and disclaimers, and of course post a complete biography of Bobby Cox for those who might be interested in learning more about the man behind the stats. I also used an Image object to include Retrosheet’s logo on the page. Some final formatting and testing and I was done, ready to compile the dashboard into a SWF file and send it to the Reportapalooza Conductor.
I wanted to add a feature that would filter by and indicate games when Bobby was ejected from a game (he has been so a record 157 times as of the end of August 2010), but I was not able to find that data. I also wanted to create some What-If functionality that would let you ask: what if the Braves played more day games or more Interleague games. But I did not have time and I was not sure what the added value would be to the dashboard. After all, Baseball is game of statistics but not so much looking back to wonder what could have been than to record for posterity what did happen.
So there you have it. How I created a Bobby Cox sports statistic dashboard in less than a week. You chose the topic, and I ran with it. I hope you like where I took your idea.
Reportapalooza is still just getting started. For general Reportapalooza information, visit the web site at http://bit.ly/ajKAJF.
Through this week, vote for your favorite Dashboards by Request (mine, mine, oh please, mine) by hitting the challenge page - http://bit.ly/bpv4iZ - and click on the Tweet link tell your followers your voting and be entered into a drawing for a $25 iTunes gift card.
If you have your own amazing reporting story, submit it to the Reporting Hero challenge at http://bit.ly/cAPKvH. Yours truly and his fellow Reporting Maestros will be picking the best ones and you could win a collectible music poster.
My next task will be to create a pair of training videos, one on a basic Crystal Reports feature and one on an advanced Crystal Reports feature. Check back here (http://bit.ly/9PqTyW) on Monday (9/13) to check out the videos and vote for your favorite. Click the Tweet link to tweet about your participation in Reportapalooza and you could win $200 in concert tickets!
If you have enjoyed this blog post, I am glad. If you learned something from it, I am pleased. If you voted for my Sports Statistic: Bobby Cox dashboard (http://bit.ly/bpv4iZ), then I am overjoyed. If you will help promote it, I am ecstatic. If you want to find out more about me, Reportapalooza or Crystal Reports, you can visit follow me on Twitter @David_Deitch, become my professional friend on Facebook (http://bit.ly/9HaNKj) or connect with me on LinkedIn (http://linkd.in/cBQzEv).
You can also visit Crystal Connections Atlanta on Facebook (http://bit.ly/ciXH0T) and coming soon, my new web site, http://crystalconnectionsatl.com. And of course, you are invited to join over 2600 Crystal Reports professionals on my Crystal Reports group on LinkedIn (http://linkd.in/9stmUW) and join the over 2100 members of the Crystal Reports Users Group on Yahoo Groups (http://bit.ly/bW2YuN).
Oh, and if you would like to get a copy of this dashboard to see how I did, just let me know.
Finally, if you tweet about this blog (and please do), be sure to include the #Reportapalooza tag in your tweet and help us get the word out. This has been a long post, and if you have read this far (please let me know if you have), thank you. If you have visited the Reportapalooza web site, thank you. And if you have voted for my Sports Statistics: Bobby Cox dashboard, THANK YOU.
David Deitch
Senior Crystal Reports Developer
SAP Reportapalooza Maestro
