WISE OWL EXERCISES
SSRS REPORTING SERVICES EXERCISES
- Designing a Simple Report (3)
- Data sources and datasets (1)
- Tables (5)
- Grouping tables (6)
- Expressions (8)
- Pages and printing (2)
- Parameters (9)
- Indicators (3)
- Gauges (4)
- Matrices (5)
- Charts (6)
- Data bars and sparklines (2)
- Lists (4)
- Subreports (2)
- Revision of expressions (3)
- Variables (3)
- Embedding code (3)
- Basic Custom Assemblies (2)
- Examples of custom assemblies (1)
- Customising reports (2)
- Using SQL views (3)
- Stored procedures in SSRS (2)
- Stored procedure parameters (2)
- Dropdowns with procedures (9)
- Multivalue parameter procedures (1)
- Improving report navigation (2)
- Linking reports (drilldown) (4)
- Dynamic reports (2)
SSRS Reporting Services | Expressions 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 one of more of the courses listed below!
- 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).
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.
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.