Index

A

AddNode Method, 237

ATOM, 11

Authentication layer, 10

Average Length of Stay, 436

B

BIDS and Visual Studio 2012. See Report deployment

Bookmark link, 147–149

Building reports, 125

chart addition, 171–174

configuration and group variables, 177–178

Employee Service Cost report, 125–126

filter application, 170–171

final touches, 181–183

from Scratch, 129–133

gauge control, 179–181

interactivity

bookmark link, 147–149

document mapping, 137–138

hyperlink actions, 143–147

hyperlink formatting and tooltips, 157

jumping to report, 151–156

sorting, 141–143

types, 136–137

URL link, 149–151

visibility, 138–140

multivalued parameters (see Multivalued parameters)

output with formatting, 133–134

parameters with stored procedures

BranchID field, 160

ChargeServiceStartDate field, 163

datasets addition, 161–162

Emp_Svc_Cost, 158–159

EmployeeTblID field, 160

No parameter values, 161

NULL values, 159–160

parameter value queries, 164

populated parameter selections, 163

query parameter, 158

query-assigned values, 160

Service Year field, 164

ServiceLogCtgryID parameter, 163

ServiceMonth parameter, 164

ServicesLogCtgryID property, 163

year and month values, 165

Report Wizard

corporate style, 128

data source, 126

generated report, 129

group and details selections, 128

Query String area, 127

tabular/matrix form, 127

subtotals, 134–136

Tablix elements, 175–177

Business intelligence (BI), 1, 6–7

dashboard, 394–397

data alerts, 397–399

report deployment (see Report deployment)

SQL analysis services

cube wizard, 362

graphical MDX query builder, 362

health-care application (see Health–care application)

Microsoft SharePoint 2010, 373

OLAP solution, 362

tabular models, 362

VertiPaq Engine, 362

stand–alone installation (see Stand–alone installation)

Business Intelligence Center, 388–389

Business Intelligence Development Studio (BIDS), 6, 13–14, 40–43

Business Intelligence Semantic Model (BISM), 362

C

Central Administration, 384

Chart report creation

3-D Visual Effect box, 444–445

Active Patient Census, 445

Chart Title, 444

data labels, 445

data region, 441–442

Discharge Date, 443

drag and drop data, 442

Patient Count formula, 442–443

Report Builder 2.0 Wizard

data source and dataset, 454–457

default Ocean style, 452

Emp_Svc_Cost stored procedure, 450–451

Estimated_Cost field, 460

execution time and title, 462

final formatting report, 463

finished report, 453

Insert tab, 457–458

layout selection screen, 452, 453

Microsoft’s Reporting Services ad-hoc tool, 463

New Table/Matrix wizard, 449

NULL values, 459

Preview button, 459

query design, 450

Ribbon technology, 447–448

Service Type Estimated Cost, 452

setting data source and connection string property, 449–450

SQL Server 2008 R2/SQL Server 2012, 449

SSRS 2008, 447

User ID, 461

Visit Count, 458

Report Builder 3.0 (see Report Builder 3.0)

Report Layout tab, 441

Report Manager, 446

report parts, 473

Command-line utilities, 12, 14

Comma-separated values (CSVs), 11

CreateCatalogItem method, 230, 237, 239, 240

CreateReportEditSession method, 231

Custom .NET code

class library project, 195–198

custom assembly

asserting permission, 202

code reuse, 195

debugging, 207–210

deploying, 198

flexibility, 195

language neutrality, 195

Named Permission Sets, 200–201

productive development environment, 195

reference adding, report, 202–207

rssrvpolicy.config file, 199

security control, 195

SSRS 2012, 199

task separation, 195

troubleshooting, 210

embedded code, 186, 194

ExceedMaxCosts function, 187

Boolean return value, 190

Code member, 189

Code.ExceedMaxCost method, 189

color property, 190, 191

color selection list, 192

conditional expression, 190

EmployeeServiceCost-NoCode.rdl file, 188

expression adding, report, 190

expression editor, 192

IIF native SSRS function, 190

Pro_SSRS.rds file, 188

report with the embedded code, 193

Visual Studio 2010 BI project creation, 187, 188

Custom clients, 12, 15

D

Dashboard, 394–397

Dashboard-style report objects, 89

chart data region, 89

Chart Start.rdl, 90, 91

Chart.rdl, 95

Emp_Svc_Cost stored procedure, 90

preview, 93, 94

properties, 91, 93

RDL ouptput, 94

SSRS, 90, 91

three fields, 91, 92

data bar, 89

Chart Data dialog, 111, 112

Data Bar Start.rdl, 110

Data Bar.rdl, 115

object recognition, 110

preview, 113, 114

RDL, 115

Series Properties fill options, 112, 113

type selection, 110, 111

gauges, 89

Gauge Start.rdl, 100

preview mode, 102

range, radial, 101

RDL output, 103

image, 89

data set properties, 96

DocumentImage, 95, 97

Image Start.rdl, 95

preview report, 99

properties, 98

RDL elements, 99–100

source selection, 96, 97

types, 95

indicator, 89

Preview tab, 122

properties, 120, 121

RDL file, 123

type wizard, 120

visual, 119

map object, 89

choose map visualization, 105, 106

data visualization, 107, 108

ESRI shapefiles, 103, 104

field specification, 106, 107

gallery, 103

layer wizard, 105

Map.rdl, 110

Preview tab, 108, 109

RDL output, 109

SQL Server Spatial query, 104

sparkline, 89

chart data value, 116

control, 115

preview, 118

RDL, 119

Series Properties window, 117

Sparkline Start.rdl, 115

Sparkline.rdl, 119

Data alerts, 397–399

Data processing, 10–11

Data-driven subscriptions

advantage, 303

creation, 305–308

daily activity report, 303, 304

GETDATE function, 304

output, 305

T-SQL Query, 304

Data source view creation

Add New Data Source, 409

additional tabs, 415–416

friendly names, 409

graphical query designer, 411

multiple tables, 409

named query, 410, 411, 413

New Named Query button, 410

patient census query, 411–414

Pro_SSRS_2008R2 database, 409

report designers, 409

report model

model generation rules, 418

patient census report model, 421–422

Pro SSRS 2008R2, 417

process completed successfully, 419–420

project property, 420–421

Report Model Wizard, 417, 418

SSRS report builder role, 422–423

update Statistics page., 419

Set Logical Primary Key, 415

Document mapping, 137–138

Domain Controller, 374

E

EmployeeServiceCost.rdl, 241

Environmental Systems Research Institute, Inc. (ESRI), 4

Excel 2002 and 2003, 11

F

Filters, 170–171

Format table, 154

G

Gauge control, 179–181

H

Health Insurance Portability and Accountability Act (HIPAA), 16

Health-care application

Active Directory users and computers, 340, 341

analysis services data source, 368–369

browser, 341

Health-care application (cont.)

Clinical Reports root folder, 345

connection screen, 342, 343

content filtering, 352

content management permissions, 341

content manager, 341

Daily Activity report, 346

data source authentication method, 345

data warehouse database, 362

db_datareader permissions, 345

default security group, 348, 349

error message, 347, 348

graphical MDX query builder

Average Referral to Admission report, 372

Average Time to Admission chart, 371

Design Mode toolbar button, 370

dimensional and measure elements, 369, 370

graphical design mode, 369

MAX function, 370

MIN function, 370

output, 369, 370

Referral to Admission Chart, 372

warning message, 371

Group Policy settings, 340

HW_Analysis, 362

Matrix-style reports, 366

MultiDimensional expressions, 363

My Reports feature, 341

New Role Assignment form, 349

Patient Referral cube, 362

Average Referral to Admission report, 367

branch, 364

Browser tab, 364

County, 364

cube browser, 367

Data Source Designer, 364

Date of Admission, 364

design mode, 364

diagnosis, 364

OLAP database, 364

payer, 364

Referral Source, 364

Referral to Admission Chart report, 367

structure, 362, 363

Registered Nurse folder, 345

Report Builder role, 341, 343, 344

report objects, 346, 347

reports publishing, 341

RN_DS data source, 345, 346

RNsecurity group, 340

RN Windows security group, 351

roles and system roles, 342

Rubik’s cube, 364

SSIS, 362

test account, 351, 352

Time to Admission, 365–366

users task, 342

windows integrated security option, 345

HTML, 11

text formatting, 5

code output, 154

Hyperlink

actions, 143–147

formatting and tooltips, 157

I, J, K

Integrated development environment (IDE), 13

Error List window, 44

Properties window, 44

Report Data window, 44

sample IDE configuration, 45

toolbox, 44

IntelliSense, 59

Interactive sorting, 141–143

ISNULL function, 34

L

Layer 2 tunneling protocol (L2TP), 330

ListChildren method, 235, 236

Lookup functions, 6

M

Matrix report creation

Average Length of Stay, 437

blank matrix report, 436

data region, 435

Discharge Reason field, 436

Discharge Year parameter, 440–441

filter data, 439–440

Microsoft Excel, 435

number formatting, 438

Microsoft Directory Services, 54

Microsoft Management Console (MMC), 39

Microsoft Office Ribbon technology, 45

Microsoft SharePoint 2010, 373

Microsoft Word, 5, 11

Microsoft’s Reporting Services ad-hoc tool, 463

MIME HTML (MHTML), 11

Multivalued parameters

Emp_Svc_Cost, 169

fn_MVParam, string-parsing function, 169

generated report, 170

IN clause, 166, 167

MVP, 166, 167

NULL values, 166

query/stored procedure, 165

selection, 168

table-valued function, 168

WHERE clause, 165, 166, 169

wrapping SQL statements, 168

Year and Month parameters, 166

N

NULL rendering extension, 11

NULL values, 36

O

OverwriteDataSets, 221

OverwriteDataSources, 222

P

Parameterized stored procedure

column and table aliasing, 35

CREATE PROCEDURE command, 32

database expansion, 32

Emp_Svc_Cost, 32–34

ISNULL function, 34

performance and indexes, 34–35

procedure testing, 35–36

SELECT statement, 32

Patient census report

admission and discharge information, 403

BIDS 2008 R2, 405–406

chart report (see Chart report creation)

data source views (see Data source view creation)

data source Wizard, 406–408

Data Sources folder, 421–422

health-care industry, 403

matrix report (see Matrix report creation)

process completed successfully, 419–420

Report Builder 1.0 (see Report Builder 1.0)

Report Builder 3.0 (see Report Builder 3.0)

report model, 404, 420

table report (see Table report)

Patient Name detail textbox, 152

Patient Recertification Listing report, 298

Patient Survey Letter, 153, 156

Performance-tuning queries, 35

Placeholder Properties dialog, 155

Point-to-Point tunneling protocol (PPTP), 330

Portable Document Format (PDF), 11

Q

Query design, 19

advanced query

COUNT and SUM functions, 28

employee cost query, healthcare database, 27

optimizing performance, 30–32

ORDER BY PATID clause, 28

required data output, 26

SELECT clause, 27

SQL Server machine, 28

SSMS, 28–30

dataset configuration, 19

graphical query

sample output, 26

single-user execution scenario, 26

SSMS, 23–25

top ten patient diagnoses code, 25

parameterized stored procedure

column and table aliasing, 35

CREATE PROCEDURE command, 32

database expansion, 32

Emp_Svc_Cost, 32–34

ISNULL function, 34

performance and indexes, 34–35

procedure testing, 35–36

SELECT statement, 32

sample relational database

Pro_SSRS database, 20

schema design, 20–21

sp_FieldInfo, 21–23

Query parameter, 158

R

Remote desktop protocol (RDP), 330

Report Builder 1.0, 46

BIDS/Visual Studio, 423

chart report (see Chart report creation)

Report Builder 1.0, 4.6 (cont.)

ClickOnce application installation screen, 425

NET Framework 2.0, 424

Report Builder URL, 424

report designer, 423

Report Manager, 424

report model selection, 425–426

SQL Server 2008 R2, 424

table report template, 426–427

Report Builder 2.0 Wizard, 45

connection string property, 450

data source and dataset, 454–457

default Ocean style, 452

Emp_Svc_Cost stored procedure, 450–451

Estimated_Cost field, 460

execution time and title, 462

finished report, 453

Insert tab, 457

layout selection screen, 452

Microsoft’s Reporting Services ad-hoc tool, 463

New Table/Matrix wizard, 449

NULL values, 459

Preview button, 459

query design, 450

Ribbon technology, 447–448

Service Type Estimated Cost, 452

setting data source property, 450

SQL Server 2008 R2/SQL Server 2012, 449

SSRS 2008, 447

User ID, 461

Visit Count, 458

Report Builder 3.0, 12, 14–15, 45, 219

BIDS/Visual Studio integrated tools, 218

data sources, 464

dataset caching, 464

drill, 465

Employee Service Cost by branch and patient state, 465–472

loaded report, 219, 220

Map report, 464

Pearl menu, 220

RDL file, 220

report items, 464

report parts, 464

save options, 221

Report creation

adding a data source, 406–408

BIDS 2008 R2, 405–406

chart report (see Chart report creation)

data source view (see Data source view creation)

matrix report (see Matrix report creation)

patient census model, 404

Report Builder 1.0 (see Report Builder 1.0)

table report (see Table report)

user feedback, 402

Report Definition Language (RDL), 1

Active Directory querying, 54

data source creation, 47–49

dataset creation, 49–53

expressions, 58–59

filters, 56–57

find-and-replace method, 45

OLE DB, 54

query and report parameters, 54–56

report adding, 46–47

updating and uploading

default parameter value, 288–290

Download and Replace links, 287

Notepad, RDL file, 287, 288

options menu, 285, 286

properties screen, 286, 287

ReportParameter, 287

XML-based schema, 45

Report deployment

Actions link button, 393

application running, 239–242

Average Referral to Admissions, 392

configuration

Configuration Manager, 223–224

properties, 221–223

data source definition, 391, 392

PatRef_DS data source, 390

permission-related issues, 391

Report Builder 3.0 (see Report Builder 3.0)

Report Manager (see Report Manager)

report server web service (see Report server web service)

Reports Library, 388

rs.exe utility

CreateFolder method, 229

report and data source, 230

RSS file configuration, 226–227

RSS file contents, 227–229

VB .NET code, 226

SharePoint Document Center main page, 388

SharePoint document features, 390, 391

SharePoint, rendered report, 392–394

Solution Explorer, 224–225

target URL properties, 389, 390

Windows Credentials, 391

Report layout, 61

list data region

design area, 63

free-form objects, 63

List Start, 63

patient group, 67, 68

Patient_Name grouping, 66, 67

preview, 64–66

RDL file, 68–69

Row Groups, 65

Tablix properties, 65, 66

ungrouped fields, 64

Matrix data region, 86

cost estimation, 87

data field grouping, 85

Matrix Start.rdl, 85, 86

Matrix.rdl, 88

preview, 87

RDL output, 88

page setup, 61–62

rectangle data region

default currency format, 82

formatting report, 82

free-form container, 80

preview, 84

RDL output, 84

Rectangle Start.rdl, 81, 82

Rectangle.rdl, 85

Textbox report object, 80

report objects, 62–63

table data region

data organization, 75

detail row, 76

Estimated Cost expression, 78

Group Properties, 77, 78

preview, 79

RDL, 79

Row Group creation, 76, 77

Table Start.rdl, 75

Table.rdl, 80

Textbox control

multiple format output, 74, 75

patient name value, 73, 74

plane text date formatting, 72, 73

Rich Text Formatting, 69

TextBox properties, placeholder, 70, 71

TextBox_Start report, 70

value properties, 71, 72

Report management

data source, 290–292

execution auditing and performance analysis

built-in logging feature, 308

ExecutionLog Table, 309–311

performance monitoring, 313–315

Report Execution Log, 312–313

SSRS deployment, 308

SSRS logging, 309

RDL (see Report Definition Language)

report history, snapshots

caching, 296

data-driven subscriptions (see Data-driven subscriptions)

History tab, 295

report processing, 295–296

snapshot options, 293, 294

standard subscriptions (see Standard subscriptions)

types, 293

shared schedules

creation, 281–282

report configuring, 282–284

set up, 280–281

SSRS (see SQL Server Reporting Services)

Report Manager, 12–13, 214

EmployeeServiceCost.rpt file, 215

manage option, 215, 216

properties modification, 216–218

RDL file, 215

Upload File option, 214, 215

Report Page Layout (RPL), 11

Report Processor, 10

Report rendering, 11

buttonOK click Event Handler, 271

Class-Level Private Variables, 266

data source creation, 259

finalized Web report application, 277, 278

finished report, 273

GetItemParameters method

code, 267

Combo Item class, 270

ForRendering, 267

GetParameters_Load event method, 268–270

HistorylD, 267

Item, 267

Parameters dialog box, 271

ParameterValues, 267

ValidValue, 267–268

getParameters click Event, 272–273

GetParameters Form creation, 263–265

GetParameters_Load Event, 266

Report rendering (cont.)

RenderReport Button Click method, 277

report designing steps, 259–262

report list, 276

report parameters form constructor, 266

Report Server web service, 244

calls, 244

categories, 263

ReportExecutionService, 262

ReportingService2010, 262

Report Viewer control, 243, 244

ASP.NET application, 245

button, 274

Default.aspx Partial Code Listing, 275

DropDownList, 274

Horizontal Rule, 274

local rendering, 245

reportViewerWeb, 274

ScriptManager, 274

server-side mode, 245

SOAP API, 243

URL access, 243, 245–246

credential parameters, 250

HTML Viewer commands, 247–248

HTML Viewer parameters, 247

optional path information, 246

readme.html file, 251

rendering access, 244

report document map, 251

report parameters, 246, 247

Report Server command parameters, 247–249

Report Viewer Web Part commands, 247, 250

rs:Format parameter, 251

syntax, 245

user-supplied parameters, 251

URL reporting parameters, 244

URL viewer application, 244

ViewerParemeter method, 271

ViewerRVC.cs Form, 272

web service reference, 275, 276

Windows Forms SSRS 2008 viewer application (see Windows Forms SSRS 2008 viewer application)

Report library, 388, 389

Report security

auditing, 356–358

components, 329

data encryption

complexity and reliability, 330

HTTPS, 330

IPSec, 330

Protected Health Information (PHI), 329

RDP, 330

VPN client systems, 330

WEP, 330

data sources

authentication, 340

connection options, 354–355

data storage securing, 337–339

filtering reports, 340

health-care application

Active Directory users and computers, 340, 341

browser, 341

Clinical Reports root folder, 345

connection screen, 342, 343

content filtering, 352

content management permissions, 341

content manager, 341

Daily Activity report, 346

data source authentication method, 345

db_datareader permissions, 345

default security group, 348, 349

error message, 347, 348

Group Policy settings, 340

My Reports feature, 341

New Role Assignment form, 349

Registered Nurse folder, 345

Report Builder role, 341, 343, 344

report objects, 346, 347

reports publishing, 341

RN Windows security group, 351

RN_DS data source, 345, 346

RNsecurity group, 340

roles and system roles, 342

test account, 351, 352

users task, 342

windows integrated security option, 345

HTTP traffic

capturing, 335–337

display filter rule, 332

display filter window, 332

network interface card, 331

network monitor, 330–332

PI data captured, 333

port 80, 331

report manager, 332

permissions settings, 340

SQL server permissions, 355–356

SSL certificate, 334–335

SSRS roles, 339

system administrator, 350

system user, 350

user authentication, 339

windows integrated authentication, 340

Report Server, 8, 9

ReportServerTempDB, 8

Report server web service, 315–316

access, 231–233

calls, 244

categories, 263

CreateCatalogItem method, 230

CreateReportEditSession method, 231

form coding, 234

form layout, 233–234

RDL file, 237–239

ReportExecutionService, 262

ReportingService2010, 262

server name, 235

SOAP API, 230

TreeView Control population, 235–237

Windows Forms application, 230

Report Wizard

Corporate style, 128

data source, 126

generated report, 129

group and details selections, 128

Query String area, 127

tabular/matrix form, 127

S

Secure sockets layer (SSL), 330

ServiceTypeID, 32

SQL Server Data Tools (SSDT), 6, 13–14

SQL Server Integration Services (SSIS), 1, 309, 362

SQL Server machine, 32

SQL Server Management Studio (SSMS), 6–7

advanced query, testing performance, 28–30

graphical query, 23–25

SQL Server Profiler, 22

SQL Server Reporting Services (SSRS), 90, 91

advantages

BI strategy, 3

cost, 2

customizable, 2

SharePoint portal server, 3

standard platform, 2

subscriptions, 2

Web-enabled solution, 2

architecture

authentication layer, 10

components, 7, 8

data processing, 10–11

databases, 8–9

Report Processor, 10

report rendering, 11

Report Server, 9

scheduling and delivery, 11–12

Web service interface, 9–10

Business intelligence (see Business intelligence)

charts and visualizations, 4

client applications

BIDS/SSDT, 13–14

command-line utilities, 14

custom clients, 15

installation and configuration, 15–16

Report Builder 3.0, 14–15

Report Manager, 12–13

secure deployment, 16–17

tools, 12

controlling with SOAP

Class-Level Private Variables, 317

ComboItem class, 319

CreateSubscription method, 319

ListSchedules method, 318

Namespaces importing, 317

PickSchedule Constructor, 318

PickSchedule.cs class file, 316, 317

PickSchedule_Load event, 317

PickSchedule_Load method, 319

report delivery, 324–326

report scheduler, 320–323

Report Server Web service, 315, 316

subscription functionality, 316

controlling with WMI, 326

data modeler, 3

deployment, 279, 308

embeddable controls, 5

HTML text formatting, 5

logging, 309

lookup functions, 6

memory management, 4

Microsoft Word rendering, 5

performance enhancement, 4

Report Builder, 3, 422–423

report parts, 5

roles, 339

shared datasets, 6

SQL Server Reporting Services (cont.)

SSRS 2012 Integration with Microsoft Office SharePoint, 4

standardization, 1

Tablix report properties, 4

Stand-alone installation, 373

final configuration process, 384–388

installation steps, 374

New Reporting Services Service Application, 381–384

Reporting Services SharePoint Service, 379–381

SharePoint 2010

configuring, 377–379

installation, 374–375

SQL Server 2012, 375–377

Standard subscriptions

configuring, 301

creation, 299–300

Patient Recertification Listing report, 298

Pro_SSRS data source, 298

Subtotals, 134–136

T

Table report creation

Branch Name field, 432–433

Branch Name group, 433–434

data region, 427

Define Formula dialog box, 429–430

dragging and dropping fields, 432

Page Setup dialog box, 428–429

Pat Last Name field, 430

patient face sheet, 427–428, 430–431

Pro_SSRS folder, 434

ReportServer database, 434–435

sorting and grouping options, 431–432

user feedback, 427

Table-valued function, 168

Tablix elements, 175–177

Tagged Image File Format (TIFF), 11

TargetDataSetFolder, 222

TargetDataSourceFolder, 222

TargetReportFolder, 222

TargetReportPartFolder, 222

TargetServerURL, 222

TargetServerVersion, 222

Trx.ServicesTblID field, 178

U

URL link, 149–151

V

Virtual Private Network (VPN), 17, 330

Visual Basic .NET (VB.NET), 1

W, X, Y, Z

Windows Forms SSRS 2008 viewer application

Report Viewer control

advantage, 255

coding, 257–258

properties and methods, 255

ViewerRVC.cs form, 256–257

WebBrowser control

coding, 253–255

ViewerWBC.cs form creation, 252–253

Windows management interface (WMI), 326

Wireless encryption protocol (WEP), 330