Index

A AddNode Method, 237 ATOM,l1 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 in teractivi ty

bookmark link, 147-149 document mapping, 137-138 hyperlink actions, 143-147 hyperlink formatting and tool tips, 157 jumping to report, 151-156 sorting, 141-143 types, 136-137 URLlink,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 '

483

INDEX

484

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 Inserttab,457-458 layout selection screen, 452, 453 Microsoft's Reporting Services ad-hoc

tool, 463 New Table I 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 UserID,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 -N oCode.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, Ill, 112 Data Bar Start.rdl, 110 Data Baudl, 115 object recognition, 110 preview, 113, 114

RDL,115 Series Properties fill options, 112, 113 type selection, 110, 111

gauges, 89 Gauge Start.rill, 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.rill, 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-

414Pro_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 (HIP AA), 16 Health-care application

Active Directory users and computers, 340, 341

analysis services data source, 368-369 browser, 341

INDEX

485

INDEX

486

Health-care application (cant.) 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, 37l 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, 37l

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 tool tips, 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 N arne 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

BIDSlVisual Studio, 423 chart report (see Chart report creation)

INDEX

487

INDEX

488

Report Builder 1.0, 4.6 (cant.) ClickOnce application installation screen,

425 NET Framework 2.0, 424 Report Builder URL, 424 report designer, 423 Report 11anager, 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 11icrosoft's Reporting Services ad-hoc tool,

463 New Table I 11atrix 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 UserID,461 Visit Count, 458

Report Builder 3.0, 12, 14-15,45,219 BIDSlVisual 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 11ap 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 11atrix report creation) patient census model, 404 Report Builder 1.0 (see Report Builder 1.0) table report (see Table report) userfeedback,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, RDLfile, 287, 288 options menu, 285, 286 properties screen, 286, 287 ReportParameter, 287

Xl\!IL-based schema, 45 Report deployment

Actions link button, 393 application running, 239-242 Average Referral to Admissions, 392 configuration

Configuration 11anager, 223-224 properties, 221-223

data source definition, 391, 392 PatReLDS data source, 390 permission -related issues, 391 Report Builder 3.0 (see Report Builder 3.0) Report 11anager (see Report 11anager) report server web service (see Report server

web service) Reports Library, 388 rS.exe utility

Create Folder 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 PatienCName 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

INDEX

489

INDEX

490

Report rendering (cant.) 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 ReportingService201O,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, 27l 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 userstask,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

5 Secure sockets layer (SSL), 330 ServiceTypeID, 32 SQLServerData 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 Combo Item class, 319 CreateSubscription method, 319 ListSchedules method, 318 N amespaces 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

INDEX

491

INDEX

492

SQL Server Reporting Services (cant.) 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 N arne 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 userfeedback,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 URLlink,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