book

Microsoft SQL Server 2012 Reporting Services

Name: Microsoft SQL Server 2012 Reporting Services
Author: Stacia Misner
ISBN: 9780735670051

by Stacia Misner

April 2013

Beginner to intermediate

1030 pages

30h 21m

English

Microsoft Press

Read now

Unlock full access

Microsoft SQL Server 2012 Reporting Services
Dedication
Introduction
Who should read this bookAssumptionsOrganization of this bookConventions and features in this bookSystem requirementsCode samplesInstalling the code samplesAcknowledgmentsErrata & book supportWe want to hear from youStay in touch
I. Getting started with Reporting Services
1. What’s new in Microsoft SQL Server 2012 Reporting Services
Introducing new featuresPower ViewData sourcesData visualizationInteractive scatter chartMultiplesHighlighted valuesSlicerTilesView filterVisualization filterReport sharingSharePoint integrationData alertsData Alert DesignerData Alert ManagerReviewing other changesSQL Server Data ToolsNew rendering extensionsExcel 2010 rendererWord 2010 rendererParting with Report Builder 1.0Upgrading from prior versionsPreparationIn-place upgradeNative-mode report server upgradeSharePoint integrated-mode report server upgradeReports upgradeMigrationNative-mode report server migrationSharePoint integrated-mode report server migration
2. Introducing the Reporting Services environment
A reporting platformReporting life cycleReport developmentReport administrationReport accessReporting Services architectureNative modeData tierApplication tierServer tierService architectureSharePoint integrated modeData tierApplication tierServer tier
3. Understanding deployment scenarios
Single-server deploymentMemoryDisk spaceDisk activityDistributed deploymentNative-mode distributionSharePoint integrated-mode distributionOther componentsScale-out deploymentLoad balancingFailover clusterDistributed workloadInternet deployment
4. Installing Reporting Services
Reviewing hardware and software requirementsSelecting an operating systemChoosing a Database Engine editionReviewing SharePoint integrated-mode requirementsPlanning accounts for reporting servicesPerforming the installationInstalling prerequisitesInstalling SQL Server 2012Product keyLicense termsSetup roleFeature selectionInstallation rulesInstance configurationDisk space requirementsServer configurationDatabase Engine configurationAnalysis Services configurationReporting Services configurationInstallation configuration rulesConfiguring native-mode Reporting ServicesService accountWeb service URLDatabaseReport Manager URLEmail settingsExecution accountEncryption keysScale-out deploymentConfiguring partial integration with SharePointConfiguring SharePoint for local mode reportingConfiguring SharePoint integrated modeInstalling the Reporting Services Add-in for SharePointCreating a SharePoint web applicationCreating a business intelligence center siteConfiguring SharePoint for Reporting Services integrationConfiguring the service applicationProvisioning subscriptions and alertsConfiguring a SharePoint siteAdding another report serverAdding another Reporting Services web front-endInstalling Report BuilderInstalling sample databases
5. Exploring Reporting Services fundamentals
Understanding design environmentsComparing design environmentsChoosing a report designerUsing Report DesignerUsing Report BuilderUsing Power ViewDeveloping a report with Report BuilderLaunching Report BuilderNative modeSharePoint integrated-modeStand-alone applicationGetting Started WizardUsing the dataset designerGetting familiar with the Report Builder interfaceAdding a data sourceAdding a datasetAdding a report titleUsing data region wizardsTable Or Matrix WizardChart WizardMap WizardAdding a data region manuallyAdding fields to a tableDragging fields from the Report Data paneUsing the Field List iconAdding a totalGrouping dataInserting page breaksFormatting a reportPreviewing a reportSaving a reportManaging a reportPublishing a reportPublishing to a native-mode report serverPublishing to a SharePoint integrated-mode report serverAdding a descriptionReviewing processing optionsReviewing processing options in Report ManagerReviewing processing options in a SharePoint document libraryViewing a reportOpening a reportOpening a report in Report ManagerOpening a report in a SharePoint document libraryUsing the HTML Viewer
II. Developing reports

6. Planning your report design
Knowing your audienceReviewing report optionsLayout decisionsData considerationsParametersMobile devicesPrinted reportsDeveloping standards
7. Working with report server projects
Getting started with report server projectsCreating a Reporting Services projectCreating a new project and solutionAdding a new project to an existing solutionAdding an existing project to an existing solutionSelecting a project typeReport Server Project WizardReport Server projectGetting familiar with the Visual Studio interfaceSolution ExplorerProperties windowOutput windowError List windowToolbox windowReport Data windowRemoving a project from a solutionWorking with report project filesReviewing report project file typesUsing the Report WizardInitial stepsTabular reportMatrix reportFinal stepsAdding a new item to a projectAdding an existing item to a projectRemoving an item from a projectUsing report project commandsProject menuBuild menuConfiguring report project propertiesWorking with project configurationsSetting project properties
8. Retrieving data for a report
Understanding data source typesRelational data sourcesSQL ServerSQL AzureSQL Server Parallel Data WarehouseOracleTeradataOLE DBODBCMultidimensional data sourcesSQL Server Analysis ServicesSAP NetWeaver BIHyperion EssbaseMiscellaneous data sourcesXMLSharePoint listReport server modelConnecting to data with data sourcesComparing embedded and shared data sourcesWorking with an embedded data sourceCreating an embedded data sourceProcessing queries as a single transactionEditing an embedded data sourceConverting an embedded data source to a shared data sourceDeleting an embedded data sourceWorking with a shared data sourceCreating a shared data sourceReferencing a shared data source in a reportEditing a shared data sourceDeleting a shared data sourceSetting data source credentialsIntegrated securityUser name and passwordPrompt for credentialsNo credentialsRetrieving data with datasetsComparing embedded and shared datasetsWorking with an embedded datasetCreating an embedded datasetEditing an embedded datasetConverting an embedded dataset to a shared datasetDeleting an embedded datasetWorking with a shared datasetCreating a shared datasetReferencing a shared dataset in a reportEditing a shared datasetDeleting a shared datasetExploring query designersGraphical query designerReport model query designerSharePoint list query designerMDX query designerText-based query designerUnderstanding dataset propertiesFieldsOptionsFiltersParameters
9. Designing the report layout
Reviewing report itemsGeneral report itemsText boxesLines and rectanglesImagesSubreportsTablix data regionsTableMatrixListGraphical data regionsChartGaugeMapData barSparklineIndicatorAdding general report items to a reportAdding a text boxAdding a lineAdding a rectangleAdding an imageEmbedded imageExternal imageDatabase imagesAdding a subreportWorking with data regionsAdding a tableBinding data to a data regionGrouping dataAdding a total to a tableAdding a matrixSorting groupsAdding a listUsing properties to control appearance and behaviorConfiguring the placement and size of a report itemSetting the Location propertySetting the Size propertyControlling text box growthChanging the appearance of a report itemSetting font propertiesSetting selected text propertiesSetting alignment propertiesSetting the Format propertySetting locale propertiesSetting border propertiesSetting fill propertiesSetting list propertiesSetting line properties
10. Working with the tablix
Exploring the tablixWhat is a tablix?Tablix data regionsTableMatrixListTablix cellsCell contentCell scopeCell merge and splitTransforming a tablixTable to matrixMatrix to tableDetail list to grouped listConfiguring tablix propertiesNo data rowsGroups before row headerSortWorking with tablix groupsRow groupsSeparate columns for each groupSingle column for each groupStatic columnsRepeating static columnsAdjacent columnsAdjacent groupsGroup propertiesMissing group values in nested data regionsRecursive hierarchy groups
11. Developing expressions
Introducing expressionsSimple expressionsDataset field placeholderAggregate function placeholderBuilt-in field placeholderParameter value placeholderComplex expressionsUsing the Expression dialog boxOpening the Expression dialog boxExploring the Category treeUsing IntelliSenseMember or property selectionFunction tooltipSyntax errorUsing built-in collectionsDataSets collectionDataSources collectionFields collectionGlobals collectionParameters collectionReportItems collectionUser collectionVariables collectionWorking with expressionsAdding calculations to a reportCalculated fieldsText box expressionsReport item referencesBuilt-in fieldsApplying conditional formattingWorking with variablesGroup variablesReport variablesDeferVariableEvaluation propertyUsing expressions for dynamic connections and datasetsDynamic connection stringsDynamic query strings
12. Using functions
Getting help for functionsFunction description and syntaxFunction tooltipUsing text functionsThe character functionsThe Asc and AscW functionsThe Chr and ChrW functionsThe Space functionThe search functionsThe Filter functionThe GetChar functionThe InStr and InStrRev functionsThe formatting functionsThe Format functionThe FormatCurrency, FormatNumber, and FormatPercent functionsThe FormatDateTime functionThe array functionsThe Join functionThe Split functionThe conversion functionsThe LCase and UCase functionsThe StrConv functionThe string manipulation functionsThe Left, LSet, Right, and RSet functionsThe Mid functionThe StrDup functionThe StrReverse functionThe string evaluation functionsThe Len functionThe StrComp functionThe cleanup functionsThe Replace functionThe Trim, LTrim and RTrim functionsUsing date and time functionsThe date calculation functionsThe DateAdd functionThe DateDiff functionThe date manipulation functionsThe DatePart functionThe interval functionsThe date conversion functionsThe DateSerial and TimeSerial functionsThe DateValue and TimeValue functionsThe MonthName and WeekdayName functionsThe current date and time functionsUsing math functionsThe trigonometry functionsThe sign functionsThe exponent functionsThe rounding functionsOther math functionsUsing inspection functionsUsing program flow functionsThe Choose functionThe Iif functionThe Switch functionUsing aggregate functionsThe aggregate functionsThe Scope argumentThe RunningValue functionUsing financial functionsUsing conversion functionsUsing miscellaneous functionsThe InScope functionThe lookup functionsThe Lookup functionThe MultiLookup functionThe LookupSet functionThe row functionsThe Previous functionThe RowNumber functionWorking with hierarchical dataThe Level functionThe Recursive keyword
13. Adding interactivity
Interacting with the report layoutInteractive sortingSort detail rowsSort groupsSort by expressionApply sort to other groups and data regionsUsing fixed headersConfiguring visibilityShow or hide a rowDrilldownAdding tooltipsAdding navigation featuresDefining a document mapCreating a document mapCustomizing a document mapShowing and hiding a document mapRendering a document mapUsing embedded HTML tagsWorking with report actionsGo to reportGo to bookmarkGo to URL
14. Creating dynamic reports with parameters
Working with report parametersCreating a report parameterSetting report parameter propertiesGeneral propertiesAvailable valuesDefault valuesAdvanced parameter optionsDeleting a report parameterDisplaying parameter selections in a reportUsing filtersCreating a filterFiltering a datasetFiltering a data regionFiltering a groupUsing a report parameter as a filterUnderstanding filter operatorsEquality and inequalityLikeTop and bottomBetweenInNot inApplying OR logic to multiple filtersUsing parameters with subreports and drillthroughPassing a parameter to a subreportPassing a parameter to a drillthrough reportConfiguring the drillthrough report parameter valueOmitting a parameterWorking with query parametersCreating a query parameterLinking report and query parametersDeleting a query parameterCascading parametersCreating a child report parameter datasetLinking a parent report parameter to a query parameterChanging parameter order
15. Managing the page layout
Understanding renderingReviewing rendering formats by pagination typeUsing the RenderFormat global variableAdjusting the report sizeUnderstanding the page structure propertiesReport page structurePage size propertiesColumn propertiesWhite spaceUsing device information to change page propertiesWorking with the page header and footerAdding a page header or footerConfiguring page header or footer propertiesUsing expressions in a page header or footerPage headers and footers in WordPage headers and footers in ExcelRemoving a page header or footerConfiguring page breaksCreating page breaksUsing the KeepTogether propertyAdding page breaks by groupRepeating headersNumbering pagesGlobal variablesResetPageNumber propertyNaming pages
16. Using Analysis Services as a data source
Creating an Analysis Services datasetAnalysis Services data sourceMDX graphical query designerMetadata paneData paneFilter paneToolbarQuery modeCalculated Member BuilderBuilding a reportAnalysis Services dataset fieldsExtended field propertiesAggregate functionAggregate values in detail rowsShow Aggregations buttonDesigning parametersAuto-generated query and report parametersFilter paneReport parameterAvailable values datasetCalculated fieldParameterized datasetCustom query parameterQuery parameterMDX query parameterizationQuery parameter valueReport parameterCalendar control
III. Adding data visualizations
17. Creating charts
Adding a chart to a reportSelecting a chart typeUnderstanding chart typesLinear dataRatio dataMultivalue dataUsing the chart data paneValuesField selectionValue aggregationFieldsSeries propertiesCategory groupsField selectionCategory group propertiesSeries groupsField selectionSeries group propertiesFormatting chart elementsChartChart titleAxis titleAxesSeries labelsLegendWorking with data barsAdding a data barConfiguring data bar propertiesData bar lengthData bar appearanceWorking with sparklinesAdding a sparklineConfiguring sparkline propertiesSparkline appearanceSparkline axes alignment
18. Working with chart elements
Changing color palettesBuilt-in color palettesCustom color paletteWorking with vertical axis propertiesScale breakLogarithmic scaleWorking with horizontal axis propertiesAxis typeIntervalsInterval propertiesInterlacingWorking with series propertiesEmpty pointsMarkerEmpty point colorZero valueBubble chartsCalculated seriesSecondary axisMultiple chart typesMultiple chart areasWorking with pie chartsSlice consolidationSecondary pie chart
19. Comparing values to goals with gauges
Adding a gauge to a reportUnderstanding gauge typesCreating a gaugeRadial gaugesLinear gaugesWorking with gauge elementsUnderstanding gauge elementsPointerValuePointer propertiesCustom pointerSnapping intervalScaleScale propertiesMultiple scalesRangeRange propertiesMultiple rangesGaugeRadial gauge propertiesLinear gauge propertiesCommon gauge propertiesGauge labelGauge panelGrouping gaugesMultiple gauges in a gauge panelAdjacent gaugesChild gaugesRepeating gauges
20. Displaying performance with indicators
Adding an indicator to a reportUnderstanding indicator typesCreating an indicatorConfiguring an indicatorAssigning an indicator valueDefining indicator propertiesGeneral propertiesValue and statesSynchronization scopeGrouping indicatorsAdjacent indicatorsGauge and indicatorGauge with child indicatorCustomizing indicatorsUsing customized imagesAdding labelsAdjusting location and sizeDynamic sizing
21. Mapping data
Understanding spatial dataTypes of spatial dataPointsLinesPolygonsSQL Server spatial data typesGeometryGeographyUsing the Map WizardSelecting a spatial data sourceMap GalleryESRI shapefileSpatial queryChoosing spatial data and map view optionsSpatial field and layer typeCenter pointZoom factorMap resolutionEmbedded map dataBing Maps layerChoosing map visualizationPoint data mapsLine data mapsPolygon data mapsAdding an analytical data setSetting theme and data visualization optionsBasic marker mapBubble marker mapAnalytical marker mapLine mapLine analytical mapBasic polygon mapColor analytical mapBubble map for polygonsFinalizing the mapMap previewSimple adjustmentsMap titleLegendScales
22. Working with map elements
Configuring map elementsMapViewportCenter and zoomParallels and meridiansViewport coordinate system, projection, and boundariesOptimizationMap titleLegendScalesColor scaleDistance scaleWorking with map layersMap Layers paneMap layer propertiesCommon spatial element propertiesColor rulesColorsDistributionLegend optionsPoint mapsPoint propertiesPoint color rulePoint size ruleMarker type ruleLine mapsLine propertiesLine color ruleLine width rulePolygon mapsPolygon propertiesPolygon color ruleCenter pointsTile layer mapsInserting custom pointsUnderstanding order of precedence
IV. Managing the report server
23. Deploying reports to a server
Deploying contentDeploying a report projectDeployment from SSDTDeployment verificationSaving a report from Report BuilderUploading a reportUsing the rs utilityDeploying a report modelManaging contentUsing folders to organize contentMoving contentCreating a linked reportCopying a reportConfiguring report parametersConfiguring data source propertiesSelecting a data source for a reportSending credentials to a data sourceWindows integrated securityPrompted credentialsNo credentialsStored credentialsConfiguring report processing optionsExecuting a report on demandCaching reportsConfiguring report time-outRefreshing the cacheWorking with snapshotsCreating report snapshotsSaving report snapshots in report history
24. Securing report server content
Configuring report server security policiesUnderstanding authentication typesReviewing default authorization policiesNative-mode rolesSharePoint integrated-mode groups and permission levelsAssigning user permissionsNative-mode role assignmentsSharePoint permission-level assignmentsConfiguring item-level securitySecuring folders and document librariesNative-mode folder securitySharePoint integrated-mode document library securitySharePoint integrated-mode folder securityApproving data sources in SharePoint integrated modeSecuring an itemAssigning a system roleSecuring a report modelImplementing data securityUsing a query parameter and a role to secure dataUsing a permissions table to secure dataUsing a dataset filter to secure data
25. Performing administrative tasks
Configuring the report serverUpdating report server propertiesGeneral propertiesExecution propertiesHistory propertiesLogging propertiesSecurity propertiesAdvanced propertiesManaging encrypted informationConfiguring authenticationManaging memoryDisabling report server featuresDisabling an extensionManaging the report serverCanceling jobsSuspending report executionPerforming backup and recoveryNative-Mode report server databaseSharePoint integrated-mode report server databaseEncryption keysConfiguration filesMonitoring the report serverChecking the application database sizeMonitoring the Windows application event logUsing the trace logsAdding the HTTP logSharePoint diagnostic loggingExecution loggingUsing the ExecutionLog3 viewUsing performance counters
V. Viewing reports
26. Accessing reports online
Using Report ManagerSearching for a reportUsing the HTML ViewerUsing the document mapPrinting a reportUsing the My Reports folderAccessing reports in SharePointSearching for a reportUsing the SharePoint Report ViewerCreating a dashboardCreate a Web Part pageAdd a SQL Server Reporting Services Report Viewer Web PartConfigure the Web PartExporting reportsUsing soft page-break renderersExcel formatWord formatMHTML formatUsing hard page-break renderersPDF formatTIFF formatExporting a report for data exchangeCSV formatXML formatATOM format
27. Working with subscriptions
Creating a shared scheduleSchedules listNew scheduleCreating standard subscriptionsNew subscriptionDelivery providersEmail deliveryWindows file share deliveryNull Delivery ProviderSharePoint Document LibraryReport parameter valuesSubscription processing optionsCreating data-driven subscriptionsCreating a subscription delivery tableConfiguring data-driven subscription settingsNative modeSharePoint integrated modeManaging subscriptionsUsing the My Subscriptions pageDeleting a subscriptionNative modeSharePoint integrated mode
28. Data alerting
Understanding the data alerting architectureData alert workflowAlerting serviceCreating data alertsSharePoint Report ViewerData feedsData alert rulesRule scopeRule conditionMultiple rulesData alert condition removalSchedule settingsEmail settingsReceiving data alertsSuccessful alertAlert failureManaging alertsAlert statusEdit commandDelete commandRun commandConfiguring data alertingRsReportServer.Config file settingsIsAlertingService flagAlerting interval defaultsSharePoint configuration database settingsMonitoring the alerting processExecution logAlerting stored proceduresPerformance counters
VI. Ad hoc reporting
29. Reusing report parts
Introducing report partsReport partReport part benefitsDeploying report partsDeployment from SSDTDeployment from Report BuilderRedeployment of a report partReport part management on the report serverUsing report partsReport Part GalleryUpdate notificationChoosing report parts vs. subreports
30. Preparing to use Power View
Using tabular models with Power ViewIntroducing tabular modelsCreating a BI Semantic Model connectionEnhancing tabular models for Power ViewSummarizing valuesFormatting valuesSetting the default sortMarking the date tableManaging grouping behaviorAdding imagesUsing binary dataUsing external imagesImproving the appearance of identifying informationSimplifying the creation of a new table
31. Using Power View
Getting started with Power ViewVerifying browser requirementsConnecting to a data sourcePowerPivot GalleryReporting Services shared data sourceBI Semantic Model connection fileGetting familiar with the design environmentView workspaceViews paneRibbonField listLayout sectionVisualizing dataTableTable columnsTable layout sectionTable sort orderMatrixMatrix row and column groupsMatrix layout sectionChartsColumn, bar, and line chartsColumn, bar, and line chart layout sectionColumn and bar chart sort orderMultiplesScatter chart layout sectionPlay axisCardsFiltering dataHighlighted valuesSlicerTilesTile By areaTile visualization modeMultiple visualizations in a tiles containerSynchronizationView filterBasic filter modeAdvanced filter modeSelecting a display modeSaving a Power View reportFile save optionsPowerPoint export
VII. Using Reporting Services as a development platform
32. Understanding Report Definition Language
Introducing Report Definition LanguageRDL schema definitionRDL usageExploring key elementsReport elementDataSources and DataSource elementsDataSets and DataSet elementsTablix elementWorking with RDLManual RDL editsProgrammatic RDL editsRDL generationSolution overviewRDLGenerator classSolution deployment
33. Programming report design components
Embedding code in a reportCode propertyCustom function callUsing assemblies to share custom codeClass libraryClass definitionCustom assembly digital signatureAssembly deploymentCustom assembly callCreating a custom data processing extensionData processing extension overviewData processing extension developmentAssembly propertiesConnection classCommand classDataReader classParameter classParameterCollection classTransaction classData processing extension deploymentCustom extension digital signatureFile locationsConfiguration changesImplementation
34. Programming report server management
Scripting administrative tasksDeploying reportsDeployReports.rss script walkthroughDeployReports.rss script executionCreating a linked reportCreateLinkedReport.rss script walkthroughCreateLinkedReport.rss script executionWorking with configuration settingsUsing the WMI providerWMI provider application walkthroughWMI provider application executionUsing Windows PowerShell cmdletsUsing the web serviceWeb.configReferencesInitial variable declarationsPage loadRecipient listSchedule listReport listSubscription creationWeb application execution
35. Programming report access
Linking to reports with URL accessViewing a reportURL access on a native-mode report serverURL access on a SharePoint integrated-mode report serverUsing URL access parametersReport parametersHTML Viewer commandsReport Viewer commandsReport Server commandsUsing the ReportViewer controlReports Application projectReport WizardReportViewer controlReportViewer propertiesReportViewer applicationAccessing a report with the web serviceReferencesLoad methodRender methodSolution deployment
A. About the authors
Index
About the Author
Copyright

Overview

Use your SQL Server skills to create and manage key data reports

Deliver interactive business intelligence reports using SQL Server 2012 Reporting Services, and help facilitate real-time decision making throughout the enterprise. SQL Server expert Stacia Misner shows you in detail how to design, manage, and access custom reports that capture data from multiple sources. This book is ideal for experienced SQL Server developers, whether you’re new to Reporting Services or upgrading from an earlier version.

Discover how to:

Use the latest features, including the Power View ad hoc reporting tool

Design reports by understanding how your audience accesses and uses data

Add interactive features to help end users sort and filter data

Create rich data visualization with charts, gauges, indictors, and data maps

Configure your report server, provide security, and monitor report contents

Use SharePoint or Report Manager to search and access reports online

Create a library of report parts that clients can use for ad hoc reporting

Build and manage reports using Reporting Services as a development platform

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Professional Microsoft SQL Server 2012 Integration Services

Publisher Resources

ISBN: 9780735670051Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills