This page has 0 threads | Add post

Courseware

Advanced SSRS course manual

Here are the chapters included in this course manual (we use exactly the same manuals for our classroom and online Reporting Services courses).

Click here to download a sample of the first two chapters of this Advanced SSRS manual, or review how to license our courseware.

Chapter 1 - GROUPING IN TABLES▼

Section	Title	Subsections	Page
1.1	Grouping Basics		7
1.2	Grouping Rows in a Table	Step 1 – Build a Basic Table Step 2 – Apply Grouping to the Detail Row Step 3 – Choose Which Field to Group By The End Result	8
1.3	Removing Groups	Deleting Groups in the Grouping Pane Deleting Groups in a Table	10
1.4	Formatting a Grouped Table	Adding Group Headers and Footers Rearranging Group Titles Adding Group Totals	11
1.5	Multiple Groups	Adding a New Parent Group Adding a Child Group	14
1.6	Page Breaks	Inserting Page Breaks between Groups	15
1.7	Collapsible Groups	Step 1 - Hiding Detail Rows Step 2 - Setting Toggle Items	16

Chapter 2 - EXPRESSIONS▼

Section	Title	Subsections	Page
2.1	Overview of Expressions	Choosing to Create an Expression The Expression Builder Dialog Box Referring to Dataset Fields Referring to Report Items Inserting Functions	18
2.2	Ad-Hoc Table Columns	Creating an Ad-Hoc Column Expression	21
2.3	Calculated Fields	Adding a Calculated Field to a Dataset Using a Calculated Field Adding Calculated Fields to a Query	22
2.4	Working with Numbers	Basic Arithmetic Numeric Functions	24
2.5	Conditional Functions	The IIf Function The Switch Function Comparison Operators Logical Operators	25
2.6	Working with Text	Concatenating Text Text Functions	27
2.7	Working with Dates	Returning the Current Date Date Functions Intervals for Date Functions Formatting Dates	28
2.8	Aggregating Data	Aggregate Functions Adding Aggregates the Quick Way Changing the Aggregate Function Specifying the Scope	30
2.9	Lookup Functions	The Lookup Function The LookupSet Function	32
2.10	Built-In Fields	Built-In Fields in the Expression Builder Built-In Fields in the Report Data Window	33
2.11	Placeholders	Creating a Placeholder	34

Chapter 3 - ADVANCED EXPRESSIONS▼

Section	Title	Subsections	Page
3.1	Scope	Dataset Scope Data Region Scope Row and Group Scope Entering Aggregate Formulae	35
3.2	Row Numbers	The RowNumber Function Numbering over Groups The Nothing Scope Alternate Row Colouring	38
3.3	Running Totals		40
3.4	Extra Functions		41

Chapter 4 - VARIABLES▼

Section	Title	Subsections	Page
4.1	Using Variables	The Need for Variables Creating a Variable Referencing Variables	42
4.2	How Variables are Calculated		44
4.3	Group Variables	Creating a Group Variable Referring to Group Variables in Expressions	45

Chapter 5 - EMBEDDING CODE IN REPORTS▼

Section	Title	Subsections	Page
5.1	Overview – Ways to Reuse Code	Our Example Strategies to Use Writing your Expression in SQL Writing an Expression in SSRS	47
5.2	Creating an Embedded Function	Step 1 - Designing your Function (Data Types) Step 2 – Writing your Function Step 3 – Embedding your Code in a Report Step 4 – Referencing your Code	49
5.3	Other Ways to Write Code	Writing Code within RDL Writing Code within a Class Library	52

Chapter 6 - SQL SERVER DATABASE ACCESS▼

Section	Title	Subsections	Page
6.1	Server Explorer / Management Studio	Comparison of SSMS and Server Explorer	54
6.2	Accessing Server Explorer		55
6.3	Accessing Management Studio (SSMS)	Starting to Use Management Studio Object Explorer Useful Start-up Options	56

Chapter 7 - VIEWS▼

Section	Title	Subsections	Page
7.1	The Need for Views		58
7.2	Creating Views using the Designer	Starting the Designer Choosing Columns Sorting and Filtering Adding Grouping Executing a View Saving and Closing Views Seeing your View in Object Explorer Running a View Changing a View	59
7.3	Scripting Views	Creating a New View Changing an Open View in Script Changing a View’s Script from Object Explorer	64
7.4	Switching between the Designer and Scripting		66
7.5	Using Views in Datasets		67
7.6	Using Views to Rename Columns		68
7.7	Pros and Cons of Views	Advantages of Views Disadvantages of Views	69

Chapter 8 - PARAMETERS▼

Section	Title	Subsections	Page
8.1	Introduction to Parameters	Displaying the Parameters Pane	70
8.2	Report Parameters	Creating a Report Parameter Using a Report Parameter in a Filter Parameter Data Types Dealing with No Rows Displaying Parameter Values in the Report	71
8.3	Query Parameters	Writing a Query using Parameters	74
8.4	Organising Parameters	Changing the Order of Parameters Using the Parameters Pane	75
8.5	Default Values	Typing in a Default Value Calculating Default Values Getting Default Values from a Dataset	76
8.6	Null Values	Allowing Null Values Dealing with Nulls in Filters	78
8.7	Drop Down Lists	Manually Entering Available Values Using a Dataset to Populate a List	79
8.8	Multi-Value Drop Down Lists	Allowing Multiple Values Using Multiple Values in Filters Using Multiple Values in Queries Displaying Multiple Values in a Report	81
8.9	Cascading Drop Down Lists	Creating Cascading Drop Down Lists Using Cascading Drop Down Lists	83
8.10	Conditional Formatting with Parameters	Parameters in Formatting Expressions	86

Chapter 9 - STORED PROCEDURES▼

Section	Title	Subsections	Page
9.1	Introduction to Stored Procedures		87
9.2	Creating Stored Procedures	Typing in a Stored Procedure Executing the Script to Create your Stored Procedure Viewing your Stored Procedure Basing a Report on your Stored Procedure	88
9.3	Altering a Stored Procedure	Altering an Open Stored Procedure Altering a Procedure in a Database The Need to Refresh Fields	90
9.4	Executing Stored Procedures	Refreshing your Local Cache Altering and Executing a Stored Procedure Together Selecting a Stored Procedure Name to Run It	92
9.5	Renaming and Deleting Stored Procedures	Renaming/Deleting a Procedure with the Menu Deleting a Procedure in Script Renaming a Procedure in Script	94
9.6	The Cheat’s Stored Procedure		95

Chapter 10 - PARAMETERS IN STORED PROCEDURES▼

Section	Title	Subsections	Page
10.1	Overview	Syntax of Parameters Parameters and SSRS Reports	96
10.2	Creating Procedures with Parameters	Step 1 – Specifying the Parameters Step 2 – Coding the Parameters Step 3 – Referencing the Parameters Using Text Wildcards with Parameters	97
10.3	Testing Procedures using Parameters	Passing Values to Parameters in Order Testing a Procedure using Parameter Names Right-clicking to Test a Procedure	99
10.4	Stored Procedure Parameters in SSRS Reports		101
10.5	Allowing Null Values	How Null Values are Passed from SSRS The Workaround – Trap Nulls in your WHERE Condition	102
10.6	Parameter Data Types (Numbers)	Integer Data Types Decimal and Numeric Types The Float Type	104
10.7	Parameter Data Types (Text)	Types of Character Storage Variable Length Data Types Fixed Length Data Types	105
10.8	Parameter Data Types (Dates/Times)		106

Chapter 11 - STORED PROCEDURES AND DROPDOWNS▼

Section	Title	Subsections	Page
11.1	Basic Dropdowns using Stored Procedures	Step 1 – Creating the Two Datasets Needed Step 2 – Adding the Datasets Step 3 – Configuring the Dropdown Parameter	107
11.2	Cascading Dropdowns	The Datasets and Parameters Needed The Three Stored Procedures Needed	109
11.3	Removing SELECT A VALUE	How Dropdowns Look by Default How the Solution will Work Creating the Dataset with Additional Top Row Setting the Default Parameter Value	110

Chapter 12 - MULTIVALUE DROPDOWNS AND PROCEDURES▼

Section	Title	Subsections	Page
12.1	Multivalue Parameters	How Multivalue Parameters are Stored	113
12.2	Splitting Comma-Delimited Strings	Creating a Table-Valued Function to Split the Parameter String Testing the Table-Valued Function	114
12.3	Creating a Multivalue Parameter	Step 1 - Creating the Dropdown Dataset Step 2 - Creating the Main Report Dataset Step 3 – Adding the Datasets to the Report Step 4 – Configuring the Parameter Created Step 5 – Showing the Values Chosen	116
12.4	Coping with Multivalue Choices	Intercepting SELECT ALL Setting a Maximum Number of Choices	119

Chapter 13 - DRILLTHROUGH REPORTS▼

Section	Title	Subsections	Page
13.1	Overview of Drillthrough Reports	The Example for this Chapter	121
13.2	Creating the Child Report	Step 1 - Create the Dataset Step 2 - Hide the Parameter Step 3 - Design the Report	122
13.3	Creating the Parent Report	Step 1 - Create the Dataset Step 2 - Design the Report	123
13.4	Creating Report Actions	Step 1 - View the Action Properties Step 2 - Configure the Action to Open a Report	124
13.5	Using Drillthrough Reports	Running the Parent Report Returning to the Parent Report	125
13.6	Multiple Parameters	Creating Child Reports with Multiple Parameters Creating the Parent Report	126

Chapter 14 - IMPROVING REPORT NAVIGATION▼

Section	Title	Subsections	Page
14.1	Bookmarks	Step 1 - Creating the Report Step 2 – Creating the Bookmarks to Jump to Step 3 - Creating the Links to Each Row Step 4 - Creating the Link Back to the Top Step 5 – Creating Tooltips	127
14.2	Navigation Maps	Creating a Document Map Nested Document Maps Exporting Document Map Reports to Excel Setting Titles	131

Chapter 15 - DYNAMIC REPORTS (DATA)▼

Section	Title	Subsections	Page
15.1	Dynamic Data Sources		134
15.2	Dynamic Datasets	Step 1 - Creating the Base Report Step 2 - Creating the Parameter Step 3 – Make the Report Variable	135
15.3	Dynamic Stored Procedures	Vary the Name of the Stored Procedure Passing a Value to a Stored Procedure Parameter	137

Chapter 16 - DYNAMIC REPORTS (DESIGN)▼

Section	Title	Subsections	Page
16.1	Examples in this Chapter		138
16.2	The Underlying Principle		139
16.3	Example – Choosing the Grouping Field	Step 1 – Create the Report Template Step 2 – Create the Parameter Step 3 - Choose the Grouping Field Step 4 – Make any References to the Fixed Field Dynamic	140
16.4	Example – Choosing the Row and Column Field for a Matrix	Step 1 – Create the Report Template Step 2 – Create the First Dropdown Parameter Step 3 – Creating the Second Dropdown Parameter Step 4 – Showing the Second Parameter Step 5 – Changing the Grouping Fields Step 6 – Update other References	143
16.5	Example – Choosing the Data Field	Creating the Dropdown Parameter The Data Field and Title Expressions The Number Formatting	147

If you book a place on one of our courses you will automatically get access to the relevant courseware manual online, and also have the option to receive a paper copy of it.

This page has 0 threads

Add a new post

Choose software

SSRS course manuals