While reading the SAP HANA Academy’s coverage of the Indian Premier Twenty20 Cricket tournament these past few weeks you may have wonder how we transform the data that is processed in SAP HANA into insight-laden SAP Crystal Reports analytics. In this tutorial video from the SAP HANA Academy Jamie Wiseman gives an overview on how he built various cricket analytics using SAP Crystal Reports that used ball by ball cricket match data that was loaded into SAP HANA. By using a direct connection to the SAP HANA tables that store the data, Jamie created a Wagon Wheel, a Bowler grid and a Bowler versus Batsman analytic to aid in the advanced cricket analysis we provided throughout the tournament.

Jamie created a Wagon Wheel analytic that shows the angle, length, and result of each run scoring shot a Batsman struck during a match. Jamie used a radar chart with an ‘on change of’ value that displayed the angle in which the ball was struck and returned a distinct value for each of five different types of run scoring shots. To retrieve the data for the analytic Jamie wrote directly into the report a command object, a.k.a a custom SQL that is portable with the report. The query combines data from two separate datasets. The first allows the end user to select the batsman’s name and match data. The second brings back 360 records, one for each degree of the chart, to ensure the validity of each shot’s direction. For further details read Jamie’s in-depth description of how he created the Wagon Wheel.


Check out an example of a Wagon Wheel below that details the best individual innings of the tournament – the 112 runs Kings XI Punjab’s Virender Sehwag scored from 58 balls in the second Qualifier match.

Screen Shot 2014-06-04 at 1.38.45 PM.png

Jamie built a Bowler gird analytic that shows the line, length, and results of each of a bowler’s deliveries during a match by using a sub report that was reformatted as a multi-column report, as this enabled the records to be displayed in a grid format. The Bowler grid also uses a command object that unionizes two record sets. The first is based on the user-entered parameters of the Bowler’s name and match date. The second returns a dummy record for each bowling delivery length name and each bowling delivery line name to ensure the base 56 grid cells are always displayed. For an in-depth description of how Jamie built this Bowler grid please read his blog post.


For an example of a Bowler grid check out how Ravindra Jadeja recorded the best individual bowling performance of the tournament by taking four wickets while only conceded 12 runs in four overs during the Super Kings’ attack against the Kolkata Knight Riders in the 21st match of the tournament.

Screen Shot 2014-06-04 at 1.50.33 PM.png

For the Bowler vs Batsman analytics, which compares the economies of a bowler and the strike-rates of a batsman based on the line and length of ball the delivered or faced, Jamie used a single sub multi-column report that first returned the bowler’s economies and then returned the Batsman’s strike-rates. This analytic uses a unionization of data sets similar to the other two analytics but also gives the end user the option of comparing the bowler and batsman over a single match or an entire season. It also allows the possibility to restrict the displayed results to just the instances when the bowler delivered to the batsman.


Below is a Bowler vs Batsman analytic that shows the breakdown of the economies of Kings XI Punjab’s best bowler, Akshar Patel and their best batsman, Glenn Maxwell. Patel’s economy rate of 6.13 was the best for any bowler who regularly delivered for their team and Maxwell’s 187.75 strike rate was the best for any Batsman who scored more than 200 runs in the tournament.

Screen Shot 2014-06-04 at 1.56.04 PM.png

Check out the video below for even more information about how Jamie created these informative Cricket analytics using SAP Crystal Reports.

Screen Shot 2014-06-04 at 5.28.23 PM.png

Keep your eyes peeled for more visualizations created in SAP Lumira and SAP Crystal Reports using Indian Twenty20 data processed by SAP HANA. Please check back for additional post season analysis. Also, watch out for the conclusion of our series of tutorial videos on how we utilize SAP HANA in this Cricket demo.


SAP HANA Academy – over 500 free hands-on tutorial videos on using SAP HANA.

Check out all of the Indian Premier Twenty20 Match by Match Analysis here.


-Tom Flanagan

SAP HANA Academy

Follow @saphanaacademy

Follow @thomflanagan

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Mark Richardson

    Surprised to see you running what appears to be Crystal Reports 2013 content against HANA via Commands for data-access.

    Every thing we have been hearing at SAPPHIRE this week is that we should only be using the new Crystal Reports for Enterprise (CR4E) when creating highly-formatted reports against HANA.

    Any details or documents on the Pros & Cons of using one version of Crystal Reports vs the other (ie. CR2013 vs CR4E) when reporting against HANA…?

    (0) 
    1. Jamie Wiseman

      hi Mark, thanks for the comment.

      crystal reports 2013 and cr4e are different products with different features and functionality. see this doc for more info on the two if you need to decide when and why to use either. you may also wish to check the cr4e what’s new in 4.1

      to be perfectly honest i use cr 2013 instead as it provides the flexibility and functions that i need to design the reports that i want. i would personally disagree with the statement to use only cr4e against hana for several different reasons some of which are covered in the doc referenced above. i was the one that developed the reports for the cricket project and am confident that the reports that were made for the project could only be done in cr 2013 (and earlier versions like 2011, 2008 as well). Tom had some pretty specific wishes / requirements / designs for what he was looking for as far as the cricket analytics. if you want to see more on how these reports were developed i’ve created blog posts here and here.

      recommending only cr4e would also discourage all those who have standalone report developer products (2013 etc.) from developing reports against hana. cr4e is an enterprise product and not all shops are enterprise users. to use a standalone product against hana there are a bunch of videos at the sap hana academy that can help one get started including installing the required 32 bit hana db client.

      since you mentioned Commands, with cr 2013 it’s pretty easy to optimize each report using a Command object, which is processed at the database level, as there’s no need for an additional layer between the report and the database. again it comes down to personal preference as i like to use a custom query for each report that i build and keep it in the report object itself.

      so it becomes a matter of what environment you’re using, whether you want a semantic layer, what functionality & features you need to fulfill your design specs, etc.

      -jamie

      (0) 
      1. Mark Richardson

        Jamie :

        Good to know. That level of detail might need to be spread-around a little more within SAP.

        The materials at SAPPHIRE related to HANA were only talking about CR4E as the client.

        Thanks,

        Mark

        ______________

        (0) 

Leave a Reply