This page has 0 threads | Add post

Exercises

Finding our exercises useful? Please consider helping us to share more!

Software ==>	SSRS Reporting Services (99 exercises)
Topic ==>	Expressions (8 exercises)
Level ==>	Harder than average
Subject ==>	SSRS training

Use expressions in an SSRS report to calculate a variety of values related to the season of a film.

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.

Before you can do this exercise, you'll need to download and unzip this file . Once you've done this:

Go into SQL Server Management Studio;
Open the SQL file you've just unzipped (you can press CTRL + O to do this); then
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).

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.

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:

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:

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:

Feel free to create a less offensive colour scheme.

Import four appropriately-named images into the report:

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):

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:

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:

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.

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.

You can unzip this file to see the answers to this exercise, although please remember this is for your personal use only.

You can find other training resources for the subject of this exercise here:

This page has 0 threads

Add a new post

EXERCISE TOPIC

SSRS EXERCISES

SSRS REPORTING SERVICES EXERCISES

Use expressions in an SSRS report to calculate a variety of values related to the season of a film.

Head office

London

Manchester