Exercise: Calculate Seasonal Values for Films

This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.

The answer to the exercise will be included and explained if you attend the relevant Wise Owl course (sadly, only in the UK for now).

Category ==> SSRS 2016  (33 exercises)
Topic ==> Expressions  (6 exercises)
Level ==> Harder than average
Before you can do this exercise, you'll need to download and unzip this file (if you have any problems doing this, click here for help). Once you've done this:
  1. Go into SQL Server Management Studio;
  2. Open the SQL file you've just unzipped (you can press CTRL + O to do this); then
  3. Execute this script.

This will generate the database that you'll need to use in order to do this exercise (note that the database and script are only to be used for exercises published on this website, and may not be reused or distributed in any form without the prior written permission of Wise Owl).

You need a minimum screen resolution of about 700 pixels width to see our exercises. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Create a report called Awards Season and add a data source which connects to the Movies database.

Add a dataset which selects the fields shown below for films with at least one Oscar nomination.

Dataset fields

You can either add a WHERE clause to the query or add a filter to the dataset to return the Oscar-nominated films.

 

Add the following calculated fields:

  • OscarWinRate - divide Oscar wins by Oscar nominations.
  • CostPerNomination - divide budget by Oscar nominations.
  • CostPerWin - divide budget by Oscar wins (remember that some films didn't win any Oscars).

Insert a table into the report and assign fields so that it resembles the example below:

Basic table

Apply some basic number formatting to make the data readable.

Add a field called Season which calculates the name of the season in which each film was released:

  • Spring - the release date is between March and May.
  • Summer - June to August.
  • Autumn - September to November.
  • Winter - December to February.

You could add fields to the table to test your calculation is working:

Season test

Check that you see the correct season name next to each film.

 

Use expressions to calculate appropriate formatting for each film based on the season in which it was released.  Use the example below as a guide:

Conditional formatting

Feel free to create a less offensive colour scheme.

 

Import four appropriately-named images into the report:

Import images

Right-click the Images folder in the Report Data window to import pictures. Make sure the image names match the season names!

 

Insert a column into the table and add an Image report item to it.  Use an expression to calculate the name of the image to display (make it equal to the name of the season):

Images in table

You may need to adjust the column width.

 

Add a parent group to the table using the Season field and apply conditional formatting to the header and footer rows so that it looks similar to this:

Grouped table

It should take you some time to achieve this!

Add expressions to the footer row for the last three columns in the table to calculate the average:

  • Win rate - The sum of Oscar wins divided by the sum of nominations.
  • Cost per nomination - The sum of the budget divided by the sum of nominations.
  • Cost per win - The sum of the budget divided by the sum of wins.

The results for Spring are shown below:

Averages

Apply some number formatting to make the results readable.

Add a filter to the dataset so that it returns only films that were released in the same month as the current date.

Filtered table

Running the report in November should return only films released in that month. You could apply interactive sorting as a final flourish.

 

Close and save the report.

This page has 0 threads Add post