Reporting with Microsoft SQL Server 2012

Preface 1

Chapter 1: Getting Started with Reporting 5

Standard reporting with SSRS 5

Self-service reporting with Power View 8

Power View limitations 9

Reporting scenarios 10

Scenario 1 10

Scenario 2 11

Scenario 3 11

Scenario 4 11

Scenario 5 12

Scenario 6 12

Scenario 7 12

Scenario 8 13

Scenario 9 13

Scenario 10 13

Scenario 11 13

Scenario 12 14

Scenario 13 14

Summary 14

Chapter 2: SSRS – Standard Reporting 15

Primary components of a report 16

Data source 16

Dataset 18

Report items 21

Additional components of a report 28

Report parameters 28

Expressions 31

Actions 32

Custom code 33

Report development environment 34

Server features and functionality 35

Choosing an installation mode – SharePoint Integrated versus Native 35

Scheduling 36

Subscriptions 37

Report snapshots 39

Caching 42

Data alerts 43

My Reports (Native mode only) 46

Linked reports (Native mode only) 47

Consuming reports 47

Online 47

Offline 48

Data feed 48

Extensibility 48

Security 49

Roles and permissions 49

Securing Report Server objects 50

Data security 51

Summary 52

Chapter 3: Development Activity with SSRS 53

Prerequisites 53

Tutorial scenario 54

Creating a Reporting Services project 54

Creating a report object 55

Creating a shared data source 56

Adding reference to shared data source 58

Creating a dataset 59

Adding a report item 61

Deploying a report project 66

Summary 68

Chapter 4: Power View – Self-service Reporting 69

Getting started 70

PowerPivot 72

Business Intelligence Semantic Model 73

Power View within SharePoint 74

Setup 74

Tabular model connection 75

Starting Power View connected to a tabular model connection 77

Multidimensional model connection 78

Starting Power View connected to a multidimensional model connection 78

Power View within Excel 2013 79

Setup 79

Data models 79

Starting Power View in Excel 80

Importing data into Excel 80

Adding data to a worksheet and inserting data into Power View 81

Not adding data to a worksheet and inserting data into Power View 81

Adding data to a worksheet and using PowerPivot 82

Not adding data to a worksheet and using PowerPivot 82

Data visualizations 83

The user interface 84

Enhancing data models 88

Deploying and sharing reports 92

Presentation modes 93

Reports with multiple views in Power View 93

Adding multiple views 94

Navigating among views 94

View filters 94

View preview images 95

Chart highlighting, slicers, and filtering 95

Chart highlighting 95

Slicers 96

Filtering 98

View-level/sheet-level filters 98

Visualization-level filters 98

Basic filters 99

Advanced filters 100

Search in filters 101

Sorting 101

Export reports to Microsoft Office PowerPoint 102

Design tips 103

Undo/Redo 103

Arranging visualizations 103

Fit to window 103

Pop out 104

Summary 104

Chapter 5: Development Activity with Power View 105

Prerequisites 105

Tutorial scenario 106

Creating a BI Semantic Model (BISM) connection 106

Opening the Power View design interface 108

Creating bar charts 109

Creating pie charts 111

Creating column charts 113

Adding a slicer 114

Deploying reports to SharePoint 115

Summary 117

Index 119